Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 688
  • Last Modified:

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

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?
  • 2
1 Solution
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.

Chris LuttrellSenior Database ArchitectCommented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Chris LuttrellSenior Database ArchitectCommented:
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now