kohera-logo-regular.svg

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.

150910b

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.

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature

If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before. Up until now, you had...

Creating maps with R and Power BI

The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you have creativity and knowledge about the right programming language, you can let...

Sending monitoring alerts through Telegram

What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the app Telegram. Some of you...

Send mails with Azure Elastic Database Jobs

The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want a similar functionality in Azure SQL Database? There are options,...

Sorting matrices in Power BI

Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour view data into a report...

The world of data is evolving

The data landscape has changed dramatically over recent years. In the past, we mainly heard that we needed to do as much as possible “cloud-only”—but this trend has become more...