Solved

Migrating Access 2000 to MS SQL Server 2012

Posted on 2014-04-14
10
927 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
  • 5
  • 4
10 Comments
 
LVL 15

Expert Comment

by:colly92002
Comment Utility
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 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:lanassafoods
Comment Utility
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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
<<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
Comment Utility
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
Comment Utility
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 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Glad to hear your squared aways.

Make sure you accept your last comment as the answer.

Jim.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now