MS Access: upsizing to SQL Server 2008

Tuesday, March 17, 2009

Microsoft Access I’m currently researching ways to move my main MS Access application from a simple local network client/backend setup to a global, multiple remote sites configuration using SQL Server.

One of the challenges is to upsize the current MS Access accdb backend database to SQL Server 2008. If you try it from Access itself using the Upsizing Wizard, you may end up getting this error message:

The Upsizing Wizard only works with Microsoft SQL Server (Versions 6.50 SP5 or higher). Please log in to a SQL Server data source.

The Upsizing Wizard only works with Microsoft SQL Server (Versions 6.50 SP5 or higher). Please log in to a SQL Server data source.

After spending some time fiddling around with SQL Server settings I couldn’t understand why I was still getting this error.
Turns out that the upsizing wizard is apparently sensitive to the version of SQL Server you’re using and it doesn’t consider SQL Server v10 (2008) as being later than v6.50…

This issue is in fact a blessing.
Microsoft provides a migration tool for upsizing MS Access database to SQL Server 2008 that’s orders of magnitude better than anything the basic wizard can do: the SQL Migration Assistant for Access, or SSMA.

Migrate from Access to SQL ServerSSMA lets you take a bunch of Access databases and move the tables and queries you choose to SQL Server, automatically linking them in your original database if you want.
It’s not just a one-off thing either: SSMA keeps track of the objects that where transferred and allows you to synchronise both schema and data as often as you need.

So here you are: do not use the basic MS Access Upsizing Wizard, download and use SSMA instead.

Strange COM Error

While SSMA works perfectly fine on my Windows 2008 x64 laptop, on my main Windows XP desktop it throws an exception when trying to load an Access database:

Unable to cast COM object of type ‘Microsoft.Office.Interop.Access.Dao.DBEngineClass’ to interface type ‘Microsoft.Office.Interop.Access.Dao._DBEngine’
… {00000021-0000-0010-8000-00AA006D2EA4}…

It was a COM error saying that the library for DAO couldn’t be loaded.

I couldn’t find any relevant information on the web.
After a while, I had a look at the DAO driver in
C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
and I noticed that the filename was in blue: that reminded me that I had set compression on the filesystem.

I disabled compression for the file and, magically, SSMA worked again…

Moral of the story: be careful about compressing your filesystem, some registered libraries and system files may work in unpredictable ways…

References

Updates

  • 23FEB2012: Added link to SSMA Team blog and updated download link to current version (5.2)
  • 17MAR2009: Added section on the strange COM error.
  • 14MAR2009: Added links to Tony’s Access MVP website.
  • 05JAN2009: Original publication.

Entry Filed under  :  Database,MSAccess,Programming

