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, calledSQL 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 official MicrosoftSQL Server Migration Assistant download pageand press thedownloadbutton to download the installer (a file with the .msi extension)

Choose the version of the installer you need and press theNextbutton.

Double-click on the downloaded file.

Click theNextbutton in the welcome window.

Accept the agreement and press theNextbutton.

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“典型”and press theNextbutton.

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

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 aproject. 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 theNextbutton and specify the Access database. You can do this by clicking theAdd database buttonor use the Find database button to search for one. Then, press theNextbutton.

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

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

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

You can choose to link the Access data to the migrated SQL Server tables touse existing Access application with SQL Server. PressNext.

The migration process starts.

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

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 theSQL 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 theImport Dataitem.

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

还有其他方法来访问数据库迁移SQL Server without using Microsoft products. You can migrate data from Access database using a database converter tool, calledStellar Converter for Database.

Theinstallationof the Stellar Converter for Database is super easy. From the software’s download page, you can download the executable by pressing theFree Downloadbutton.

Run the executable.

Press theNextbutton in the welcome window.

Accept the licence agreement. Press theNextbutton.

Enter the path to install the application. Press theNextbutton.

Select the Start Menu folder to place the shortcuts. Press theNextbutton.

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

Press theInstallbutton to start the installation procedure.

When the installation is finished, press theFinishbutton.

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

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 useSQL 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 usefuldatabase converter toolcalledStellar Converter for Databaseto convert Access database into SQL Server (.mdf) format.