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.
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
If your VBA code opens any recordsets, you must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server linked table.
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
SSW Upsizing Pro - Commercial tool for automatic upsizing.
Q286335 - Access reserved words
SQL Server reserved words