Transferring Data from MS Access 2003 to SQL Server 2008

A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008

Launching the Import and Export Wizard

Click Start | All Programs| SQL Server 2008 and click on Import and Export Data (32 bit) as shown in the next figure.

Transferring Data from MS Access 2003 to SQL Server 2008

This brings up the - "Welcome to the SQL Server Import and Export Wizard”. Make sure you read the explanation on this window.

Transferring Data from MS Access 2003 to SQL Server 2008

Connecting to Source and Destination databases

You will connect to the source and destination. In this example the data to be transferred is in a MS Access 2003 database. A table in the Northwind.mdb file available in MS Access will be used for the source. The destination is the 'tempdb' database (or the master database). These are chosen for no particular reason and you can even create a new database on the server at this point.

Connecting to the Source

Click Next. The default window gets displayed as shown. The data source is SQL Server Native Client 10.0.

Transferring Data from MS Access 2003 to SQL Server 2008

As we are transferring from MS Access 2003 to SQL Server 2008 we need to use an appropriate data source. Click on the drop-down handle for the data source and choose Microsoft Access as shown in the next figure.

Transferring Data from MS Access 2003 to SQL Server 2008

The 'SQL Server Import and Export Wizard' changes to the following. Click on the Browse... button to locate the Northwind.mdb file on your machine also as shown. For Username use the default 'Admin'.

Transferring Data from MS Access 2003 to SQL Server 2008

Click the Advanced button. It opens the Data Link properties window as shown. You can test the connection as well as look at other connection related properties using the Connection and Advanced tabs.

Transferring Data from MS Access 2003 to SQL Server 2008

Click OK to the Microsoft Data Link message as well as to the Data Link Properties window. Click Next.

Choose Destination

The Choose a Destination page of the wizard gets displayed. The Destination source (SQL Server Native Client 10.0) and the Server name that automatically shows up are defaults. These may be different in your case. Accept them. The authentication information is also correct (the SQL Server is configured for Windows Authentication). Click on the drop-down handle for the Database presently displaying <default> as shown.

Transferring Data from MS Access 2003 to SQL Server 2008

Choose tempdb and click on the Next button.

Choosing a table or query for the data to be transferred

You can transfer data in table or tables; view or views by copying them over, or you may create a query to collect the data you want transferred. In this article both options will described.

Copying a table over to destination

The Specify Table Copy or Query page of the wizard gets displayed as shown. First we will copy data from a table. Later we will see how to write a query to do data transfer.

Transferring Data from MS Access 2003 to SQL Server 2008

Click Next. The Select Source Tables and Views page gets displayed as shown. When you place a check mark for say, Customers, the Destination column gets an entry for the selection you made. We assume that just this table is transferred. However you can see that you can transfer all of them in one shot.

This window also helps you with two more important things. First you can Preview... the data you are transferring. Secondly, you can edit the mappings, the scheme of how source columns go over to destination.

Transferring Data from MS Access 2003 to SQL Server 2008

You can modify columns

Click the Edit Mappings... button. The Column mappings page gets displayed as shown. If you go ahead with this choice by clicking OK, the default action, Create destination table with the information present in this window will be used. You could edit the SQL, that is customize a scheme for the transfer (You may not need all columns for example, you may need a different data type, and for other reasons).

Transferring Data from MS Access 2003 to SQL Server 2008

For now accept the default by clicking OK (that is to say, no changes were made for column mappings).

In the Select Source Tables and Views page click Next.

Start moving the data

Now the Save and Run Package page gets displayed. There are two options. You can use the first option if you are in a hurry or you can save the Package as well as run.

Transferring Data from MS Access 2003 to SQL Server 2008

Accept the default (only check mark for run). The Complete the Wizard page gets displayed. Showing your choices for source and destination; the column mappings and regarding saving package for later use.

Transferring Data from MS Access 2003 to SQL Server 2008

Click Finish. The process of transfer begins immediately as shown reporting the progress.

Transferring Data from MS Access 2003 to SQL Server 2008

Transfer is done

When all transfer process is finished you should be seeing the following screen. There were no errors in this case. Depending on what you are transferring you may get some errors which show up as hyperlinked information in the message column.

Transferring Data from MS Access 2003 to SQL Server 2008

You are done. If you want to see a report you could click on the drop-down button and pick an item to see such as , View Report, Save report to file, Copy report to clipboard and send report as an email to some one who needs the information. Click Close.

Verifying Transferred data

Open SQL Server 2008 Management Studio and connect to the server by logging-in. Expand Databases node and expand the System Databases node. Open the Tables node in tempdb as shown. You will see that the Column properties of the transferred table dbo.Customers closely match the data you saw earlier before (remember that no column mappings were made).

Transferring Data from MS Access 2003 to SQL Server 2008

Use Query to transfer data

We saw at an earlier page of the wizard there were two possibilities of data transfer, copy table(s) or View(s) from source to destination or write a query to transfer data. In this section we will look at the second option.

We go through all the steps for the same source and destination (Master database on SQL Server 2008) as discussed earlier and choose to write a query.

Click Next. This opens the Provide a Source Query of the Wizard as shown.

Transferring Data from MS Access 2003 to SQL Server 2008

Type in the SQL Statement shown in the window. You can test the syntax with the Parse button which should verify the validity of the statement.

Transferring Data from MS Access 2003 to SQL Server 2008

Close the message by clicking Ok and click on Next on the Provide a query Source page.

You get the Select Source Tables and Views as shown. You can change the query name in the destination column to one of your own. You could also Edit Mappings of column as well as preview the data.

Here the query was changed to SpecialOrders by typing over the default dbo.query.

Transferring Data from MS Access 2003 to SQL Server 2008

Click Next. In the window that shows up accept the default to 'RUN immediately' and click Next. Click Finish on the 'Complete the Wizard' page. The processing window shows up indicating a success.

Transferring Data from MS Access 2003 to SQL Server 2008

Verify the transfer in the Management studio as shown in the 'master' database.

Transferring Data from MS Access 2003 to SQL Server 2008

Summary

This article shows how you may transfer table(s), View(s) or query results to SQL Server 2008 from an MS Access database. Export / Import tool does not have a graphical query designer such as the one you find in the SQL Server Management Studio and you will need to have a statement ready to transfer the data returned by a query. During the transfer of data we did not save a package, but a saved package can be run later.

If you have read this article you may be interested to view :

Books to Consider

Microsoft SQL Server 2008 R2 Administration Cookbook
$ 32.99
Microsoft SQL Server 2012 Integration Services: An Expert Cookbook
$ 26.99
Creating Reports with SQL Server 2012 Reporting Services [Video]
$ 25.50
comments powered by Disqus