Migrating from Access to SQL Server

I ran across a request where I needed to upgrade an access database into SQL Server, seems the original application was created using VB and access and it grew so much that the client needed to migrate to C# and change to a more robust database engine.

It is a fairly simple request but I realized, when was the last time I actually did this? ermmm, like a zillion years ago, so I had to start by checking some online info and get the work started.

I figured this could be a good topic for a blog post as I am sure I am not the first person doing this migration, so maybe others could benefit from a step by step guide on how to move from access to sql.

So….this is my story:

A little background…..My access is in spanish, however the steps should be the same, also I tried a migration from a 2007 access database file to SQL Server 2014 to make it interesting.

1 – Access Upsizing Wizard

Access comes with a “Upsizing Wizard” which will allow you to migrate to SQL Server very easily. You will find it under database tools menu.

 

 

Once you click that icon it will show you the wizard, you can choose to either import into an existing database or create a new one

 

I tried creating a new one for this example. Next Screen will ask for the connection information, so the wizard will connect to a SQL Server instance and start doing the magic. This spanish message means that the upsizing wizard is not working with access 2007 into SQL Server 2014, seems the driver or the binary itself is not able to connect to a new product of SQL Server.

 

Lesson learned here: if you are migrating from access to sql , the upsizing wizard might not work depending on your office and SQL Server version.

 

2- Let´s just import the database into SQL with the SQL Server import wizard, this should work right?

 

I started by creating a new empty database so I have a place where to run the import wizard:

 

 

3- The wizard can be found by right click on the database, tasks and then Import data…

 

 

4- Start by picking in your data source the correct type, in this case I am using “Microsoft Access Database Engine” (depending on the case you could use Jet Engine)

 

 

5- Pick a destination, in this case I picked my local SQL Server, provided connection info and clicked on Next

 

 

6- In here you can choose how to import the data from access to sql, using the entire data or specifying a query.

 

7- Next Pick the tables you want to migrate from access to SQL Server, in this case I picked them all

 

8- Next screen will allow you to pick data types , this is the most important step, as in here you will define string lengths and data types, failing to size values properly might result in values being truncated.

 

 

9- After this Go Next Next and run

 

 

10- And finally check your data, as you can see my blank database, is now full of tables:

 

 

Conclusion

 

If you are a SQL Server DBA and need to migrate from access to sql, then you will probably feel more “at home” by skipping the access upsizing wizard and just using plain old data import tool from SQL Server, this will present you with a more familiar environment and also allow you to easily define destination data types and lengths.



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

2 Comments

  • Avatar
    Niaz

    hi, thanks, I did the same, but all the primary, composite keys, index are lost during import and odbc process to bring it back to access. I tried to do it manually, but could not edit it. Not very familiar with SQL 2008.
    now trying to mmsa, this would not login to sql server.

    Any help pls will be highly appreciated.

    thanks, Niaz

    • Alejandro Cordero
      Alejandro Cordero

      Hi Niaz, not sure I understand the question, so you migrated to SQL Server and now you want to go back to Access? SQL Server is a complex engine, some objects created while on SQL Server would not exists in Access. If you have a newer version of access and SQL Server might want to try using the “Migration wizard for Access also called SQL Server Migration Assistant for Access”, with this wizard you will be able to specify which object you want to migrate and also be able to keep indexes and keys.

      Some steps are covered on this post

      http://sqlturbo.com/from-access-to-sql-with-microsoft-sql-server-migration-assistant-for-access/

      Hope this helps

      Cheers!

Leave a Reply