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.
Click Next, Create a new project by entering the Name of the project and it’s location.
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.
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.
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.
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.
Click Next to start Migration.
You will see the objects and queries to be migrated. Here If there are any errors or discrepancies will also notified.
Click Ok to complete migration.
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