Importing Microsoft Access 2007 Database Tables
into SQL Server
Provided by
Aparna
Pophale, Quality Assurance Specialist
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.”
Select this option to import from an ACCDB file. Then click the
Properties button to open the Data Link Properties window:
On the Data Link Properties page, provide the location of database in
to the Data Source field, and enter the User name and password if
applicable. Click on the Test Connection and make sure it succeeds.
Click OK to close the Data Link Properties sheet, and continue to the
next page of the Wizard, to choose the Destination.
Your destination is SQL Native Client, since you want to import to
SQL Server. The server name and database should already contain the
database that you want to import to.
Click Next to continue, and choose option for Copy data from one or
more tables or views.
Click Next, and select the Source Tables or Views to import. If
necessary use the Edit Mapping button to map the Columns correctly.
The next screen of the
wizard shows two options. First one is to Execute Package Immediately
and second one is to save SSIS package which can be used in the Business
Intelligence Development Studio Project.
Click Next and Finish the Wizard. It shows all
successful action list and errors if any.
Once execution is finished, you can click on the database and see the
imported tables from Access database.
This way you can import data from Access 2007 database tables and
also create an SSIS package for a Business Intelligence Development
Project.
The limitation of the wizard is it
doesn’t share sources with other packages and more advanced logic can’t
be applied in the wizard.
Note About
Attachment and MultiValue Fields
Access 2007 ACCDB
files support Complex field types: Attachment fields and MultiValue
fields. When you import such fields, they're imported as Long Text
fields.
-
Values from a
MultiValue fields are imported to a single Long Text field,
with values separated by a semicolon (e.g.
Value1;Value2;Value3).
-
Values from an
Attachment field are stored similarly, except only the name
of the Attachment is stored, not the attachment itself (e.g.
Picture1;TextFile1)
Additional Resources
Return to the tips page
|
Email Newsletter
Developer Team Blog
Facebook
Twitter
All Our Microsoft Access Products
