Datacap admin Database: migrate_access_to_sql_server_2005

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
  
  
  
  • http://msdn.microsoft.com/netframework/downloads/updates/#.NET%20Framework%202.0%20Beta%20SDK%20and%20Redistributable
  
  
  
  


• SQL Server 2005
  
  
  
  • http://www.microsoft.com/sql/downloads/default.mspx
  
  
  
  


• MS Visual J# Version 2.0
  
  
  
  • http://www.microsoft.com/downloads/details.aspx?familyid=F72C74B3-ED0E-4AF8-AE63-2F0E42501BE1&displaylang=en
  
  
  
  


• SSMA for Access
  
  
  
  • http://www.microsoft.com/downloads/details.aspx?familyid=D842F8B4-C914-4AC7-B2F3-D25FFF4E24FB&displaylang=en
  
  
  
  

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
    
    
    
    1. Click Tools > Project Settings
    
    
    2. Click the “Migration” link
    
    
    3. 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


14. 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