Transferring Data from MS Access 2003 to SQL Server 2008

Exclusive offer: get 50% off this eBook here
Learning SQL Server 2008 Reporting Services

Learning SQL Server 2008 Reporting Services — Save 50%

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

£18.99    £9.50
by Dr. Jayaram Krishnaswamy | July 2009 | Microsoft

The Export and Import Wizard is an extremely useful tool for transferring data. In fact it is the simplest tool to copy over data from one database to another and to create data transfer packages that can be persisted. Data can be transferred between SQL Servers (between versions of SQL Servers for example) as well as between even two non-Microsoft databases. Both Microsoft and proprietary data source providers are available to connect to many different database products.

In this article by Dr. Jayaram Krishnaswamy, we will be transferring data from an MS Access database to a database on SQLServer 2008. Both the source of data and the destination database are on the same machine, in this case a computer box running Windows XP Pro. There are two versions of the Import and Export Wizard and in this example the 32 bit version is used.

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

Learning SQL Server 2008 Reporting Services A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008
Published: March 2009
eBook Price: £18.99
Book Price: £30.99
See more
Select your format and quantity:

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 :

Learning SQL Server 2008 Reporting Services A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008
Published: March 2009
eBook Price: £18.99
Book Price: £30.99
See more
Select your format and quantity:

About the Author :


Dr. Jay Krishnaswamy is a graduate of the Indian Institute of Science, Bangalore writes on database and web development related topics to several computer programming related web sites. He is an active participant in several forums and discussion groups. Before working in the IT industry as a Microsoft Certified Trainer and a Siebel Certified consultant he taught at several institutes of technology and universities in India, Australia, Brazil and the USA. Links to his articles may be found at his website or the blog. He lives in Plainsboro, NJ, USA and may be reached at jkrishnaswamy@comcast.net

Books From Packt

Entity Framework Tutorial
Entity Framework Tutorial

SOA Patterns with BizTalk Server 2009
SOA Patterns with BizTalk Server 2009

Implementing Microsoft Dynamics NAV 2009
Implementing Microsoft Dynamics NAV 2009

ASP.NET 3.5 Social Networking
ASP.NET 3.5 Social Networking

ASP.NET 3.5 Application Architecture and Design
ASP.NET 3.5 Application Architecture and Design

WCF Multi-tier Services Development with LINQ
WCF Multi-tier Services Development with LINQ

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

Small Business Server 2008 – Installation, Migration, and Configuration
Small Business Server 2008 Installation, Migration, and Configuration

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software