Preface
Prerequisites
· .Net Framework Version 2.0
· SQL Server 2005 (Express, Standard, Enterprise) installed
· MS Access (2000,2002,2003) file to be migrated
· MS Visual J# Version 2.0
· SQL Server Migration Assistant (SMAS)
Download Links
· .Net Framework Version 2.0
· SQL Server 2005
· MS Visual J# Version 2.0
· SSMA for Access
Assumptions
· .Net Framework Version 2.0 is installed
· SQL Server 2005 is installed and remote connections are enabled
How-To
Instructions
1. Log into your SQL Server 2005 data source
2. Right click Databases and click “New Database…”
3. In the “Database Name” field, enter the name of the database that you would like to create and press “OK” when done
4. Download MS Visual J# Version 2.0 from the download link above
5. Install MS Visual J# Version 2.0
6. Download SQL Server Management Services (SMAS) from the download link above
7. Install SSMA
8. Start the SSMA application
9. Create a new project by clicking File > New Project … or click the New Project icon 
10. Give the project a name and location of where to save the project file like Figure 1
Figure 1
11. To preserve primary key numbering
a. Click Tools > Project Settings
b. Click the “Migration” link
c. Change “Keep Identity” to true see Figure 2
Figure 2 - You can change other settings here to match your desired database environment
12. Press “Apply” and “OK”
13. Add your MS Access file to the SSMA project by clicking the “Add Files” graphic 
in the upper left of your SMAS screen
in the upper left of your SMAS screen14. Under the Access Metadata Explorer, you can now expand the tree to see all of the objects related to this Access file and then click the name of database to make sure all of your objects are highlighted
Figure 3
15. Add your SQL Server 2005 connection to the SSMA project by click the “Connect to SQL Server” graphic in the upper left
16. Enter your database server, database name, authentication, user name and password to connect to your SQL Server 2005 instance in Figure 4
Figure 4
17. You will see in the SQL Server Metadata Explorer, the list of databases available to your SQL Server. Click the name of the database you will be importing the MS Access data into
Figure 5
18. From your Access Metadata Explorer, click the name of your Access database and then click the “Convert Schema” graphic
19. Along the bottom, you will see the output panel start outputting the conversions of the schema in Figure 6
Figure 6
20. From your SQL Server Metadata explorer, expand “dbo” tree and then expand the “Tables” tree and you will see that the converted schema was loaded into the database in Figure 7
Figure 7
21. Now right-click the “Tables” folder that is above the tblTest table icon and from the options window you will see an option that says “Load to Database” or click the graphic. This will create and modify all of the tables that you have loaded into the schema and place them into the database as table objects.
22. From your Access Metadata explorer, left click the “Tables” folder under your Access file tree see Figure 3
23. You will see the “Migrate Data” graphic become enabled
24. Click “Migrate Data” and the data from your Access database will be exported into SQL Server 2005
25. A report of the completed tasks will pop up, press close and your migration is complete
