Tip: Importing Data from MS Access 2010 64-bit .accdb database into SQL Server using SSIS

Problem:

You can’t connect using Native OLEDB \Microsoft Jet 4.0 OLEDB Provider.  It fails with following error message:

Test connection failed because of an error in initializing provider. Unrecognized database format 'C:\Users\vishah\Documents\myDB.accdb'.

Solution:

• Download 2007 Office System Driver: Data Connectivity Components from here -http://www.microsoft.com/downloads/en/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en
• then choose Microsoft Office 12.0 Access Database Engine OLE DB Provider and point it to your .accdb file. 

source: http://blogs.technet.com/b/vipulshah/archive/2011/06/07/importing-data-from-ms-access-2010-64-bit-accdb-database-into-sql-server-using-ssis.aspx

Tip: Importing Microsoft Access 2007 Database Tables into SQL Server

Microsoft SQL Server 2005 and 2008 offer the Import and Export Wizard to move data to and from an external source. You can also create a basic SQL Server Integration Services (SSIS) package which can be used later in the Business Intelligence Development Project.

With the Import and Export wizard, you can access different types of data sources. These sources include database formats such as Microsoft Access, Microsoft SQL Server, flat files, Microsoft Excel, and Oracle. This article discusses importing Access 2007 database tables into MS SQL Server 2005/2008.

Importing Microsoft Access MDB databases (2003-format or earlier) is a built in feature of SQL Server. However, because of the difference between the database engine of Microsoft Access 2007 and earlier versions, it is not possible to connect to the Access 2007 database (*.ACCDB) using the built-in data source “Microsoft Access”. To import data from a Microsoft Access 2007 database, you must install the OLEDB Provider for Microsoft Office 12.0 Access Database Engine. Refer to the following FMS tip for details:

Errors Importing Data from a Microsoft Access 2007 Format (*.ACCDB) File into SQL Server

After installing this driver, open SQL Server Management Studio and connect to the desired instance of SQL Server database engine. In the Object Explorer, it shows database list available in that instance of SQL Server. Select a desired database or create a new one. Right Click this database and select Tasks -> Import Data.

Now follow the several pages of the wizard. The steps below detail how to import data into a SQL Server 2005 database, but the steps are very similar in SQL Server 2008.
On the first page, select the Data source from which you want to import the data. There are several data sources also available such as:

• Microsoft OLEDB provider for SQL Server
• Microsoft OLEDB provider for Oracle
• SQL Native Client
• Microsoft Access
• Microsoft Excel, etc…

Because of the difference between the database engine of Microsoft Access 2007 and earlier version of Microsoft Access, it is not possible to connect to the Access 2007 database using data source “Microsoft Access”. You can use this if you wish to import data from a MDB format, but not an ACCDB from Access 2007.
If you have properly installed the 2007 Office System driver, you will see another Data Source option: “Microsoft Office 12.0 Access Database Engine.”