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.][4]
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][5].
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 Team Blog][8]
* [SQL Server Migration Assistant for Access][5] main page.
* [SQL Server Migration Assistant download][1]. 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”][2] discussion on Microsoft groups.
* [“KB838594: Error message when you try to upsize your Access database by using the Upsizing Wizard”][3] 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][6] from Tony’s Access MVP website, and his [list of links to other resources][7].
### 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.
[1]:http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=28763
[2]:http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.access.sqlupsizing&tid=a1198438-f914-4710-91ca-440da168dc5d&cat=&lang=&cr=&sloc=&p=1
[3]:http://support.microsoft.com/kb/838594
[5]:http://www.microsoft.com/sqlserver/en/us/product-info/migration-tool.aspx#Access
[4]: /wp-content/uploads/2009/01/UpsizingErrorsm.png
[6]:http://www.granite.ab.ca/access/sqlserverupsizing.htm
[7]:http://www.granite.ab.ca/access/sqlserverupsizinglinks.htm
[8]:http://blogs.msdn.com/b/ssma/
Comments
Bless you. This saved me alot of time and hair.
Thanks Karla, you’re welcome.
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..
@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.
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
Well Done!!Thanks!!
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….
Thanks it worked. Much prefer the old upsizing wizard from within Access…
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
My error is SID owner not the same on master database…
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.
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!!!
Really helpful
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!
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
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
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.
Thanks for the tip about SSMA I was going to use the upsizing wizard but I’ll try that instead.
Thanks for all the info, very very useful!!
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
Thank you for useful information. quite worked for me
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.
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.
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
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?
@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.
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.
Great post. Our Access-based customers are constantly asking us for tips on this very migration. Now I can just direct them here.
Comments are closed.