29 Comments Add your own

  • 1. Karla  |  February 10th, 2009 at 11:06 am

    Bless you. This saved me alot of time and hair.

  • 2. Renaud  |  February 10th, 2009 at 3:03 pm

    Thanks Karla, you’re welcome.

  • 3. Shane  |  March 14th, 2009 at 5:43 am

    Thanks for the info. I have used SSMA to migrate Access97 tables to sql server2005 and link them. After I have migrated the access97 front end containing forms has become much slower in executing queries. Can you suggest some tips to make it faster..

    Any help is appreciated..

  • 4. Renaud  |  March 14th, 2009 at 11:09 am

    @Shane: it’s one of the drawbacks of migrating to SQL Server: you need to optimise some of your queries as they will be slower.

    The problem is that, if you use Access functions or some Access-only construct in your queries, Access will have to pull all the data from the tables to perform the joins and special treatment.
    Before, it would have known how to optimise for efficiency but once the tables have moved away, it can’t optimise some of the queries any longer.

    I’ve added some links on upsizing.

  • 5. Shane  |  March 24th, 2009 at 3:13 am

    Thanks Renaud. I have some 100 queries that I work with and even if I optimise(don’t really know how to) you say that some of the queries can no longer be optimised. So, can you suggest any other methods to make it faster..

    Thanks again for ur help

  • 6. Paolo Cavaliere  |  September 25th, 2009 at 4:57 am

    Well Done!!Thanks!!

  • 7. NTC  |  October 21st, 2009 at 6:47 am

    So what you went thru is more than I wanted to do. Really do not want to get into sql server management…and instead used the replication service of AccessTables.com It isn’t a fit for every application but it saved the day for my requirement. Just fyi to anyone in the same boat….

  • 8. Tech, Web, How to, Intern&hellip  |  November 9th, 2009 at 10:22 am

    Ms Access Database Project…

    It is a long long time since my last Ms Access project. I never use Ms Access in my work. But this time have to develop a simple database system for training assignments, a small project for myself. The previous trainer use manual way to assign trainin…

  • 9. craig  |  December 10th, 2009 at 3:36 pm

    Thanks it worked. Much prefer the old upsizing wizard from within Access…

  • 10. Michel  |  February 5th, 2010 at 6:36 am

    Thank you. I had a problem making this conversion, and you greatly helped me.

    Unfortunately, there is nothing that Microsoft is direct with. Instead of upsizing Access itself to suit more for a client/server architecture, Microsoft is shily telling us to leave Access and choose Sql Server. I think it is a waste of time investing in most Microsoft software, except the other basic Office software (Word, Excel, Outlook), Sql Server, and C#.

    The link to the SSMA can be got directly from here.

    SQL Server Migration Assistant 2008 for Access V4.0:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=133b59c2-c89c-4641-bebb-6d04476ec1ba&DisplayLang=en

    Thanks

  • 11. Chris McGue  |  March 26th, 2010 at 6:04 am

    My error is SID owner not the same on master database…

  • 12. Britt  |  April 28th, 2010 at 8:15 pm

    What about the new attachments type with Access 07? Does know of any resources on using an Access front end with the SQL Server backend with attachments.

  • 13. Chris McGue  |  April 28th, 2010 at 8:25 pm

    Does anyone know how to solve this issue of the SID owner not being the same on the master database?

    This is massive waste of time. I’ve had to revert back to MS-Excel and use the primitive method of addind UNION ALLs in my query pane. Microsoft should have allowed the upsizing directly from MS-Access as it was in the previous version of SQL. I can’t believe no one can answer this. Shouldn’t the new product SQL 2008 be better than the older version!!!

  • 14. mb  |  July 8th, 2010 at 3:53 am

    Really helpful

  • 15. George  |  July 21st, 2010 at 5:31 am

    I am having a COM problem as well, though my file is not compressed. I keep receiving this error:

    Access Object Collector error: Database Unable to cast COM object of type ‘Microsoft.Office.Interop.Access.Dao.DBEngineClass’ to interface type ‘Microsoft.Office.Interop.Access.Dao._DBEngine’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{00000021-0000-0010-8000-00AA006D2EA4}’ failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). An error occurred while loading database content.

    Any ideas on what might be going on would be greatly appreciated!

  • 16. Matthew Atherton  |  December 18th, 2010 at 1:14 am

    I had the same issue, and followed the steps in the article below (registered the DAO360.DLL and also added to the PATH Environment Variable).

    The error went away after that. Good luck!

    http://sqlserver-qa.net/blogs/tools/archive/2010/01/12/unable-to-cast-com-object-of-type-microsoft-office-interop-access-dao-dbengineclass-to-interface-type-microsoft-office-interop-access-dao-dbengine.aspx

  • 17. Ian  |  January 20th, 2011 at 12:05 am

    Hi, I think I’ve done just about everything to try and rsolve this issue, but when using SSMA to link an Access 2007 db to sqlserver 2008expressr2 db, when connecting there is no available sql server intances showing in the drop down. I have enabled tcp/ip, restarted the service. The sql instance is on my machine and so is the access db. Any ideas? thanks Ian

  • 18. Santhosh  |  January 26th, 2011 at 4:31 pm

    SSMA worked ut I had a different issue when running Windows 7, I had to use change the properties to XP compatibility mode or the tables wont be listed to migrate when I open the Access DB.

  • 19. Simon  |  February 24th, 2011 at 10:41 am

    Thanks for the tip about SSMA I was going to use the upsizing wizard but I’ll try that instead.

  • 20. GIL  |  May 24th, 2011 at 8:46 pm

    Thanks for all the info, very very useful!!

  • 21. Jan Waters  |  June 29th, 2011 at 1:33 am

    I registered the DAO360.dll added it to my path, but early in the migration sequence I get this error.

    Access Object Collector error: Database Could not load file or assembly ‘Microsoft.Office.Interop.Access.Dao, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’ or one of its dependencies. The system cannot find the file specified. Error occurred while loading children.

    I am new to this tool any help would be appreciated My email was wrong on the prior post

  • 22. ofis dersi  |  August 21st, 2011 at 10:45 pm

    Thank you for useful information. quite worked for me

  • 23. Adam Senour  |  November 11th, 2011 at 6:06 am

    To add something useful to this blog post and to Matthew Atherton’s followup reply (both of which got me 98% of the way to where I needed to go), two things:

    1) On 64-bit systems, the path to the DLL may be C:\Program Files (x86)\Common Files\Microsoft Shared\DAO\ . This is where I found mine.

    2) If you continue to see the error after applying the fixes Renaud and Matthew listed here, remove the Access database from your migration project and add it back in again. This did the trick for me.

  • 24. Adam Senour  |  November 11th, 2011 at 6:07 am

    By the way, thanks very much for this, Renaud and Matthew. I probably would have ripped my own hair out if not for you guys.

  • 25. Cameron Reid  |  January 25th, 2012 at 1:38 am

    ­Great post. Here’s how you can convert Microsoft Access to web in minutes http://www.caspio.com/extend/platform-extensions/ms-access-database-online/convert-ms-access-to-web.aspx

  • 26. Rx  |  March 22nd, 2012 at 4:40 am

    HOLD ON! I used the Microsoft SQL Server Migration Assistant for Access. Looked and feels good. But, all of the Access 2010 Date filds are messed up! Every date text box now has a text date in it and of course the Date Picker won’t function. None of the Access VBA date check codes (e.g. can’t enter an Updated Date that is before a Submitted Date) don’t work either.

    What is up with a tool that doesn’t do what it advertised? And how can it be fixed?

  • 27. Renaud Bompuis  |  March 22nd, 2012 at 8:57 am

    @Rx: by default SSMA will use the new DateTime2 in SQL Server. Best is to modify the default mapping options to regular DateTime instead. VBA functions should work fine with that.
    One other thing: while you should allow SSMA to add a TimeStamp column to each table, don’t try to use that directly within Access, it has nothing to do with date or a time, it’s just a special kind of counter that ensures that everytime a record is updated, a new value will be saved in the TimeStamp.
    This helps Access with Optimistic Concurrency, when checking if the record you’ve just edited can be safely saved back to the database or if someone else modified it before you.
    If you have specific VBA issues, let me know.

  • 28. Richard Batuwan  |  February 23rd, 2013 at 12:53 am

    Re. Migrating Access tables to SQL2008:- I have run into problems upsizing with SSMA, formatted Date fields. In Access: (2 seperate fields) 1. Date Field formatted as “mm\dd\yyyy” 2. Time Fields formatted as short Time “hh:mm:ss” When I use type mapping, Source Type “date” – Target Type “smalldatetime”; The upsizing fails on errors, invalid date “12\30\1899″: IF I use type mapping, Source Type “date” – Target Type “datetime2″; The upsizing works, but the values in the 2 target fields are as: Date: “2000-05-03 00:00:00.0000000″ Time: “1899-12-30 04:54:04.0000000″

    BUT, another table with only Date Field formatted (ShortDate), when upsized using type mapping “smalldatetime”, works perfectly, and Target file receives date as “mm\dd\yyyy”. Question is “WHAT CAN I DO TO RESOLVE THIS ISSUE, UPSIZING THE TIME FIELD AS FORMATTED AND TO RECEIVE IN TARGET TIME FIELD ONLY???” Thanks in advance.

  • 29. Jon Paulson  |  September 12th, 2013 at 8:35 am

    Great post. Our Access-based customers are constantly asking us for tips on this very migration. Now I can just direct them here.

Leave a Comment

(Will not be shown)
Notify me of follow-up comments via e-mail

Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Trackback this post  |  Subscribe to the comments via RSS Feed


about

Renaud This is a simple technical weblog where I dump thoughts and experiences from my computer-related world.
It is mostly focused on software development but I also have wider interests and dabble in architecture, business and system administration.
More About me…

My StackOverflow Profile
My (sporadically active) StackOVerflow account

Most Recent Posts

Categories

Links