How to import Microsoft Access into SQL Server 2005

I have a client that has an old Microsoft(MS) Access 2000 database that is attached to ColdFusion 6.1.

As one of the developers working on this project, I recommended, as did my colleague, that the client upgrade their database to SQL Server 2005 for better maintainability, scalability and development efficiency.

I have been using SQL Server since version 7.0 when Data Transformation Services (DTS) was introduced. DTS is an ETL or Extract, Transform and Load process that allows users to import and export data of many database types rather easily.

I started to research DTS with SQL Server 2005 and I found that service has a new name. DTS is now called SQL Server Integration Services (SSIS).

SSIS sounds like a great product that extends the previous DTS modules to be compatible with 2005 databases, but the question remained, “How do I import a MS Access database into SQL Server 2005?”

I started reading tech notes, blogs and forums for the solution. I noticed that this question is quite prevalent. The suggested solutions ranged from linked tables, the Microsoft Upsizing Wizard and ODBC datasource exports. I found that the suggested solutions did not sit well with me; I wanted a better solution.

My intuition told me that Microsoft would not release a database product unless they had some sort of migration tool or wizard that would help assimilate databases into the new 2005 database engine.

I am sure people are going to disagree with me on this one.

Regardless of Microsoft’s past response time to new product offerings, Microsoft will create a tool or console that will make it easier for more organizations to be apart of the Microsoft paradigm.

I am not peddling Microsoft’s software, but many of my clients do have MS Access or SQL Server for reasons of budget , consulting costs and apprehension with open source software.

I found a solution that solved my problem perfectly: SQL Server Migration Assistant (SSMA) for Access

I was surprised how many suggested solutions did not allude to this tool. SSMA is a free software product from Microsoft that has three versions (MS Access, Oracle, and Sybase).

In my efforts to help others, I have created a Microsoft Word document that will show you how to import Access into SQL Server 2005. Now, I say “into SQL Server 2005” as the documentation for the product does not discern the various versions of SQL Server 2005, so my intuition would suggest that this will work for other versions of SQL Server 2005.

Download the walk-through by clicking the following link:

Migrate Access to SQL Server 2005

