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:

• Microsoft .NET Framework version 4.7.2 or higher
• Microsoft Data Access Object (DAO) provider version greater or equal to 12.0
• You must have credentials to connect to the SQL Server or Azure SQL Database instance.

 Installation

 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.

Double-click on the downloaded file.

Click the Next button in the welcome window.

Accept the agreement and press the Next button.

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.

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

Then, click the Install button.

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

Using SSMA for Access

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

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

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.

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.

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.

After preview, press the Next button.

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

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

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

The migration process starts.

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

Access objects are migrated. Press Close.

 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.

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.

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

 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.

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.

Run the executable.

 Press the Next button in the welcome window.

Accept the licence agreement. Press the Next button.

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

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

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

Press the Install button to start the installation procedure.

When the installation is finished, press the Finish button.

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.

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

 Press the Convert button.

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

Conclusion

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.