I recently had to migrate data from an Access database to SQL Server 2014 because my database was getting too big for Access. Although the process is pretty simple, I decided to 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 are downloading the Extended Services version. Otherwise, the database engine will not be installed and you will have SQL Management Studio without any database instance to connect to.
Once you have correctly installed SQL Server, you can continue with the next steps to import your Access database into a new database on SQL Server.
Transfer an Access database to SQL Server
Open SQL Server Management Studio and connect to the database server where you want to import the Access database. In the Databases section, right-click and select New Database. If you already have a database and just want to import a couple of tables from Access, just skip that and skip to the Import Data step below. Just right click on the current database instead of creating a new one.
If you are creating a new database, give it a name and adjust the options if you want to change the default.
Now we need to right click on the test database we just created and select Tasks and then Import Data.
In the Select Data Source dialog box, select Microsoft Access (Microsoft Jet Database Engine) from the drop-down list.
Next to the File Name box, click Browse, navigate to the Access database you want to import, and click Open. Please note that the database cannot be in Access 2007 format or higher (ACCDB) because SQL Server does not recognize it! Therefore, if you have an Access database from 2007 to 2016, first convert it to the 2002-2003 database format (MDB) by choosing File – Save As.
Go ahead and click Next to choose your destination. Since you right-clicked the database you wanted to import data into, it should already be selected in the list. If not, select SQL Native Client from the Destination dropdown list. You should see the database instance in the Server Name section and then be able to select a specific database at the bottom as soon as you choose an authentication method.
Click Next and then specify how you want to transfer data from Access to SQL by selecting Copy data from one or more tables or Write a query to specify the data to transfer.
If you want to copy all tables, or just some tables from an Access database without any data manipulation, choose the first option. If you only need to copy specific rows and columns of data from a table, choose the second option and write an SQL query.
By default, all tables should be selected, and if you click the Edit Mappings button, you can customize how the fields are mapped between the two tables. If you have created a new database for import, this will be an exact copy of it.
Here I only have one table in my Access database. Click Next and you will see the Run Batch screen where the Run Immediately checkbox should be checked.
Click Next and then Finish. After that, you will see how the data transfer takes place. Once complete, you will see the number of rows passed for each table in the Message column.
Click Close and you’re done. Now you can go ahead and run SELECT on your tables to make sure all data has been imported. Now you can use the power of SQL Server to manage your database.
Having trouble importing data from Access into SQL Server? If so, please leave a comment and I’ll try to help. Enjoy!