Migrating Access 2007 to SQL Server 2014

There comes a time in every SQL Server dba’s life when they will have to consolidate an MS Access database to an SQL Server. It’s inevitable and destiny decided that it was my turn today. A heavily used Access database which had almost grown beyond its capacity sealed my fate. It had about 1.2 GB of data and had become extremely slow at executing queries, which was something we could no longer ignore. This was the first Access database I was going to migrate, so I started by doing some research on the good old internet. Many people suggest that making SSIS packages is the easiest way to do this, while others recommend using openrowset or opendatasource. I chose the openrowset technique.

First of all I listed all the Access tables and stored them in a table on my SQL Server, so that I could use this table to loop through all my tables on the Access database using openrowset.

Let’s get started.

I found the following connection string for openrowset online and tried it but instantly got my first error:

SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘MYPATH\Access.mdb’;’Username’;’Password’;’SELECT * FROM Tablename’);
Msg 7403, Level 16, State 1, Line 1 The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered.

This one is fairly straightforward – I can’t use OLEDB for Access because I haven’t installed it yet. So I installed the provider and reran the statement and this time I got the following error:

Msg 7438, Level 16, State 1, Line 1 The 32-bit OLE DB provider “Microsoft.ACE.OLEDB.12.0″ cannot be loaded in-process on a 64-bit SQL Server.

Oops, made another mistake. I’m using a 64-bit SQL Server but this doesn’t work with a 32-bit provider. So then I had to uninstall it and reinstall the 64-bit driver. You can find this here.

After installing the provider I got the statement to work.

Next I created a loop to execute the openrowset query for all my tables. To use a parameter in the openrowset query I had to put the whole statement into a variable and execute it with exec:

declare @table varchar(400)
DECLARE TableList CURSOR FOR
select name from dbo.tables
DECLARE @CMD varchar(4000)
OPEN TableList
FETCH NEXT FROM TableList
INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CMD =’ select * from OPENROWSET(”Microsoft.ACE.OLEDB.12.0”,”C:\Atoum\databases\atoum_data_fixed.mdb”;”someuser”;”somepassword”,”select * from ‘+@table+”’)’
exec (@CMD)
FETCH NEXT FROM TableList
INTO @table
END
CLOSE TableList;
DEALLOCATE TableList;

This worked perfectly.

Now I could get all this table data but I still had to insert it into a table, preferably with the same name & structure as in Access. I did this by using select * into from:

declare @table varchar(400)
DECLARE TableList CURSOR FOR
select name from dbo.tables
DECLARE @CMD varchar(4000)
OPEN TableList
FETCH NEXT FROM TableList
INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CMD = ‘ select * into ‘+@table+’ from OPENROWSET(”Microsoft.ACE.OLEDB.12.0”,”C:\Atoum\atoum_data_fixed.mdb”;”someuser”;”somepassword”;”select * from ‘+@table+”’)’
exec (@CMD)
FETCH NEXT FROM TableList
INTO @table
END
CLOSE TableList;
DEALLOCATE TableList;

Note: After this step you still need to look at your column sizes and expand some of them, e.g. description fields. You also have to create primary and foreign keys as they were created in the Access database to ensure data integrity, and create clustered & nonclustered indexes for performance.

This created all the tables in a loop, with the right number of columns & data types, and inserted the data directly into them. So after executing this statement, the Access to SQL Server migration was a success. That is how I tackled the issue of migrating an Access database to SQL Server. There are different ways of doing this for different situations and you will have to decide what is best for you… when your inevitable Access migration comes along.