Migrate from MS Access to SQL Server 2005 database

Posted on September 16, 2008 at 5:39 am

Recently, I had to migrate the data from an Access database to SQL Server 2005 because my database was getting too large for Access to handle. I decided that since it’s a fairly simple process, but something that many people might be doing, that I would write it up here as an article.

I’ve also written two other migration articles that you might be interested in: Migrating pictures from Flickr to Picasa and migrate from iPhone to iPhone 3G. Now back to Access to SQL. 

First, you need to make sure you have SQL Server 2005 or SQL 2005 Express installed on your computer along with the SQL Server Management Studio. If so, you’re ready to export your Access database to SQL 2005.

Transfer Access database to SQL Server 2005

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.

new database sql 2005

Give it a name and configure the other settings as needed and click OK. Now we need to right-click on the database we just created and choose Tasks and then Import Data.

sql 2005 import data

On the Data Source dialog box, scroll up until you see Microsoft Access.

import microsoft access data

Next to File, 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 format as SQL Server 2005 does not recognize it! So if you have a 2007 Access database, first convert it to the 2002-2003 Access format by going to Save As.

mirgrate access to sql

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.

destination database

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.

access to sql server

In most cases, if you want to just transfer the entire database, you should choose the first option. If you want only a portion of the data from a particular table, you can write out the SQL query. Click Next.

By default all the tables should be selected and if you click the Edit button under Mapping, you’ll see the the destination database will create all of the tables to match what’s in the Access database.

transfer database access to sql

Here I have only one table in my Access database. Click Next and then choose whether to execute immediately or to save it as a file to perform later.

save as dts package

Click Next and then click Finish. You’ll then see the progress of the data transfer as it occurs.

transfer data

After it is completed, you’ll see the number of rows transferred for each table in the Message column.

data transferred

Click Close and you should now have all of your Access data nicely imported into a SQL Server 2005 database, which you can then use for whatever you like.

access database

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

Enjoyed this post? Subscribe to Online Tech Tips via RSS Feed or via Email and receive free daily productivity tips.

» Filed Under MS Office Tips

Add to Delicious Save this page Mixx it Stir it up on Mixx
Reddit Add to Reddit StumbleUpon Stumble this page  

Related Posts

12 Responses to “Migrate from MS Access to SQL Server 2005 database”

  1. raaghav said on : September 27th, 2008 at 2:05 am

    Thank you for yo help……….


  2. Larry said on : September 29th, 2008 at 3:17 pm

    I’m having an issue with an Access 2007 database being downconverted to 2002/3 and imported. I need to use this for InfoPath, and one of my tables (a repeating table) is not importing as such and causing issue in my form. Any advice?

    Thank you,
    Larry


  3. Jayateertha said on : October 1st, 2008 at 5:43 am

    Hello Sir!
    i am getting one error while executing this. It says AcquireConnection Failed. please Help


  4. Pat said on : October 1st, 2008 at 12:08 pm

    After I move my access tables and views to sql 2005. How can I make web-based front end instead of Access front end? Thanks.


  5. Lawrence Bird said on : October 6th, 2008 at 1:29 pm

    I have no import tab on my sub menu!!!


  6. Uday said on : October 9th, 2008 at 1:09 am

    I had no problem importing data I made changes to connection string also.I am getting login screen but when I try to login through admin it is giving me error Error connecting to database. Error reads: Operation is not allowed when the object is closed.Please suggest some help.


  7. Jim Boulton said on : October 22nd, 2008 at 1:38 pm

    very useful


  8. haider_up32 said on : October 23rd, 2008 at 12:16 pm

    a similar guide is available on mysql.com to migrate from access to Mysql(open-source)


  9. chandrima roy said on : October 23rd, 2008 at 4:32 pm

    I was importing database from MS-Access .it was successful but few data in the tables are not inserted instead null value was inserted.

    Can any one sugges me why I faced this problem?What is the solution for this?Is there any store procedure to solve such problem?


  10. Koly said on : November 6th, 2008 at 3:18 am

    There is no “Import Data …” option on my Tasks Sub-Menu lists.


  11. Subbarao.kare said on : November 14th, 2008 at 2:06 am

    Very useful


  12. Harald said on : November 18th, 2008 at 12:47 am

    I don’t have the option “Import Data” under Tasks


Please post your comments/suggestions!