110 Comments

  1. Nice job, Teddy. Thanks for finding and sharing this.

    I have added a pointer to this from my blog (http://carehart.org/blog/client/index.cfm/2006/7/24/migrate_access_sqlserver). Would have done a trackback, but I don’t see how to add those in BlogCFC.

    One other minor comment: where you link the tool at

    http://www.microsoft.com/sql/solutions/migration/default.mspx

    I’ll note that there’s a more direct link to the section of the page on the Access tool specifically, at

    http://www.microsoft.com/sql/solutions/migration/default.mspx#EYC

  2. Charlie,
    Thanks for the reference.

    You are right about having a more specific link. I should have both. I listed the home page for SSMA so that people could see all three versions (Oracle, Access and Sybase). I will create additional links in the Word doc for the specific versions.

  3. The link to the initial SSMA for Access was updated.

  4. Great article!
    Thanks for the pointers – and here’s one for you… http://www.DataMouse.biz
    My site with a growing larder of Access groceries…
    :-)

  5. Danny,
    Thanks for the added suggestion. I am sure other MS Office users will appreciate having more options than just Access support.

  6. Hi, nice job :)

    I have a question to everybody. I have an access db. i have made a new design for the datamodel and i want to migrate de data into sqlserver 2005.

    Do you know how to start? Because i only want to export de data that is in some tables, not the full database.

    Thanks all

  7. Curro,
    After you download the MS Word document from the post entry, you have the instructions that show you the ability to choose your tables that you want to import from Access to SQl Server 2005. The instructions show that I imported everything, but you can uncheck the names of tables.

    If you do not want to pick and choose, I would recommend creating a new database that you import everything into and then copy over the tables into the destination database as you need it. The document shows good detail and have several key images to point out what I am referring to.

  8. Nicely done!

  9. How I Can Convert Data In Sql Server 7.0 Into Sql Server 2005. IF Any One Knows This Answer Please Tell Me On My Email Id dashah_323@yahoo.co.in.

    Please Reply As Soon As Possible

    Thanks & Regards

    Dharmesh A. Shah

  10. Thanks for the great article!

    Maybe an year ago I had to migrate Access database to SQL Server 2000. Finally I posted by experiece in this on my blog.

    If you’re interested, you can read all the problems occured and how I solved them by reading my post http://www.aspnettricks.com/archives/migrate-access-database-to-ms-sql-server-2000/.

    Good luck!

  11. Thanks for the information.

  12. Hi,
    I found your blog via google by accident and have to admit that youve a really interesting blog :-)
    Just saved your feed in my reader, have a nice day :)

  13. Thanks very much!

  14. Muy buena la informacion, gracias.

    Thanks very much, i needed this info.

  15. Just what I was looking for ( for the last 3 days). Followed your instructions and my Access data has been migrated to Sql Server Express. Thanks….

  16. Very nice…….I just migrated my access db to sql express without any problems…

    Once again, very nice how-to.

    Regards,

    Craig M.

  17. Thank you very much. This was a great help.

  18. Thank you for the link – it did what the SQL 2005 Import Wizard could’nt handle.

  19. I use the MS Access 2003 wizard to import data to SQL 2005. It works like a charm, all data are imported. Although, you need to recreate the relationship and reindex the table.

  20. Thanks for sharing this valuable information.

    Jason from Irvine, CA

  21. Hi,
    I was just curious abt one thing. Why is required to have VJ# installed prior to migration? How is VJ# important for this process? Would anyone care to enlighten me?
    Thx

  22. This is an article to be respected!

    Thanks a lot.

    • Suparuksa Boonyacharti
    • Posted September 6, 2007 at 10:13 pm
    • Permalink
    • Reply

    Nice suggestion it’s very useful for me. Thank you a lot.

  24. Hi
    I’m trying to transfer a database from access to sql, i used your instructions but when i get to connect to sql server it says an error has occurred while establishing a connection. This may be due to the fact that under the default settings SQL server does not allow remote connections. (provider: Named Pipeds Provider, error 40 – Could not open a connection to SQl server)

    Please help??

  25. Forgot to add I’m using access 2003 and sql server 2005 express editions

  26. Nice Guide saved me a ton of work…

  27. Hi,

    Thank you for the explanation for importing an access db to ms sql 2005 server. It was really useful. I’m a student trying out stuff. I do have a question though, all the additional software that i need to download, I need to install all that on the ms sql 2005 server? You see, i’m a trainee in a firm where they have a large amount of access (and works) databases locally in each department. Recently they have a db server with mssql 2005 installed on it. Now, my job is to import all the seperate access (and works) databases to the ms sql 2005 server. So to be able to do it like you explained it, i need to have access to the sql server so i can install all the additional software right?….i’m new to all this so please bare with me….All information that you can give me on how to do this (step by step) would be highly appreciated. Please email me if possible.

    Thank you!!

  28. forgot to ask, can i also use my laptop and home pc to experiment on this? my laptop with the mssql 2005 installed (with the additional software you recomended) and my home pc with the access databases from where i have to transfer the db’s to my laptop….is that possible? do i need to know anything else before doing this?

    thank you!!

  29. ottimo funziona tutto!!!

  30. Great Article !!! I have one question. have anyone tried to automate the process for migration after saving the project in the tool ? If you have done it succesfully , can you please share with me ?
    Thanks.
    TJ

  31. To: SSC

    You have to enable remote connection via TCP/IP and named pipes.
    Do folowing:

    Menu Start -> Programs -> MS SQL Server 2005 -> Configuration Tools ->
    -> SQL Server Surface Area Configuration

    In SQL Server Surface Area Configuration
    click on SQL Server Surface Area Configuration for Services and Connections.

    In the windows tahat appears select your SQL Server and click on Remote Connections node. Click on radio button “Local and remote connections”
    and than on “Using both TCP/IP and named pipes”. Than click on “Apply”.

    After that you have to restart SQL Server Service, so click on service node,
    click on stop and than on start. So you restart SQL Server Service.

    Try your connection to SQL Server 2005

  32. Came across you blog, but if you have SQL Server Management Server (Sql Server 2005) installed, then you can just create database, choose tasks and import data. Supports a bunch of source database, including Access.

    It worked great and you might want to put a note for other people.

  33. Nicely done … great job … I used the SQL 2005 import feature before but had some problems with the keys. Your document helped me to get the import done. Again, thank you for your detailed documentation.

  34. Great app, but man…why on earth is this application not accepting a database name with a dot in it….??
    My db is called oxle.com, and as soon as I enter the dot in the database field to connect to SQL, it disables the connect button….??

  35. Hi, nice article – just wondering if there is such a tool to do this with SQL server 2000? Also I can only use dot net framework 1.x and this tool needs dotnet framework 2 to work.

    Thanks,
    Gary

  36. Thanks so much for sharing this great ’secret’.
    Why is this assistant soooo hard to find when you need it?
    Thanks again….really appreciate it.

  37. Great job it was exactly what i needed. keep the good work.

  38. Thanks so much for this

  39. I did this before and i only wish i had seen your article ..
    there is also some good info at http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3
    and http://www.theithelper.com

  40. Greetings all

    There are a number of customers which use a database on runtime access, some of these wish to convert to SQL but do not have the upsizing wizard with runtime. I have created a VBA script which converts the database structure to SQL and it works fine and does exactly what I want it to. The problem comes trying to insert the data..

    I use ‘dBase.ExecuteImmediate “SET IDENTITY_INSERT ” & tdf.Name & ” ON”‘ so that I can insert the existing autonumber value into the Identity field but I get the following error when the script runs on a table with no Identity field
    ‘Table x does not have the identity property. Cannot perform SET operation’

    Does anybody know how I can check if the identity property exists for a table?

  41. Great write up! Worked perfect.

  42. Thanks for this! My boss gave me a ZIP Codes / State database in Access file (.mdb) and this helped me get it into SQL 2005 Express.

    Strange this is not more publicized on SQL Express / SQL Server Microsoft sites.

  43. I had to do a migration task and the estimate was 2 weeks. I finished in 2 hours.

    All thanks to you.

  44. Thanks for the documentation…great stuff!

    If you aren’t opposed to using 3rd party tools to migrate data between databases, you should check out the data tools at this web site: http://pervasivedatatools.com.

    I’ve used some of their tools and they worked great for me. They’ve got trials and some are free…at least for now.

  45. Good JOB! excellent

  46. Author, it’s your best post in this blog. I sent it to all my friends! Big thanks for article.

  47. You are the lifesaver!!

  48. i wanted to let you know that i found this your blog as well as this tool very useful. it really saved me a lot of time and i was able to migrate from access to sql thank you for your post.

  49. Greate article which works!
    Keep on. Many thanks.

  50. Thank you very much. This was a great help.

  51. Thanks for your HowTo!!

  52. I just can’t seem to figure this migration wizard out. I go all the way after converting the old database, “connect to SQL Server 2005″ window. Then, it gives me an error saying. Basically, couldn’t connect to the server, make sure you allow remote connections to server, error 40. I checked the remote connections deal and that seems fine.. arr.. sql server is just on the same machine.. anyone experienced this?

  53. Thanks for the article. Fairly new to SQL Server and have a couple of customers who want to upsize from Access. Lots of stuff around on Upsizing but nothing about migrating latest copies of live data once the upsize was done and further development had changed the SQL definitions away from the Access ones – until I found this little gem! Great!

  54. Thank you for the info. It was most helpful.

  55. Ran into an issue importing an Access 2003 database into SQL 2005 and this write up saved me many more hours of troubleshooting. Thanks!

  56. Thank you, this got the db into SQL.

    Do you have any tips on getting Access data into an existing SQL table with similar but not identical structure? I’ve been trying to use the DTS Wizard but I can’t get it to let me map the relative fields, even after converting it to SQL.

    Thanks again.

  57. very nice article. Have a look at access repair for more queries regarding microsoft access error.

    Vish…

  58. Thanks very much, i needed this info.

  59. Is it possible to merely link tables (and modify data)from SQL Express to an Access 2007 front end. All I need is some way to update the SQL from forms in Access and build reports, queries, etc. locally to be used locally. SQL Express database is on a remote server and we are a few states away. Need to have rights to the data only and be able to see changes. Rookie here: need the best and most efficient way to do this … I am not the SQL admin … only a remote user … but am allowed most rights.

  60. That tool is Awesome! Thanks Dude.

  61. This is an awesome tool!! Thanks for the documentation

  62. Somehow i missed the point. Probably lost in translation :) Anyway … nice blog to visit.

    cheers, Cerebrum.

  63. I would recommend creating a new database that you import everything into and then copy over the tables into the destination database as you need it.

  64. You Rock!

  65. Thanks. This was immensely helpful.

  66. Great! Exactly what I was looking for. Lots of thanks.

  67. Amazing tool!!How did you figure that out? Well done!thanks a lot!

  68. Thanks for this initiative, document, & tools.

    However, this tool doesn’t work with Access 2007. Can someone suggest something on migrating data from Access 2007 (*.accdb) to SQL Server 2005?

    Cheers,
    Anshu

  69. Found newer version of the same tool to migrate data from MS Access 2007 (*.accdb) to SQL Server 2005.

    http://www.microsoft.com/downloads/details.aspx?FamilyID=4ecd1e67-c64e-49e6-821e-c4d83d9d5fed&DisplayLang=en

    Cheers,
    Anshu

  70. Hi, Thanks for the documentation.
    Something however, I can’t find here or anywhere else so far, is how to edit hese schemas before you actually migrate. Most articles seem to indicate this is possible. Is this actually possible? If so, how?

  71. Thanks for putting together the directions on how to do this. You saved me a lot of time and work.

  72. Thanks for putting this together it was very helpful.

  73. This is so helpful, I was stuck with an error trying to use the regular “tasks > import data…” for a DB with up to 70 tables, but this solved my problem, thanks dude!

    Now it’s in SU

  74. I agree with author

  75. Hey all.. I am adding migration tool for access, as instructed, and I am running MS SQL 2005 express on my XP Pro, and the database is fine, but when I click to ‘connect to sql server’, it isn’t finding it.
    Any ideas?

  76. Add to bookmarks. Now I will often read!

  77. It worked all of a sudden.. not sure why.
    The only thing I did was switch from mixed mode auth (because I saw something that said it works best with windows auth) to windows only auth. I am not sure that did it, but it works fine now.

    TP

  78. Read with great interest, very interested in history

  79. Really a nice atricle.
    Good work.

    Thanks.

  80. Interesting post. thanks!

  81. Great help I ave ben struggling with this for class for the past week. This was very helpful. I look forward to more of your tips and tricks

  82. hey,

    I have an existing application running on the .Net platform using MS Access as the database. What I have to achieve is, design the data in MS SQL Server 2005 and populate the data in SQL from my existing .Net application that is using MS Access locally. The sesond thing to do is that the application should primarily use the SQL database for populating the data but if the connection is not available or if there is some error then it should use the MS Access database for population.
    How do you think I can achieve this with minimum maintenance in long run?
    Is there a way to create a .Net class library that will do all this for me, if yes then how?
    Your help is really appreciated, I have never done this kind of stuff before, will be good if someone of you can explain it.

    Thanks,
    Anand

  83. please send me an email…i will follow up this blog too..

    thanx again!

  84. Hi all,
    Great job! very useful for non-sql2005 experts!

    but how to upload information after? I mean I created db with this tool but as the application used is a very old one I still have information within access db but sql server 2005 to access from an intranet.

    I need to “feed” 2005 with new information but each time I try to upload information I have access problems.

    Could you please help me on how to create a proper connection to refresh information onces all tables are created?

    thanks so much.

  85. thank you
    http://kypitesporno.ru/buy-prono-za-sms/

  86. u rock man this is GREAT !!

  87. Greate and useful article which works!
    Many thanks.

  88. Excellent article. Thank you very much for the info.

  89. thank u

  90. thank u for sharing the useful information with us
    I have also migrated my access db to sql express without any problems…
    you have save my time

  91. 6TJUWq doors1.txt;25;55

  92. Ololo! I like what is written here!!

  93. Vell, nоt perfесt pоst, but I liked it аnd thаt is thе main thing. ;)
    I am Voli Dublino

  94. I have a querry
    I tried to import database created in access to sql server 2005
    latest version, but it does’nt show me the Import option when
    i right-clicked on the new created database->tasks->no import option availabel.
    Where can i find the import option
    please help me.

    Bye.

  95. Seems good to me. (flex bahth house)

  96. i have a problem….

    during the import i need to rename a few of the tables and about 15 of the fields.. which would be fine if i was only doing it once

    i am doing it once on old data .. and then again when we go live (this would have been easy in the old sql server 7.. but seems impossible with SSMA for access)

  97. \(^_^)/
    thx from France!

  98. i have a problem.
    i have to migrate from access to sql server 2005. but i have a problem with the empty fields, because the migration tools converts empty fields to NULL.
    is there a way to set another value instead of NULL?

    thanks.

  99. I had a little problem to import access database into SQL server.
    This post helps me out. Thanks a lot.

  100. You can use the Upsizing wizard in MS Access to convert your access database to a SQL Server database.

  101. Thanks.

  102. Firstly thanks for the good advice, it helped a lot.
    I had the same problem as one of the posters, that I could not connect in the migration tool and I have tried every trick in the book, and all the combinations, as advised and more.
    Different ports, ON/OFF my Firewall, TCP/IP, name pipes etc. all of them

    Then I found this little gem in Microsoft site

    http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&DisplayLang=en

    which gives me the SQL server management GUI similar to the good old fashioned Version 6 and 7 I was used to.

    For some reason the migration tool also started to work as soon as I installed this Studio application.

    It could be some missing components that gets installed by the studio, I am not sure.

    One more thing, in the studio it shows the Server name as default, I typed the same in the Migration tool and it did not work, I copied and Pasted the name and it worked.
    Is that just a freak coincidence?

    Hope this help some people.

  103. I have a problem,
    to import the all data from .mdf file to .mdb.

    Please help and send my mail address.

    Thank’s

  104. Amazing find. I have just successfully migrated over 36 tables with tons of dependencies and data. Thanks

  105. Great!!! I used Anshu’d link sucessfully immigrated table from access2007 to SQL sever2005!

    Thanks,!

    http://go2.wordpress.com/?id=725X1342&site=cfpayne.wordpress.com&url=http%3A%2F%2Fwww.microsoft.com%2Fdownloads%2Fdetails.aspx%3FFamilyID%3D4ecd1e67-c64e-49e6-821e-c4d83d9d5fed%26DisplayLang%3Den

    • Deepak K. Sharma
    • Posted September 25, 2009 at 6:04 am
    • Permalink
    • Reply

    gr8 work,

    thanks

  107. Thank you for your kind help. Your word document was very much helpful.

    Thank you and best regards.

  108. This is really useful topic. But i’ve a question that i used to import a table/data from MS Excel or MS Access to SQL using MS Sql server management studio->Database-> Tasks-> Import Data …..
    Is this way appropriate or not?? Just want to know the difference in the one u explained & the way i do….thanks

  109. Thanks a million for posting that.
    i just needed it there and it worked perfectly.

    cheers
    Elaine


3 Trackbacks/Pingbacks

  1. By How to migrate from MS Access to MS SQL Server 2005 at ASP.NET Tricks on 12 Jan 2007 at 6:08 am

    [...] Today I found a great post about migrating from MS Access to MS SQL Server 2005. [...]

  2. By Gianluigi Sellitto » Migrare un db Access a SQL 2005 on 16 Jul 2008 at 7:05 am

    [...] Via CF_Payne. [...]

  3. By Access Chick » Blog Archive » Migrate Access to SQL Server 2005 on 23 Jul 2009 at 3:43 pm

    [...] SQL Server Migration Assistant for Access.  It was wonderful!  Many thanks to CF Payne for his blog post about this very topic and the wonderful walkthrough instructions.  The database was migrated in [...]

Post a Comment