MS Access: upsizing to SQL Server 2008
Tuesday, March 17, 2009
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 2007 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.
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.
SSMA 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
- SQL Server Migration Assistant for Access main page.
- SQL Server Migration Assistant download. Note that you will have to register to get a license file that you need to save on your PC before you can use this free tool.
- “Access 2007 Upsizing to SQL Server 2008 Express in SQL Upsizing” discussion on Microsoft groups.
- “KB838594: Error message when you try to upsize your Access database by using the Upsizing Wizard” is not the problem here, but some may find the reference useful if you’re trying to upsize your MS Access database to a SQL Server database on a network.
- My random thoughts on SQL Server Upsizing from Microsoft Access from Tony’s Access MVP website, and his list of links to other resources.
Updates
- 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
10 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
Leave a Comment
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