Migrating Access Database to SQL Server

I recently received a call from one of my friend asking me, what is the easiest way to migrate a database from ACCESS to SQL Server? I was curious on answering his question and just went for a search in internet to find it was much simple than what I expected. This article describes the steps to migrate a Access database to SQL Server.

 

To Migrate Access to SQL Server database, download the tool SQL Server Migration Assistant for Access

Install SQL Server Migration Assistant for Access on local computer

You need to have the access database closed while migrating to SQL Server.

Goto Start->;Programs->;Microsoft SQL Server Migration Assistant 2008 for Access->;Microsoft SQL Server Migration Assistant 2008 for Access from the computer where it’s installed. You will see the welcome screen.

migrate_access_to_sql_1

Click Next, Create a new project by entering the Name of the project and it’s location.

migrate_access_to_sql_2

Click Next, You will be taken to a screen to add the Access database. Click on Add Databases button to specify the folder where your access database resides.

migrate_access_to_sql_3

Click Next, Here you will have option to migrate only data and/or the queries too. Here, we are migrating both data and the queries.

migrate_access_to_sql_4

Click Next, You will see the SQL Server connection Screen. Here specify the SQL Server instance where you want to move your Access database along with the destination Database name and credentials.

migrate_access_to_sql_5

Click Next to Link tables. If you want to use your existing Access applications with SQL Server, you can link your original Access tables to the migrated SQL Server tables. Linking modifies your Access database so that your queries, forms, reports, and data access pages use the data in the SQL Server database instead of the data in your Access database.

migrate_access_to_sql_6

Click Next to start Migration.

migrate_access_to_sql_7

You will see the objects and queries to be migrated. Here If there are any errors or discrepancies will also notified.

migrate_access_to_sql_8

Click Ok to complete migration.

migrate_access_to_sql_9

Now, you can open your SQL Server database to see that the Access tables and Queries are migrated. The Queries will be migrated as Views and you can use them as you did in the Access tables. The migration task is simple than expected.

Leave a Reply

Your email address will not be published. Required fields are marked *

*