How-to: Upsize an Access database to SQL Server

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

• Create a new server side database.
• Create an ODBC connection on your client that connects to the new database.
• Create the Tables and Columns with matching names and equivalent datatypes.
• Add any data into the new tables.
• Add indexes, primary keys and default values.
• Delete the old Access tables and link to the new server side ones (take a backup first)
• Test thoroughly including edits and deletes.

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

• The built-in Access Export Table option - this can create and populate the server side tables, and is probably the easiest method.
• The SQL Server Migration Assistant for Access (SSMA)

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. In SQL Server 2019 and above, UTF8 is supported using just VARCHAR, so change these if you don't need UTF16.

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 219 instead of 2019, as these will not be accepted by SQL server.

Supported date ranges

Database	Date range	Date range Span
Access	Jan 1, 100 AD to Dec 31, 9999 AD	9,899 years
SQL Server (datetime)	1753 AD to Dec 31, 9999 AD	8,246 years
SQL Server (smalldatetime)	1 Jan 1900 to 6 Jun 2079 AD	179 years
Postgres (timestamp)	4713 BC to 294276 AD	298,000 years
Postgres (date)	4713 BC to 5874897 AD	5,879,000 years
Oracle	4712 BC to Dec 31, 9999 AD	14,711 years

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:

Create an ODBC connection to SQL Server.
SSW Upsizing Pro - Commercial tool for automatic upsizing.
Q286335 - Access reserved words
SQL Server reserved words