• Home
  • About
  • Blog
  • Speaking
  • Contact

The inevitable: Migrating ACCESS 2007 to SQL Server 2014

18 March 2015SQL StijnGeen categorieNo Comments

There comes a time in every SQL Server dba’s life when he will have to consolidate a MS Access database to a SQL server, it is inevitable and destiny decided that it was my time today.
An overly used Access database which had almost grown beyond its capacity was the one who decided my fate today, it had about 1.2GB of data and became extremely slow to query, so this was something we could no longer ignore. This was my first Access database which I was going to migrate, so I started off by doing some research on the good old internet to get some more information on how this could be achieved the easiest. Many people suggested making SSIS packages, other people suggested using openrowset or opendatasource. I choose to use the openrowset technique.

First of all I listed up all the Access tables and stored them in a table on my sql server, I will be using this table to loop through all my tables on the access database using openrowset.

Let’s get started.

I found following connection string for the 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 quite straightforward, I can’t use OLEDB for access if I haven’t installed it yet, so I installed the provider and reran the statement and got 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 SQL server on 64 bit this will not work with a 32 bit provider, so I need to uninstall it and reinstall the 64 bit driver. This can be found at

http://www.microsoft.com/en-us/download/details.aspx?id=13255

After installing this provider I got the statement to work

The next thing I am going to create now is a loop which will execute the openrowset query for all my tables, to use a parameter in the openrowset query we will have to put the whole statement in to a variable and execute this 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 that I can get all this table data I still have to insert it into a table, preferably with the same name & structure as in access. We can achieve this by doing 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 will still need to look at your column sizes and expand some f.e. description fields etc. You will also have to create primary and foreign keys like it was created in the access database to ensure data integrity, and create clustered & nonclustered indexes for performance.

 

This will then create all the tables in a loop, with the right amount of columns and data types & directly insert the data into them. So after the execution of this statement, The ACCESS to SQL Server migration was a success.

This is the way I tackled the issue of migrating an access database to sql server, there are other ways to do this for different situations, you will have to decide which one is best for you when you inevitable ACCESS migration comes along.

Thanks for reading and stay tuned!

 

 

 

Tags: Access database, SQL SERVER

Related Articles

Openrowset across the CMS: The Proxy Way of Life

20 March 2015SQL Stijn

The SQL Server Misconfiguration Chronicles: Part 1 Database AutoClose & AutoShrink

8 December 2015SQL Stijn

THE SQL SERVER MISCONFIGURATION CHRONICLES: PART 2 Instance Memory Settings

17 December 2015SQL Stijn

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Recent Posts

  • Building a Modern Lakehouse Data Warehouse with Azure Synapse Analytics: Moving your Database to the lake
  • Azure Synapse Dedicated SQL Pools: Workload Management
  • Synapse Spark – Playing with Data Frames with Python
  • Synapse Spark – Reading CSV files from Azure Data Lake Storage Gen 2 with Synapse Spark using Python
  • Azure Synapse Analytics – Serverless SQL Pools: Data Elimination

Recent Comments

    Archives

    • June 2022
    • December 2021
    • May 2021
    • April 2021
    • March 2021
    • February 2021
    • January 2021
    • December 2020
    • August 2016
    • July 2016
    • March 2016
    • February 2016
    • December 2015
    • October 2015
    • August 2015
    • March 2015
    • December 2014
    • November 2014
    • July 2014
    • February 2014
    • January 2014
    • December 2013

    Categories

    • Azure Synapse Analytics
    • Building a Modern Lakehouse Data Warehouse
    • Geen categorie
    • MS PDW
    • SQL 2016
    • SQL Maintenance
    • SQL Server Migration
    • SQL Server Misconfiguration Chronicles
    • SQL2014
    • Troubleshooting

    Contact Stijn now


    © SQL Stijn 2017 - Privacy Policy - Cookie Policy
    Manage Cookie Consent
    To provide the best experiences, we use technologies like cookies to store and/or access device information.
    Functional Always active
    The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
    Preferences
    The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
    Statistics
    The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
    Marketing
    The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
    Manage options Manage services Manage vendors Read more about these purposes
    View preferences
    {title} {title} {title}