Database Administration from access to sql

Published on April 24th, 2015 | by Alejandro Cordero

0

From Access to SQL with Microsoft SQL Server Migration Assistant

In a Previous Post we showed how to move from Access to SQL Server  using the SQL Server Import Wizard. This is a very good option, however certain bits of our access project is left outstanding, such as the queries that are designed to return the data from the access tables.

When moving to SQL Server the concept of queries changes, and this means that more complex structures will be available for you. The more natural way to migrate a query is to convert it into a view. A view is a logical abstraction of a query, you will need to name this view and it will behave pretty much like another table, you then are able to build new queries on top of views as well.

You can use those views as reference from your VB code or you C# code, which leads me to a good point, when migrating from access to sql server you don´t necessarily need to migrate the application immediately to a web based architecture or another language like C#. What I mean here is that an Access application is normally conceptualized as a monolithic project, where you will have both the user interface and database structure in the same place, when moving to SQL Server you can actually break this and use your application on one side and your VB application in another layer. So you can probably now have the application in diverse machines and connect to the same database that is now a brand new SQL Server.

The architecture can now look like this:

 

 

So how do you accomplish this? this time it will be by using the Microsoft SQL Server Migration Assistant for Access. You can download it for free from this Microsoft site

Next a step by step on how to migrate a Northwind database.

Step #1 – Opening the Migration Wizard.

After installing the migration assistant you should double click on the icon and start up the wizard

 

Step#2 – Configuring your Migration Project

In the next screen you will be able to browse and specify the location of your migration project. In here you will be able to specify if you want to migrate to SQL Server 2005/2008/2012 or even to SQL Azure.

 

Step#3 – Choosing your Source Access project

In the next screen you will be able to browse and specify the location of your access project/file.

 

 

Step#4 – Pick which Tables and Queries you are going to Migrate

In the left pane of the tool, you will be able to browse your access database and it will be divided into 2 main folders, tables and queries. You can check the entire folder, which means you want to migrate the whole thing or just pick whatever you need.

 

Step#5 – Choose target SQL Server Instance

If you are looking to move to a SQL Server, naturally you will need to have one available and already installed. In the next screen you will specify the connection information to this SQL Server. In this case I am migrating to SQL Server 2014 and will specify user, type of authentication and password if using SQL Authentication. You have available 2 options for encrypting or using a server certificate as well in case you need those.

 

Step # 6 – Reusing your VB application

If you want to modify your access project so it will now point to the new SQL Server database, you can do so by checking “link tables”, this will allow you to keep using your application and still connect to a upgraded SQL Server. The wizard is actually pretty good doing this, however some manual tweaking could be needed at the end. Have this in mind and do test this first so you make sure it will actually work with your project.

 

 

Step # 7 – Start Migration

Give it a go, and see the assistant start doing most of the work for you :)

 

Step # 8 – Match VB references to the new SQL Server tables

 

In the next screen you will do the linking mentioned before. Normally you can just go “Next” in this screen, but do check in case you want to match an access table to specific SQL Server table.

 

 

Step # 9 – Check the final Report

Check the final Report and see if you had any issue. When moving from access to sql you will realize some data types changed. This means that depending of the value that you are using in a column, you will need to manually convert it, this is the manual part, so…..the wizard is not doing all the magic, just most of it. I will cover how to fix common issues in a future post. If you click the report button it will show you a detailed list of conversion errors, check below images for reference:

 

 

 

Step #10 – Work with your migration Project

After this step you will realized you now have a complete migration project. The migration assistant is not just a wizard, it will help you troubleshoot data conversions and have a general view of what can be migrated and what is presenting compatibility issues. The designer or main screen of the assistant looks like this:

 

As you can see the tables with issues are marked with a red icon, this means you need to do manual work on those. Stay tuned for the next article on how to fix those conversion issues.

 

Conclusion

 

The SQL Server Migration Assistant is probably the best tool to migrate your access project to a SQL Server database. It allows you to keep your original VB code and link references to the SQL Server tables. It will also allow you to migrate to diverse SQL Server versions or even to the cloud which opens a lot of possibilities for your migration plans.

 

Tags:


About the Author

SQL Server DBA with over 8 years of experience, developer for .Net , Certified Scrum Master and Entrepreneur.



Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Back to Top ↑
  • Sign Up For Our Newsletter

    Stay up to date on the latest from SQL Server products and Features.

    Sign Up Now

    Sign up for mailing list and receive new posts directly to your inbox.

    We promise never to share or sell any of your personal information.

  • Recent Posts

  • Categories

  • Archives

  • Tags

  • Topics

  • Subscribe to our RSS Feed


    sqlturbo.com A SQL Server Web Blog
    Canada, Ontario, Ottawa.
    http://sqlturbo.com

    The Authors