Tutorial:
MSSQL-
Dr. Thomas E. Hicks
Import To MS-SQL-Server From 2007 Access Database
Computer Science Department
Trinity University
The purpose of this tutorial is to show how export a Microsoft 2007 Access Database into Microsoft SQL Server Express.
We are about to import a Microsoft Access 2007 Database into either Microsoft SQL Server Management Studio or Microsoft SQL Server Management Studio Express. Note that Microsoft SQL Server Management Studio Express is a free download for those who have Microsoft Visual Studio 2005.
Microsoft SQL Server Management Studio has an assortment of import tools included, but Microsoft SQL Server Management Studio Express does not. You can use this approach to transfer Access databases into either Microsoft SQL Server Management Studio or Microsoft SQL Server Management Studio Express
You can see within Microsoft SQL Server Management Studio Express, above, that the only database present is the System Databases. It is our intent to export a Microsoft Access database into the MSSQL Server of Net4.
1] I have opened a Microsoft Access Database, called Northwind.mdb. Mafke an extra copy of any database you plan to Export; this will give you a backup copy in case the export fails or is done incorrectly.
2] The database can not be exported to MSSQL Server until it is trusted. To enable the trust, Select the Options Button! (See Below!)
3] Select the Enable this trust radio button. Select the OK Button! (See Below!)
4] Using the mouse, select the Database Tools tab. (See Below)
5] Using the mouse Select SQL Server button in Move Data. (See Below)
6] Choose to create a new database. Using the mouse, select/push the Next Button. (See Below)
7] Select the SQL Server. Net4 is the name of my system. If you are using Microsoft Management Studio Express, you will have to add \SQLEXPRESS to your server thus making my NET4\SQLEXPRESS; If you have the complete Microsoft Management Studio, you simply use NET4. Name the database Using the mouse, select/push the Next Button. (See Below)
8]
Use the 
button to select to export all
of the tables in the Northwind database. I am selecting all of them.
Using the mouse, select/push >>.
(See Below)
9] You can customize your selections if you wish, but I often just use the mouse and select/push the Next button. (See Below)
10] You can actually link the two databases, but that is not our intent. Select No application changes. Using the mouse, select the Finish Button. (See Below)
11] You will see one or more progress bars during the export. (See Below)
12] A conversion report is generated. It will include errors, if any. Check it out. (See Below)
1] Open your Microsoft SQL Server Management Studio connection
2] Note that the Northwind database was created; I expanded the Customers table on the right. (See Above)
1] Load the Microsoft SQL Server Management Studio Express and delete any portion of the Northwind database before you begin.
2] On occasions when my students have had problems, I have sent them to Microsoft.com to download the newest Microsoft Data Access Component (MDAC) and any service packs that go with that component.
3] Make another copy of your backup database and use that to do the export!