Database Administration from access to sql

Published on April 7th, 2015 | by Alejandro Cordero

2

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.


About the Author

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



2 Responses to Migrating from Access to SQL Server

  1. nak1973@gmail.com' Niaz says:

    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 says:

      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

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