Importing Data from a Microsoft Access 2007/2010/2013 Format (*.ACCDB) Database
into SQL Server
Provided by Aparna Pophale, Quality Assurance Specialist
While attempting to import a Microsoft Access 2007 or later format database (*.ACCDB) from the Microsoft SQL Server Management Studio, you receive an error such as:
The operation could not be completed. Unrecognized Database Format <ACCDB file path>. (Microsoft JET Database Engine)
Test connection failed because of an error initializing provider. Could not find installable ISAM.
These errors occur if you do not have the appropriate driver installed to import data from ACCDB files. Fortunately, it's easily solved.
Access MDB databases into SQL Server is a built in feature of SQL
Server. However, because of the difference between the
database engine of the ACCDB file format introduced with Microsoft Access 2007, it is not possible to connect to the Access 2007
database using the built-in data source "Microsoft Access". You can use
this if you wish to import data from a MDB format, but not an ACCDB from
MS Access 2007 or later.
Before importing data from a Microsoft Access ACCDB database, you must install the OLEDB Provider for Microsoft Office 12.0 Access Database Engine. Microsoft offers free download of
Microsoft Office System 2007 driver on their website.
After installing the 2007 Office System driver, you
will see another Data Source option, "Microsoft Office 12.0 Access
Database Engine", when importing data into SQL Server. Use that and your
data is imported as expected.
Microsoft SQL Server Management Studio can now import your ACCDB databases.