We all have limits, and an Access database is no exception. For example, an Access database has a size limit of 2 GB and can't support more than 255 concurrent users. So when it's time for your Access database to go to the next level, you can migrate to SQL Server. SQL Server (whether on-premises or in the Azure cloud) supports larger amounts of data, more concurrent users, and has greater capacity than the JET/ACE database engine. This guide gives you a smooth start to your SQL Server journey, helps preserve Access front-end solutions you created, and hopefully motivates you to use Access for future database solutions. The Upsizing Wizard was removed from Access in Access 2013, so now you can use the Microsoft SQL Server Migration Assistant (SSMA). To successfully migrate, follow these stages.
Before you begin
The following sections provide background and other information to help you get started.
About split databases
All Access database objects can either be in one database file, or they can be stored in two database files: a front-end database and a back-end database. This is called splitting the database and is designed to facilitate sharing in a network environment. The back-end database file must only contain tables and relationships. The front-end file must only contain all other objects, including forms, reports, queries, macros, VBA modules, and linked tables to the back-end database. When you migrate an Access database, it's similar to a split database in that SQL Server is acting as a new back-end for the data that is now located on a server.
As a result, you can still maintain the front-end Access database with linked tables to the SQL Server tables. Effectively, you can derive the benefits of rapid application development that an Access database provides, along with the scalability of SQL Server.
SQL Server benefits
Still need some convincing to migrate to SQL Server? Here are some additional benefits to think about:
More concurrent users SQL Server can handle many more concurrent users than Access and minimizes memory requirements when more users are added.
Increased availability With SQL Server, you can dynamically backup, either incremental or complete, the database while it's in use. Consequently, you do not have to force users to exit the database to back up data.
High performance and scalability The SQL Server database usually performs better than an Access database, especially with a large, terabyte-sized database. Also, SQL Server processes queries much faster and efficiently by processing queries in parallel, using multiple native threads within a single process to handle user requests.
Improved security Using a trusted connection, SQL Server integrates with Windows system security to provide a single integrated access to the network and the database, employing the best of both security systems. This makes it much easier to administer complex security schemes. SQL Server is the ideal storage for sensitive information such as Social Security numbers, credit card data, and addresses that are confidential.
Immediate recoverability If the operating system crashes or the power goes out, SQL Server can automatically recover the database to a consistent state in a matter of minutes and with no database administrator intervention.
Usage of VPN Access and Virtual Private Networks (VPN) don’t get along. But with SQL Server, remote users can use still use the Access front-end database on a desktop and the SQL Server back-end located behind the VPN firewall.
There are a few issues you can address up front that can help streamline the migration process before you run SSMA:
Add table indexes and primary keys Make sure each Access table has an index and a primary key. SQL Server requires all tables to have at least one index and requires a linked table to have a primary key if the table can be updated.
Check primary/foreign key relationships Make sure these relationships are based on fields with consistent data types and sizes. SQL Server does not support joined columns with different data types and sizes in foreign key constraints.
Remove the Attachment column SSMA doesn't migrate tables that contain the Attachment column.
Before you run SSMA, take the following first steps.
Close the Access database.
Make sure that current users connected to the database also close the database.
Back up your database. For more information, see Protect your data with backup and restore processes.
Tip Consider installing Microsoft SQL Server Express edition on your desktop which supports up to 10 GB and is a free and easier way to run through and check your migration.
Tip If possible, use a stand-alone version of Access. If you can only use Office 365, then use the Access 2010 database engine to migrate your Access database when using SSMA. For more information, see Microsoft Access Database Engine 2010 Redistributable.
Microsoft provides Microsoft SQL Server Migration Assistant (SSMA) to make migration easier. SSMA mainly migrates tables and select queries with no parameters. Forms, reports, macros, and VBA modules are not converted.
To migrate a database using SSMA, first download and install the software by double-clicking the downloaded MSI file. Make sure you install the appropriate 32 or 64 bit version for your computer.
After installing SSMA, open it on your desktop, preferably from the computer with the Access database file.
You can also open it on a machine that has access to the Access database from the network in a shared folder.
Follow the beginning instructions in SSMA to provide basic information such as the SQL Server location, the Access database and objects to migrate, connection information, and whether you want to create linked tables.
If you are migrating to SQL Server 2016 or later and want to update a linked table, add a timestamp column by selecting Review Tools > Project Settings > General. For more information, see Access linked table to SQL-Server database returns #deleted.
To set precise data types, select Review Tools > Project Settings > Type Mapping. For example, if you only store English text, you can use the varchar rather than nvarchar data type.
The SQL Server Metadata Explorer displays your Access database objects and SQL Server objects allowing you to review the current content of both databases. These two connections are saved in your migration file should you decide to transfer additional objects in the future.
Note The migration process can take some time depending on the size of your database objects and the amount of data that must be transferred.
SSMA converts Access objects to SQL Server objects, but it doesn't copy the objects right away. SSMA provides a list of the following objects to migrate so you can decide whether you want to move them to SQL Server database:
Tables and Columns
Select Queries without parameters.
Primary and Foreign keys
Indexes and Default values
Check constraints (allow zero length column property, column validation rule, table validation)
As a best practice, use the SSMA assessment report, which shows the conversion results, including errors, warnings, informational messages, time estimates for performing the migration, and individual error correction steps to take before you actually move the objects.
Converting database objects takes the object definitions from the Access metadata, converts them into equivalent Transact-SQL (T-SQL) syntax, and then loads this information into the project. You can then view the SQL Server or SQL Azure objects and their properties by using SQL Server or SQL Azure Metadata Explorer.
To convert, load, and migrate objects to SQL Server, Follow this guide.
Tip Once you have successfully migrated your Access database, save the project file for later use, so you can migrate your data again for testing or final migration.
Consider installing the latest version of the SQL Server OLE DB and ODBC drivers instead of using the native SQL Server drivers that ship with Windows. Not only are the newer drivers faster, but they support new features in Azure SQL that the previous drivers don’t. You can install the drivers on each computer where the converted database is used. For more information, see Microsoft OLE DB Driver 18 for SQL Server and Microsoft ODBC Driver 17 for SQL Server.
After you migrate the Access tables, you can link to the tables in SQL Server which now hosts your data. Linking directly from Access also provides you with a simpler way to view your data rather than using the more complex SQL Server management tools. You can query and edit linked data depending on the permissions set up by your SQL Server database administrator.
Note If you create an ODBC DSN when you link to your SQL Server database during the linking process, Create the same DSN on all machines that use the new application.
For more information, see Link to or import data from an Azure SQL Server Database and Import or link to data in an SQL Server database.
Tip Don't forget to use the Linked Table Manager in Access to conveniently refresh and relink tables. For more information, see Manage linked tables.
Test and revise
The following sections describe common issues you can encounter during migration and how to deal with them.
Only Select Queries are converted; other queries are not, including Select Queries that take parameters. Some queries may not completely convert, and SSMA reports query errors during the conversion process. You can manually edit objects that do not convert by using T-SQL syntax. Syntax errors may also require manually converting Access-specific functions and data types to SQL Server ones. Common SQL syntax differences include:
Access uses the asterisk sign (*) as a wildcard character while T-SQL uses the percent (%).
Access uses quotes around table names and objects. T-SQL can use them for table names with spaces, but this is not standard naming practice. In most cases, object names should be renamed without spaces, but queries must also be rewritten to reflect new table names. Use brackets [ ] for tables that cannot be renamed but which do not conform to naming standards.
Access adds extra parentheses around parameters in queries, which can be removed.
SQL Server handles NULL values and string concatenation differently. Test queries to ensure that the right values are returned.
For conditional statements, Access uses IIF while T-SQL often uses CASE, although SQL Server 2012 and later supports IIF.
Several functions and data types are used differently in Access and T-SQL. SSMA should convert these for you, but you may need to make further manual changes.
Access and SQL Server have similar data types, but be aware of the following potential issues.
The Large Number data type The Large Number data type stores a non-monetary, numeric value and is compatible with the SQL bigint data type. You can use this data type to efficiently calculate large numbers but it requires using the Access 16 (16.0.7812 or later) .accdb database file format and performs better with the 64-bit version of Access. For more information, see Using the Large Number data type and Choose between the 64-bit or 32-bit version of Office.
Attachment The Attachment type stores a file in Access database. In SQL Server, you have several options to consider. You can extract the files from the Access database and then consider storing links to the files in your SQL Server database. Alternatively, you can use FILESTREAM or FileTables to keep attachments stored in the SQL Server database.
Hyperlink Access tables have hyperlink columns that SQL Server does not support. By default, these columns will be converted to nvarchar(max) columns in SQL Server, but you can customize the mapping to choose a smaller data type. In your Access solution, you can still use the hyperlink behavior in forms and reports if you set the Hyperlink property for the control to true.
Multivalued field The Access multivalued field is converted to SQL Server as an ntext field that contains the delimited set of values. Because SQL Server does not support a multivalued data type that models a many-to-many relationship, additional design and conversion work might be needed.
For more information about mapping Access and SQL Server data types, see Compare data types.
Note Multivalued fields are not converted and were discontinued in Access 2010.
Although VBA is not supported by SQL Server, note the following possible issues:
VBA functions in Queries Access queries support VBA functions on data in a query column. But Access queries that use VBA functions cannot be run on SQL Server, so all requested data is passed to Microsoft Access for processing. In most cases, these queries should be converted to remote queries ( pass-through queries, stored procedures, or views) that run in SQL Server for optimal performance.
User Defined Functions in Queries Microsoft Access queries support the use of functions defined in VBA modules to process data passed to them. Queries can be standalone queries, SQL statements in form/report record sources, data sources of combo boxes and list boxes on forms, reports and table fields, and default or validation rule expressions. SQL Server cannot run these user defined functions. You may need to manually redesign these functions and convert them to stored procedures on SQL Server.
By far, the most important way to optimize performance with your new, back-end SQL Server is to decide when to use local or remote queries. When you migrate your data to SQL Server, you are also moving from a file server to a client-server database model of computing. Follow these general guidelines:
Run small, read-only queries on the client for quickest access.
Run long, read/write queries on the server to take advantage of the greater processing power.
Minimize network traffic with filters and aggregation to transfer only the data you need.
Your application can also use user-defined functions, stored procedures, and triggers to centralize and share application logic, business rules and policies, complex queries, data validation, and referential integrity code on the server, rather than on the client.
Avoid running a query that combines a local Access table and SQL Server table, often called a hybrid query. This type of query still requires Access to download all the SQL Server data to the local machine and then run the query, it does not run the query in SQL Server.
Don’t open a form recordset based on a large table and do turn off record navigation.
Finally, test each query to ensure optimal performance.