Upsize an Access database to SQL Server

To upsize an access database to SQL Server (or Oracle, Postgres etc) there are several steps to perform:

There are several tools which can automate some of the steps above.

One pre-requisite for a sucessfull upsize is having compatible Table and Column names, if the database was built using a sensible naming convention, this won't be a problem. At a minimum it will involve removing any spaces and punctuation from the names. Both Access and SQL Server have a number of 'reserved words' that should not be used for table or column names.

In the SQL upsizing wizard, choose 'Use Declarative Referential Integrity (DRI)', for relationships rather than triggers. Don't allow the wizard to add timestamp columns unless you have a specific plan for using them.

Data Types

By default Access Text fields will be converted to Nvarchar, if you don't require unicode these can be changed to plain Varchar.

Access YES/NO columns will be converted to SQL bit or tinyint columns, it is essential that these have a default value
e.g. (0) as Access will not be able to interpret any NULLs.

It is worth checking dates in Access for typos like 214 instead of 2014, as these will not be accepted by SQL server.

Supported date ranges

Access: Jan 1, 100 AD to Dec 31, 9999 AD (9,899 year range)
SQL Server (datetime)1753 AD to Dec 31, 9999 AD (8,246 year range)
SQL Server (smalldatetime) 1 Jan 1900 to 6 Jun 2079 AD (179 year range)
Oracle: 14712 BC to Dec 31, 9999 AD (24,711 year range)

Data Types
MS Access (Jet) SQL Server
Binary(size) Varbinary(size)
Byte Tinyint
Currency Money
Date Datetime
Decimal Float
Double Float
GUID Uniqueidentifier
Integer Smallint
Long Integer Integer
LongBinary Varbinary(max)
Memo Nvarchar(max), Varchar(max)*
Single Real
Text(size) Nvarchar(size), Varchar(size)*
Yes/No Bit, Tinyint

OpenRecordset

If your VBA code opens any recordsets, you must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server linked table.

Renaming linked tables

Rename linked tables to remove the DBO_ prefix, instead of renaming tables manually, create a form with a command button and add this code to the OnClick event:

Dim strSQL As String
Dim db As Database
Dim rs As Recordset
 
' Select the Linked tables to rename
strSQL = "SELECT Name FROM MSysObjects WHERE (((Left([Name],4))='dbo_') AND (MSysObjects.Type) IN (4,6) );"
' MSysObjects.Type values:
' 1 = Local Table
' 4 = ODBC Linked Table
' 6 = Access Linked Table
 
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
 
If rs.EOF = False Then
    rs.MoveFirst
    Do Until rs.EOF
        'rename starting at the 5th character
        DoCmd.Rename Mid(rs!Name, 5, 100), acTable, rs!Name
        rs.MoveNext
    Loop
    rs.Close
End If

Note that the script above, only reads from the MSysObjects table, the rename is done using docmd.rename.
Performing updates on system tables is not supported or recommended.

“The only meaningful definition of a “generalist” is a specialist who can relate his own small area to the universe of knowledge” ~ Peter Drucker

Related Links:

SSW Upsizing Pro - Commercial tool for automatic upsizing.
Q286335 - Access reserved words
SQL Server reserved words


© Copyright SS64.com 1999-2016
Some rights reserved