Published on April 7th, 2015 | by Alejandro Cordero2
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:
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.