How to Migrate Data from Access Database to SQL Server?

Migrate Access Database using SSMA for Access

 SQL Server Migration Assistant for Access is a part of a series of tools, called SQL Server Migration Assistant (SSMA), developed by Microsoft. This tool is designed to automate and simplify the process of database migration. You can use this tool for migrating data from any version of Access - from 97 to 2010.

 With this tool, you can easily convert all the Access database objects into SQL Server database objects or Azure SQL database objects and then migrate them to a SQL Server or Azure SQL database.

You can freely download the tool version 9.3.

 To use this tool, you need to fulfil the following prerequisites:


 The installation procedure is easy. Go to the official Microsoft SQL Server Migration Assistant download page and press the download button to download the installer (a file with the .msi extension)

 Choose the version of the installer you need and press the Next button.

Choosing download from Summary

Double-click on the downloaded file.

Selection for File SSMA for Access

Click the Next button in the welcome window.

SSMA for Access Setup Wizard

Accept the agreement and press the Next button.

SSMA for Access Setup

A message will appear if you are missing some prerequisites. In this case, exit from the installation program, install the missing prerequisite, and then run the setup again.

 Now choose the Setup Type. Choose “Typical” and press the Next button.

Choosing Typical Setup

Choose to enable (or not) both telemetry and automatic update checks when the SSMA starts.

Then, click the Install button.

Ready to Install

When the installation of the SSMA for Access is completed, click on the Finish button.

Finishing the Setup

Using SSMA for Access

Once the SSMA for Access is installed, you will find it on the Windows menu.

Microsoft SQL Server Migration Assistant

When you run the tool, a wizard welcomes you and shows you the steps you need to complete.


SQL Server Assistant for Access Wizard

First step is the creation of a project. In this, project name and its location are necessary fields.

You also need to choose the version of SQL Server you want to migrate data to.


Migrate to Server Selection


Press the Next button and specify the Access database. You can do this by clicking the Add database button or use the Find database button to search for one. Then, press the Next button.


Adding Access Databases


The left side of the window will display all the Access database objects (queries and tables) in a tree-view structure. For each table, additional information, such as the list of fields, indexes, and keys, is displayed.

 In the below example, we used an Access database where only a table called Campaign_Table exists. This table has three fields and a primary key.

Select Objects to Migrate

After preview, press the Next button.

 Enter the details needed to connect to the SQL Server instance and press the Next button.


Connect to SQL Server


If the specified database does not exist, it will be created. To do so, press Yes.


Creating Database Options


You can choose to link the Access data to the migrated SQL Server tables to use existing Access application with SQL Server. Press Next.


Link Tables


The migration process starts.


Migration Status

 The following window shows that the test table (Campaign_Table) does not exist in the SQL Server database. Press OK.


Synchronize with Database

Access objects are migrated. Press Close.


Migration Status

 Data migration from Access to SQL Server is finished using the SSMA for Access.

 After the data migration process, verify that all the data from Access database is exported to the SQL Server database.

In the main window, you can easily compare the read and written data.


Example project accessing Metadata Explorer


Migrate Access Database to SQL Server using SSMS

Alternatively, you can also use the SQL Server Management Studio (SSMS) in SQL Server to import Access database.

 For this, open Object Explorer, go to the database, and right click on it.

From the menu that appears, choose the Tasks option. Then, click on the Import Data item.


Object Explorer Options


Choose Microsoft Access as data source and specify the name of the Access database (mdb) file.


Importing Access Database engine

 Choose the target format. You can use the SQL Server Native Client 10.0 or 11.0 to import data into a SQL Server database.

 After the connection to the target SQL Server database, choose the tables to convert.

 Here you will see a button to preview the data to import and another one to choose the correct mapping of the columns between the two tables.

 Finally, press the Finish button to start the migration.


Completing Wizard

An Alternative Way to Migrate Access Database to SQL Server

 There are other ways to migrate Access database to SQL Server without using Microsoft products. You can migrate data from Access database using a database converter tool, called Stellar Converter for Database.

The installation of the Stellar Converter for Database is super easy. From the software’s download page, you can download the executable by pressing the Free Download button.


Stellar Convertor for Database tool Page

Run the executable.

 Press the Next button in the welcome window.



Accept the licence agreement. Press the Next button.


Accepting Agreement


Enter the path to install the application. Press the Next button.


Select file for setup

 Select the Start Menu folder to place the shortcuts. Press the Next button.


Select file for setup

Choose if you want to create Desktop shortcut or the Quick Launch shortcut. Press Next

Selecting Additional Tasks

Press the Install button to start the installation procedure.


Ready to Install

When the installation is finished, press the Finish button.


Finishing the Wizard

Now, let’s see how to use this tool. 

Start the program and choose the type of source database. You will see five options: MS Access, MS SQL, My SQL, SQL Anywhere, and SQL Lite.

Note: By default, data is read when the database is offline. You can also choose to read data directly from the “live” database.

 Enter the name of the Access database file and press the Scan button.

Selecting database


After scanning is complete, on the left panel, you will see the structure of the Access database.

Selecting Database

 Press the Convert button.


Press Convert

Now, if your application is licensed, conversion will start.


In this article, we talked about migrating Access database to SQL Server. We learned different ways to do this. We can use SQL Server Migration Assistant for Access – a tool specifically developed to automate and simplify the process of database migration. We have seen how to use SQL Server Management Studio (SSMS) to import Access database into SQL. We have also mentioned a useful database converter tool called Stellar Converter for Database to convert Access database into SQL Server (.mdf) format.

Was this article helpful?
About The Author
author image
Bharat Bhushan linkdin Icon

Technical Marketer at Stellar Information Technology Private Limited. He makes Tech concepts easy to understand with his strong grip on Technology.

Table of Contents


Why Choose Stellar?
  • 0M+


  • 0+

    Years of Excellence

  • 0+

    R&D Engineers

  • 0+


  • 0+


  • 0+

    Awards Received