Preparing Access databases for migration (AccessToSQL)

Article
02/28/2023

Before you migrate Access databases to SQL Server, you must determine which databases to migrate and ensure that those databases are ready for migration.

Determining when to migrate to SQL Server

The Jet database engine, which is used as the database engine for Access, is a flexible, easy-to-use solution for data management. However, as databases become larger and more mission critical, many users find that they require greater performance, security, or availability. For applications that require a more robust data platform, consider moving the underlying databases for those applications to SQL Server. For more information about deciding when to migrate, see the migration information page on the SQL Server Web site.

After you migrate databases to SQL Server, you can continue to use Access by using linked tables, or you can manually migrate your applications to Microsoft .NET Framework-based code that interacts directly with SQL Server.

Determining which databases to migrate

SQL Server Migration Assistant (SSMA) for Access can locate Access databases for you. You can then export metadata about those databases to SQL Server. For more information about how to export and query metadata, see Exporting an Access Inventory.

Note

Not all Access features and settings are supported by, or can be easily converted to, SQL Server. Before you start migrating databases, see Incompatible Access Features.

Preparing for migration

Use the following guidelines to help prepare your Access databases for migration to SQL Server.

Upgrading older Access databases

SSMA for Access supports Access 97 and later versions. If you have databases from earlier versions of Access, open and save the databases in Access 97 or a later version.

Removing workgroup protection

SSMA cannot migrate databases that use workgroup protection. To remove workgroup protection from an Access database, perform the following steps:

Copy the Access database file to another location.
Open the copied database.
On the Tools menu, point to Security, and then select User and Group Permissions.
Select the Users option, select the Admin user, and then ensure that the Administer permission is selected.
Select the Groups option, select the Users group, and then ensure that the Administer permission is selected.
Click OK, and then on the File menu, click Exit.

You can now use SSMA to migrate the copied database. After you load the schema into SQL Server, you can manually secure the database on SQL Server.

Backing up databases

Before you migrate your Access databases to SQL Server, you should back up both the Access databases that you will be migrating as well as the SQL Server databases into which you will migrate Access objects and data.

To back up an Access database, on the Tools menu, point to Database Utilities, and then select Back Up Database.

For information about how to back up SQL Server databases, see "Backing Up and Restoring Databases in SQL Server" in SQL Server Books Online.

Documenting databases

You might also want to document the properties, such as lists of database objects, file sizes, and permissions, of your Access databases. To generate this documentation in Access, on the Tools menu, point to Analyze, and then click Documented.