Solved

Migrating Access 2000 to MS SQL Server 2012

Posted on 2014-04-14
10
945 Views
Last Modified: 2014-04-16
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?
0
Comment
Question by:lanassafoods
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 15

Expert Comment

by:colly92002
ID: 40001280
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.
0
 
LVL 58
ID: 40001368
<<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.
0
 

Author Comment

by:lanassafoods
ID: 40001490
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58
ID: 40001511
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.
0
 

Author Comment

by:lanassafoods
ID: 40001555
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?
0
 

Author Comment

by:lanassafoods
ID: 40001586
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"
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40001629
<<How do I go about changing this so that the table names stay exactly named as they are in access to minimize the impact? >>

Relink all the tables, then run this:

Private Sub RenameTables()

Dim tbl As dao.TableDef

For Each tbl In CurrentDb.TableDefs
  If Left$(tbl.Name, 4) = "dbo_" Then
    tbl.Name = Mid$(tbl.Name, 5)
  End If
Next

End Sub

 and FYI, you'll be far better off to do the up size with SSMA.   One thing you want to do, which exporting won't do for you is add a timestamp column to every table so row versioning in SQL is turned on.

Jim.
0
 

Author Closing Comment

by:lanassafoods
ID: 40001787
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.
0
 

Author Comment

by:lanassafoods
ID: 40002736
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
0
 
LVL 58
ID: 40003736
Glad to hear your squared aways.

Make sure you accept your last comment as the answer.

Jim.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question