Why would you want to upsize to SQL Server?
Informational Links
Suggestions for the SQL Server Upsizing Wizard

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:

• Access 2000 and SQL Server 2000 Readiness Update
• ACC2000: "Access 2000 Upsizing Tools" White Paper Available - 241743
• ACC2002: "Access 2002 Upsizing Tools" White Paper Available - 294407
• Acc97 : "Overflow" Message When Upsizing to SQL Server 2000 - 279454
• "Overflow" Error Message When You Try to Upsize (from ACC2000) to SQL Server 2000 - 272384
• ACC2000: Jet Table Constraint Silently Fails to Upsize - 201310
• ACC2002: Upsizing Wizard Fails to Upsize Data in Large Tables - 295231
• ACC2000: Optimizing for Client/Server Performance - 208858 (which includes the registry settings to optimize the performance of ODBC data sources in Microsoft Access.)
• Designing Efficient Applications for Microsoft SQL Server
• ACC2002: Issues with the Access 2002 Upsizing Wizard - 328319

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.

Data Overflow. Invalid character value for cast specification...  By Brendan Reynolds - Access MVP The range of values allowed in an Access Date/Time field and in the SQL Server equivalents is different. An Access Date/Time field accepts dates from 1 Jan 100 to 31 Dec 9999. A SQL Server datetime field accepts dates from 1 Jan 1753 to 31 Dec 9999, while a smalldatetime field accepts dates from 1 Jan 1900 to 6 Jun 2079. Try running a query on your Access table to see if it has any dates prior to 1 Jan 1900 or later than 6 Jun 2079.

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.

Data conversion

Suggestion originally by fellow MVP Elmar Boye and extensively updated by me.
For a one time conversion the quickest solution would be:
- Use the upsizing wizard for A2002 to upsize the table structures.
- Generate a SQL script via SQL Server Enterprise Manager  (Within Enterprise Mgr right click on your database, highlight All Tasks and choose Generate SQL Scripts ...)
- Use a text editor to replace ncharvar, ntext with varchar/text (if upsizing to SQL Server 7)
     - replace bit type fields with int  (See the Bit Fields section here.)  (Hint, in case you have any field names with 'int' embedded in them I'd suggest you replace [bit] with [int] including the square brackets)
     - replace any nchar with char (if the upsizing wizard creates any that is.)
- I'd suggest splitting the generated SQL script into two files.  One with the the CREATE TABLE and the ALTER TABLE CONSTRAINT and PRIMARY KEY NONCLUSTERED ON [PRIMARY] lines.  The second script with the CREATE INDEX ON [PRIMARY] lines and the ALTER TABLE ADD CONSTRAINT lines.
- Create the SQL Server database with the first fixed script (using Query Analyser)  (Don't forget to change the db name from master to your db.   Yup, I forgot.  <smile>  Several times. <sigh>)
- Upsize the data preferably with the Data Transformation Service but possibly with the Upsizing Wizard in A97 or A2002   (The progress bar on the DTS screen is a bit too eager to get to the right hand side.   It was at the end before even 75% of my tables were upsized.)
- Create the indexes and the relational constraints with the second script.  This will likely allow for better performance when bulk adding records, although this is just a guess on my part.  More importantly this allow you to add the table data without having to start at the parent tables and work your way down the parent child relationships.

Cleaning up the index names.
  If you are going to link to the database using Access 97 then you'll want to rename the GUID created indexes to a much shorter name.  Heck, GUID1,2,3 etc. is as good as any.  <smile>  Do make sure there aren't duplicate index names per table as these just won't be added.
  By following the below method I determined that 98% of the GUID indexes were duplicates of already existing indexes.  Although this was likely because all my primary and foreign key names end in ID.
    I decided the simplest way to do this was to use the SQL Server Enterprise manager.  Right click on a table, select All Tasks and click on Manage Indexes.  You can then work with the indexes on the current table.  Occasionally there was a GUID index which didn't have a normal name so don't go blindly deleting all of them.   When you're done the current Table click on the combo box to choose the next table.
    When you're done cleaning up the indexes create a new copy of the database by using the process outlined in the previous step with splitting the script into two, etc, etc.
     Note that GUIDs were only used in A97 (or likely earlier) when creating index names.    If you've created the database entirely within Access 2000 or newer you won't have to concern yourself with GUID 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.
A suggestion by an MVP Lead.    Triggers are really the "old" way of doing things and DRI is the newer, more preferred (less difficult to mess up) method.   Now the Access 97 Upsizing wizard uses triggers for cascading deletes/updates.  The A2002 Upsizing Wizard gives you the choice to use Triggers or DRI.  I don't use cascading deletes/updates and strongly urge people to stay away from them.

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
  Recordset.Update
  Recordset.Move 0, Recordset.LastModified
first. After that you can reference the autonumber value. That will get also all other default constraints which could have been set by SQL Server constraints.

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
ACC97: Tips for Converting Applications to Using ODBCDirect - 164481
ACC2000: New SQL Records Appear Deleted Until Recordset Reopened - 208799

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

If Errors.Count > 1 Then
    For Each errX In DAO.Errors
        MsgBox "ODBC Error: " & errX.Number & ", " & errX.Description
    Next errX
Else
    MsgBox Err.Number & ", " & Err.Description
End If

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.

[ Access Tips | Main ]