SQL Server data can be transferred to a Microsoft Access database via importing or linking. Importing essentially creates a copy of the SQL Server data in the Access database. Once the data is imported, changes made in either the SQL Server database or the Access database are not reflected in one another. In other words, think of it as a one-way street. Linking, on the other hand, provides a direct connection between the two entities that will reflect any changes made to the data in either entity.

Importing comes in handy when have data stationed in your SQL Server database that you want to transfer to your Access database permanently. This specific tutorial will explain how to import SQL Server Data to an Access database. An upcoming tutorial detailing the linking process will be posted in the future.

How to Get Your Data Ready Before Import

Before you begin preparing for the import process, you will obviously need to have the proper information in place to connect to your SQL Server database. If you are not the database administrator, contact them and secure the relevant login information first.

Once you have the necessary information, connect to the SQL Server database containing the data you are planning to import to Access.

You will have the option to import several objects (tables or views) at the same time. As is usually the case when importing data from different programs, errors can occur if the formatting is not correct before the import begins. To ensure that your database’s source data gets imported without problems, follow the guidelines in this checklist:

1. Your data must be limited to a maximum of 255 columns, as that is all that Access will support.

2. Your source data cannot exceed 2GB in size. Access limits database size to 2GB, and you will also have to concede some storage for system objects as well. If your SQL Server source data is very large due to having multiple tables, you will probably encounter an error when trying to import them via an .accdb file. Should this apply to you, linking your SQL Server data to Access will provide a solution to your woes. You will be shown how to link the data in an upcoming article in this series.

3. It will be necessary to manually create relationships between new and existing tables due to the fact that Access does not create them automatically once the data is imported. You can create the table relationships by selecting the File tab, clicking Info, and selecting Relationships.

Now that you have looked over your SQL Server source data and checked that it meets the prerequisites, go to the Access database that will be the destination for the data. Before you can import anything to the Access database, you will need the appropriate permissions to do so.

You can either add data to an existing database, or add it to a fresh, blank database. To add data to a blank database, select File, click New, and select the Blank Database option. If you are adding data to an existing database, check over the tables to see that there are no errors.

How to Import Your Data

With your data prepared, it’s now time to finally import it. Open up your Access destination database, and click on the External Data tab. Go to Import & Link, and select the ODBC Database option. Select the Import the source data into a new table in the current database option. Click OK.

You must now select your data source, or .dsn file. If you see your .dsn file, click on it and select OK. If you need to create a new .dsn file, clicking New will bring up the Create New Data Source wizard, which is detailed in the next paragraph of this tutorial. If you already selected your data source, skip the next two paragraphs.

In the Create New Data Source wizard, select SQL Server from the driver list. Click Next. Enter a name for the .dsn file. You’ll need write permissions for the folder where you are saving the .dsn file. Click Next, followed by Finish.

You should now see the Create a New Data Source to SQL Server window. You have the option of entering a description of the data source in the Description box. Enter one in, or leave it blank. Enter the name of the SQL Server you want to connect to in the corresponding box, and click Next. Choose whether to use Windows NT authentication or SQL Server authentication, and click Next. Check the box next to the Change the default database to option if you plan on connecting to a specific database. Click Next, followed by Finish. Check over the summary, and select Test Data Source. If your test results are fine, click OK twice. Click OK once again until the Select Data Source window is closed.

In the Import Objects window, go to the Tables. Here is where you click on the tables or views to import. Click OK to begin importing the data.

It is recommended that you save the steps you just performed as an import specification. This will save you time as you repeat the process in the future. In the Get External Data – ODBC Database window, look for and add a check in the box next to the Save Import Steps option. Enter a name for the import specification. You can optionally enter a description in the appropriate box as well. If you have Microsoft Outlook 2010 installed and want to execute the data import during specific times, check the box next to the Create Outlook Task option. Otherwise, you can run the import at your own discretion without the help of Outlook. Click Save Import to save the specification. You will now have the import specification at your fingers the next time you want to import data between SQL Server and Access.