asked on

Migrating Access 2000 to MS SQL Server 2012

I am currently looking at moving our ms access order system back end to an MS SQL 2012 Server. I thought I would play around with MS SQL and the Microsoft SQL Server Migration Assistant on my macbook pro running parallels 8 with Windows 8 Pro before attempting this on my server.

I have install MS SQL Server 2012 and the migration assistant without a hitch. However, I am getting the following error when attempting to migrate the access database to SQL Server using the migration assistant:

"Retrieving the COM class factory for component with CLSID {CD7791B9-43FD-42C5-AE42-8DD2811F0419} failed due to the following error: 80040154. This error may be a result of running SSMA as 64-bit application while having only 32-bit connectivity components installed or vice versa. You can run 32-bit SSMA application if you have 32-bit connectivity components or 64-bit SSMA application if you have 64-bit connectivity components, shortcut to both 32-bit and 64-bit SSMA can be found under the Programs menu. You can also consider updating your connectivity components from http://go.microsoft.com/fwlink/?LinkId=197502.
An error occurred while loading database content."

I have tried running both 32 bit and 64 bit versions of the migration assistant and I keep getting the same error. I notice when I initially add the access database to the migration assistant, I can't view the tables within the access database.

What could be the problem?

colly92002

It would appear you don't have the correct .dll available on you laptop, or at least a dll not compliled for the environment it expects. Can you open and run the Access DB on that laptop wihout any problems? Does it have any third party controls in it?
Does it have Office 2000 installed?

You might find it easier to first upgrade the Access DB to Access 2010 64 bit or above and then migrate that to SQL 2012.

Jim Dettman (EE MVE)

<<What could be the problem? >>

You don't have the required 32 bit components installed.

First, see if the DAO360.DLL exists on the system. Usual place is "C:\Program Files\Common Files\Microsoft Shared\DAO\Dao360.dll"

If not, you'll need to install a 32 bit version of Access in one form or another.

If it does, try and re-register it (no harm if it's already registered). From the run command, assuming it's in the usual place:

Regsvr32.exe "C:\Program Files\Common Files\Microsoft Shared\DAO\Dao360.dll"

Jim.

lanassafoods

ASKER

I have both access 2003 and access 2013 installed on the computer.
I have the Dao360.dll file, and have registered it as suggested.
However, I get the same error in migration assistant in either 32 or 64 bit versions.
I have been able to create an ODBC connection to SQL Server 2012 and exported the tables to the database with no hitch. At this point, I think that parallels could be the problem.
I will try to get my hands on a PC and run the setups again to see if I get different results

Jim Dettman (EE MVE)

Which edition of Access 2013 was installed, 32 or 64 bit?

and were 2003 and 2013 installed to different directories? or was 2013 installed over the top of 2003?

Jim.

lanassafoods

ASKER

I noticed that the tables all have the "dbo_" prefix. I have a lot of queries and forms in my existing access front end which would be a PITA to relink to all the tables with the dbo prefix.
How do I go about changing this so that the table names stay exactly named as they are in access to minimize the impact?

lanassafoods

ASKER

Access 2003 is installed here "C:\Program Files (x86)\Microsoft Office\OFFICE11" and access 2013 is installed here "C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft Office 2013"

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lanassafoods

ASKER

Thanks Jim, the plan right now is to get my hands on a PC and re-run the same scenario using the SSMA on the PC before carrying this out live on my server.

lanassafoods

ASKER

Just to add a comment...

I downloaded and installed the Microsoft Access 2013 32 bit run time file.
This allowed the migration assistant to work correctly.
Thanks everyone for your assistance

Jim Dettman (EE MVE)

Glad to hear your squared aways.

Make sure you accept your last comment as the answer.

Jim.