Skip to content

preparing-access-databases-for-migration-accesstosql.md

Latest commit

 

History

History
83 lines (55 loc) · 5.15 KB

preparing-access-databases-for-migration-accesstosql.md

File metadata and controls

83 lines (55 loc) · 5.15 KB
title Preparing Access Databases for Migration (AccessToSQL)
description Learn how to determine which Access databases to migrate to SQL Server or Azure SQL Database and ensure that those databases are ready for migration.
author nilabjaball
ms.author niball
ms.reviewer randolphwest
ms.date 12/30/2025
ms.service sql
ms.subservice ssma
ms.topic how-to
ms.collection
sql-migration-content
helpviewer_keywords
Access databases, versions
Access databases, when to migrate
Access databases, workgroup security
backing up databases
documenting databases
files, preparing
migrating databases, versions
migrating databases, when to migrate
versions of Access
workgroup security

Prepare Access databases for migration (AccessToSQL)

Before you migrate Access databases to [!INCLUDE ssNoVersion], you must determine which databases to migrate and ensure that those databases are ready for migration.

Determine 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 [!INCLUDE ssNoVersion]. For more information about deciding when to migrate, see the migration information page on the [!INCLUDE ssNoVersion] Web site.

After you migrate databases to [!INCLUDE ssNoVersion], you can continue to use Access by using linked tables, or you can manually migrate your applications to .NET Framework-based code that interacts directly with [!INCLUDE ssNoVersion].

Determine 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 [!INCLUDE ssNoVersion]. For more information about how to export and query metadata, see Export an Access inventory.

Note

Not all Access features and settings are supported by, or can be easily converted to, [!INCLUDE ssNoVersion]. Before you start migrating databases, see Incompatible Access features.

Prepare for migration

Use the following guidelines to help prepare your Access databases for migration to [!INCLUDE ssNoVersion].

Upgrade 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.

Remove workgroup protection

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

  1. Copy the Access database file to another location.

  2. Open the copied database.

  3. On the Tools menu, point to Security, and then select User and Group Permissions.

  4. Select the Users option, select the Admin user, and then ensure that the Administer permission is selected.

  5. Select the Groups option, select the Users group, and then ensure that the Administer permission is selected.

  6. Select OK, and then on the File menu, select Exit.

You can now use SSMA to migrate the copied database. After you load the schema into [!INCLUDE ssNoVersion], you can manually secure the database on [!INCLUDE ssNoVersion].

Back up databases

Before you migrate your Access databases to [!INCLUDE ssNoVersion], you should back up both the Access databases that you're migrating as well as the [!INCLUDE ssNoVersion] databases into which you 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 [!INCLUDE ssNoVersion] databases, see Back up and restore of SQL Server databases.

Document 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 select Documented.

Related content