Microsoft SQL Server DevelopersErrors Importing Data from a Microsoft Access ACCDB Database Format into SQL Server

Provided by Aparna Pophale, Quality Assurance Specialist

Problem

While attempting to import a Microsoft Access ACCDB format database (created by Access 2007 or later) 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)

or

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.

Solution

Importing Microsoft 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 Import and Export Wizard

Microsoft SQL Server Management Studio can now import your ACCDB databases.


Additional Resources

Microsoft Resources

Strategic Overview

SQL Server Express

SQL Azure Cloud

What it Means to Information Workers

Link Microsoft Access to SQL Azure Databases

Deploy MS Access DBs linked to SQL Azure

Eliminate SQL Azure Security Holes

Monitor SQL Server Usage and DTU Limits on Azure

Convet Azure SQL Server to Elastic Pools

SQL Server Tips

Database Users and Permissions

Database Backup and Restore on Network

AM/PM Date Time Format

Get Date Portion

Set NOCOUNT ON

Running Total

Videos

Migrate Your Data Tier to SQL Server: Strategies for Survival

Microsoft Access Database and Migration Challenges

Are we there yet? Successfully navigating the bumpy road from Access to SQL Server

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Services

Microsoft SQL Server Consulting Services

Visual Studio .NET programmers for web development

General Microsoft Access consulting services

Additional Resources

Microsoft Access Help

Microsoft Access Developer Help Center

MS Access Developer Programming

More Access and SQL Tips

Technical Papers

Microsoft Access Tools

Connect with Us

 

Free Product Catalog from FMS