How to import my Access 2007 DB into SQL Server 2008?

Posted on 2010-01-11
Medium Priority
Last Modified: 2013-11-30
The problem is that Access 2007 saves as .accdb and when I use the Import Data function in SQL server 2008, and select "Access"  as a datasource, its looking only for .mdb.  It doesn't recognize .accdb at all.  How do I get my Access 2007 into my SQL Server?
Question by:arthurh88
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 26289364
one alternative - instead of using the Import Data function in SQL server 2008, you could use the Move Data function in Access 2007.  
first, create an ODBC connection for the SQL server database.  Then in Access, go to the Database Tools menu, and select Move Data (SQL Server).  

another alternative would be to save the access 2007 database as Access 2002-2003 format, so it would be an mdb file.

LVL 27

Expert Comment

by:Chris Luttrell
ID: 26289574
From this link, http://msdn.microsoft.com/en-us/library/ms189667.aspx, to "Choose a Data Source (SQL Server Import and Export Wizard)" it refers to this link, http://msdn.microsoft.com/en-us/library/cc280478.aspx, for "How to: Connect to an Access Database" which tells you how to do it, the main line is below:
"On the Choose a Data Source page, for Data Source, select Microsoft Office 12.0 Access Database Engine OLE DB Provider, and then configure the connection as appropriate."

HTH, Chris
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 26289595
I'd suggest you use the SQL Server Migration Assistant: http://www.microsoft.com/Sqlserver/2005/en/us/migration.aspx

The link above is for SQL Server 2005; there's also one for 2008.
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26289607
P.S. to my comment above.  Unfortunately, it does not appear to have a browse feature to go find the file.  After picking the Microsoft Office 12.0 Access Database Engine OLE DB Provider, you have to click the properties link to put in the .accdb file name and password if any.  But the import worked perfectly from there.

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question