Table/Field Descriptions, Formats Upsizing Access to Sql Server

[nazzaro]

Those of you with experience upsizing Access 97 to Sql Server 7.0 and linking tables to Access 97:

Is there a way to retain the Table and Field descriptions and "special" formats (like "mm/dd/yyyy", etc.) so that they appear in Access 97 (as linked to Sql Server) the way they did in the "native" Access 97 original database? The database in question contains literally thousands of fields for which re-entry of this info would be a real headache.

Also, would there be any differences in the database schema once the tables are linked to Sql Server vs. the "native" tables?

A heads-up on any other "gotchas" (like not being able to use the "seek" method or "index" object on linked Sql Server tables, the "dbSeeChanges" stuff, maintaining the same table relationships, outer join problems, using +1 instead of -1 for "true", using "Top N" syntax in queries, implementing cascading updates and deletes, changed data type problems, zero-length vs. null strings, etc. would be appreciated.

 

[BSman]

I was just ready to ask a similar question. I've recently received some SQL 2000 training and found that SQL 2000 displays a window when in the table design mode in Enterprise manager and you click on a column.

However, no matter what I try, it seems that SQL 7 (which is what we currently use) does not have a place to store column descriptions?

Can someone either confirm that SQL 7 does not include a place to store and display column descriptions (which is a step backward from Access!) or, if there is a place to store column descriptions, tell use how to display it and enter/modify column descriptions?

I haven't really looked at retaining column formats, but I have learned some "tricks" that I recommend for converting.

First, you may want to remove all keys from your Access tables before importing them into SQL 7. You can then define them appropriately (including meaningful names, clustering, etc.) in SQL. I have found this to work better, at least for me.

You can retain your Access front end, but as much as possible use pass through queries to execute queries on the SQL server. You can also use the pass through query format to execute stored procedures on SQL server.

You will have to modify your queries a bit so they work properly under SQL. One especially important thing is that dates (and text) are surrounded by single quotes.

The biggest tables in the SQL database I'm developing and is being used have between 700,000 and over 2.5 million rows of data in each of them. By using pass through queries to minimize the data transferred through the network you can see dramatic increases in speed, especially in reports. I tend to use pass through queries that I modify just before the user runs the report. This way I can include user selected values (such as date ranges) in the query or stored procedure parameters in the query and only have the specifically needed values returned.

Also, Access grouping queries have the weird (but useful) ability to use columns in their select statements that are not part of the returned columns. This option is not available in true SQL (including SQL server), so you can't do a grouping query in SQL that groups columns/sums values for a range of columns selected for a date range (for example), unless the date column is included as part of the grouping columns. So for those cases return only the needed data for your report and in Access run a separate grouping query on the results of the pass through query.

I also modified an ad hoc query builder form I wrote (using a data table that identifies the data source tables, columns, etc.) for something that is easy for users to use to build their own queries. It will display all records or records grouped according to the user selected sort columns (and just show totals and counts for the appropriate columns).

Overall I like using Access (I'm using Access 97 at this point because that's the standard where I work) as a front end. The reporting is pretty good and the handling of forms, etc., is very good. Speed also seems to work well.

 

[SQLSister]

One thing that really annoyed me when I switched over was that Access supports cross-tab queries and SQL does not directly. There are ways to manipulate SQL to get the same results but it is complex whereas cross-tabs were a snap in Access.

Agree about the improved performance of using pass-through queries vice Access queries. Suggest you convert and then really test your user interface and find the places running slowest and convert these first. Other than the performance issue, you can pretty much use the linked tables just as if they were Access tables. One caution is that you cannot enter or update data in a linked table unless it has some kind of unique field.