How do I convert an Access database to SQL?

Recently, I had to migrate the data from an Access database to SQL Server 2014 because my database was getting too large for Access to handle. Even though the process is fairly simple, I figured I would write an article with step-by-step instructions.

First, you need to make sure you have SQL Server or SQL Server Express installed on your computer. When downloading SQL Server Express on a personal computer, make sure you download the version with Advanced Services. If you don’t, then the database engine won’t be installed and you’ll have SQL Management Studio without any database instance to connect to.

Table of Contents

Once you have SQL Server installed properly, you can continue with the steps below to import the Access database into a new database in SQL Server.

Transfer Access Database to SQL Server

Open SQL Server Management Studio and connect to the database server you want to import your Access database into. Under Databases, right-click and choose New Database. If you already have a database and you simply want to import a couple of tables from Access, then just skip this and go to the Import Data step below. Just right-click on your current database instead of creating a new one.

If you’re creating a new database, go ahead and give it a name and configure the settings if you want to change them from the defaults.

Now we need to right-click on the Test database we just created and choose Tasks and then Import Data.

On the Choose a Data Source dialog box, select Microsoft Access (Microsoft Jet Database Engine) from the drop down box.

Next to File name, click on Browse and navigate to the Access database you want to import and click Open. Note that the database cannot be in Access 2007 or higher format (ACCDB) as SQL Server does not recognize it! So if you have a 2007 to 2016 Access database, first convert it to the 2002-2003 Database format (MDB) by going to File – Save As.

Go ahead and click Next to choose the destination. Since you right-clicked on the database you wanted to import the data into, it should already be picked in the list. If not, select SQL Native Client from the Destination drop down. You should see the database instance under Server Name and then be able to choose the specific database at the bottom once you pick your method of authentication.

Click Next and then specify how you want to transfer the data from Access to SQL by choosing either Copy the data from one or more tables or Write a query to specify the data to transfer.

If you want to copy all the tables or just some of the tables from the Access database without any data manipulation, choose the first option. If you need to copy only certain rows and columns of data from a table, then choose the second option and write a SQL query.

By default, all the tables should be selected and if you click the Edit Mappings button, you can configure how the fields map between the two tables. If you created a new database for the import, then it’ll be an exact copy.

Here I have only one table in my Access database. Click Next and you’ll see the Run Package screen where Run Immediately should be checked.

Click Next and then click Finish. You’ll then see the progress of the data transfer as it occurs. After it is completed, you’ll see the number of rows transferred for each table in the Message column.

Click Close and you’re done. You can now go ahead and run a SELECT on your tables to make sure all the data got imported. Now you can enjoy the power of SQL Server for managing your database.

Any problems importing your data from Access to SQL Server? If so, post a comment and I’ll try to help. Enjoy!

Microsoft Access is a popular desktop database management system that incorporates application development tools (Forms, Reports, Queries, VBA macros) with a relational DBMS. MS Access is ideal for individual users or for small workgroups where a few users work on the data at the same time by sharing an MS Access database file over a local area network.

However, once the number of simultaneous users grows above a handful, MS Access starts to encounter issues with sharing the data. One solution to this “scale up” problem is to split the MS Access database and host the data in a server based DBMS such as Microsoft SQL Server.

Microsoft provides a handy tool called the “Microsoft SQL Server Migration Assistant for MS Access” (SSMA) to assist in moving the database tables from an MS Access database to SQL Server. Before getting into the actual steps of this process it is helpful to understand the architecture of an MS Access database system. This architecture is described starting on the next page.

Pre-Requisites

Before proceeding any further with this tutorial, make sure the following software is in place.

This tutorial was written using Microsoft Access 2013 (from 64-bit MS Office) running on a Windows 7 64-bit operating system. MS Access 2010 and 2016 may also work but have not been tested. Be sure to check MS Office as the 32-bit MS Access will not have the proper support to allow SSMA to work.

You may also need to install the MS Access 2010 Runtime software. This software was originally designed to read MS Access database files without the need to install a complete version of MS Access. The QL Server Migration Assistant uses the Data Access Objects (DAO) parts in the MS Access 2010 Runtime to open up MS Access files.

A sample MS Access database file is provided (see next pages for links to this file).

The target database management system will be Microsoft SQL Server. This tutorial requires credentials (username, password etc.) to a SQL Server instance. You may install MS SQL Server on your own PC or server, use an existing server in your home or workplace, or use a cloud based service to host SQL Server for you. A tutorial on setting up a SQL Server instance on GearHost is provided here: /getting-started-with-gearhost-for-sql-server-database-development/

Note that as of 2021, GearHost no longer has a Free-Tier SQL Server instance. You may want to try obtaining a Student account on Microsoft Azure and using the Azure SQL Database.

You should also have the Microsoft SQL Server Management Studio available to directly explore and work with SQL Server. Use the SQL Server Management Studio to connect to your SQL Server instance and create an empty Database.

A SQL Server instance hosted by GearHost is used for this tutorial. A sample database named testmssqldb1 was created as shown in the SQL Server Management Studio Object Explorer below.

Finally, be sure to make a backup of any MS Access database file(s) before migrating them to SQL Server.

How do I open an Access database in SQL?

How to convert Access query to SQL?

How to convert MDB to SQL database?