Many more comments and updates to follow as I continue to work with the tool(s). Later. The upsizing was not completed as I ran into some interesting problems and bugs. A PHB (Dilbert's Pointy Haired Boss) at a client cancelled the upsizing. Thus this document is unfortunately incomplete.
Mary Chipman has stated: "I would avoid using Access 97 with SQL Server 2000 due to data type incompatibilities." However I'm wondering if you replace field types ncharvar with charvar, nchar with char and bit with int if this will be a problem.
MS has a tool called the Upsizing Wizard which assists with the task of converting your Access MDB to use SQL Server. For Access 97 you can download the Microsoft Access 97 Upsizing Tools It's built in to Access 2000 and newer. A2002 apparently does a much better job of upsizing. I presume that this also works in the A2000 format MDBs but I'm not certain of this.
Some randomly selected KB and MSDN articles:
There are quite a number of KB articles on upsizing so I strongly urge you to do a search at the MS Knowledge Base yourself using the keyword 'upsizing'.
Get the SSW Upsizing PRO! tool. It's well worth using as it checks for problems and warnings that the Microsoft Upsizing Wizard does not. Both in table, fields and indexes as well as data. For example records with dates < #1900/1/1# were completely ignored by the Access 2002 upsizing wizard. With no message of any sort. The SSW Upsizing PRO! tool caught this problem and warning me before it happened. Thus I was able to clean up the tables and data before upsizing.
I've seen an MDB with over 130 tables and a half million or so records between them contain about 10 or 15 date errors where the century is 1 or 12 or something similar. The developer chose to display dates with just the year and not displaying the century. Users however made those 10 or 15 errors by accidentally keying in some extra information. Such as, for example, entering 12/31/199. As soon as the user tabbed/exited out of the field the date is displayed as just 12/31/99. So how was the user to know there was an error? These dates were also just displayed and printed on reports and not part of any computation so that wasn't a problem.
That said I came across some minor problems. Mostly along the lines of error messages generated by their tool which were no longer applicable to the newest version of SQL Server. I'm awaiting fixes by SSW for these. Later: SSW have incorporated all my of my suggestions however I haven't yet had the time to test the new version of the tool. <smile> Even later. Yup, they fixed all my complaints and suggestions.
(Disclaimer. I was given a license for this tool by SSW. However I feel I'm still quite objective in my comments.)
Upsize Wizard "Access to SQL Server is what we do" However I've written tools myself to automatically upsize many Access queries to SQL Server views. Mind you only the simple ones without any VBA functions involved.
Converting your queries to SQL Server views and stored procedures
The biggest factor here will be the use of embedded functions. Move them to either the control source on text fields as functions on forms or the OnFormat/OnPrint Events of reports. One exception would be very simple functions such as a rounding function which I use for formatting purposes. This can easily be converted to T-SQL code.
Suggestion originally by fellow MVP Elmar Boye and
extensively updated by me.
Cleaning up the index names.
Create a rowversion field on each table. (Previously known as timestamp.) Otherwise when Access looks to see if someone else has changed any data on the record in the event of a conflict Access has to scan each field on the record.
Use declarative referential integrity (DRI) instead of triggers.
Don't use sys as a prefix for tables while in Access. As I did for some of my tables such as workstation name, user name, login/logout times and so forth. SQL Server also uses tables with these prefix. While there is a column within SQL Server so you can easily see which tables are yours and which is SQL Server, it is quite possible that your table name could be the same as a SQL Server name. Let alone the confusion factor.
And don't use _Local as a suffix. _Local tables aren't upsized! Comment by DG
Determining the value of the autonumber field after adding a record
by fellow MVP Elmar Boye
One thing for your migration to be aware of. If you are working with
recordsets and use the autonumber for detail records, the autonumber isn't
available after an AddNew. You have to issue a
Thankfully that's how I've always done that. I never knew until recently you could even get the value of the autonumber after doing the .addnew. Thus I've never done it that way.
You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.
I'm somewhat lazy when it comes to using recordsets within VBA Code. I sometimes didn't bother differentiating between read only recordsets and updatable recordsets because the performance difference was immaterial most of the time. However SQL Server really likes those options to be set. And Access gives you the above error message. Thus use the following options as appropriate.
E.g.. Set rs = db.OpenRecordset("TestTable") should now be Set rs = db.OpenRecordset("TestTable", dbOpenDynaset, dbSeeChanges)
Note: You can no longer use dbAppendOnly but must use dbOpenDynaset, dbSeeChanges.
You can also put dbSeeChanges in your code while access Jet tables. So you can do this operation long ahead of time.
ACC: New SQL Records Appear
Deleted Until Recordset Reopened - 135379
ODBC - Call failed
Getting that error message when running some code? You need to change your error handling routines as per ACC2000: How to Trap Specific ODBC Error Message - 209855. Although I shortened that code down to the following:
Dim errX As DAO.Error
I see no reason to put this code in unless you're actually debugging a module. In other words don't bother to go back to add this code to a thousand routines when doing the conversion.
Error 3125 "The database engine can't find <name>. Make sure it is a valid parameter or alias name, that it doesn't include invalid characters or punctuation, and that the name isn't too long."
I got this message when attempting to link to the SQL Server tables from Access 97. Fortunately Microsoft SQL Server Advisor had the answer. "The length of the table name combined with the longest name of all the indexes on the table must be less than 64 characters in length." This wasn't a problem in Access 2000. See Solve Access/Jet Text Bug with SQL Server ODBC-Linked Tables for more details. And, yes, SSW's Upsizing Pro had caught this as a warning but I wasted considerable time before I finally did a web search and then went to the Upsizing Pro report. <sigh>
Access 2000 doesn't have this problem.
Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.
Granite Fleet Manager - the best designed fleet maintenance tracking and management system available