Archive for March, 2009

MS Access: upsizing to SQL Server 2008

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.

30 comments March 17th, 2009

Technical ebooks: some publishers don’t get it

Stupid thingsI like buying technical books.
Unfortunately, here in Hong Kong, we have less choice: there are not that many technical bookstores that cater for English-language books and the selection is rather limited.

So whenever a book isn’t available here, I buy it online as a PDF.
It’s cheaper, saves postage and I can keep them handy on my desktop computer at work and my laptop.

I love Safari and being able to access such a large library of books online in such a flexible way is great, but if you’re not in the US, the experience is not always that pleasant, with the site sometimes becoming a bit too slow for comfort.

GraphitiTek by Charles Kalpakian

The publishers I regularly buy ebooks from are O’Reilly, when they make the PDF available, and Apress.

O’Reilly’s PDF may have your subscribtion details embedded at the bottom of each page.
It’s perfectly reasonable and doesn’t ruin the experience: I can still search, copy snippets to the clipboard and print the book if I need to keep an annotated copy.

Apress encrypt the PDF with your account email. Again, that’s fine by me, they don’t prevent me from using the book and it’s not a great annoyance to have to type the password to unlock the book.

Now comes Wrox (Wiley): they publish fine books and even have some available as ebooks.
The biggest issue I have though is that they assume that people who buy their books are all potential criminals:

  • The book is as expensive in paper as it is in ebook format. That can’t be right: ebooks have zero reproduction cost while paper books have huge material costs.
  • The ebook version needs to be registered with Adobe Digital Editions that locks it to a particular machine.
  • You’re only allowed to download the ebook to 4 different PCs and you’ve got 2 weeks to do so.
    This seems fair, but it’s not: if I change OS or PC, I’ve already burnt 2 licenses.
  • You can’t copy/paste more than a page at a time, up to 10 pages every week… that’s just a bit silly.
  • Can’t copy any of the artwork, diagrams, etc.
  • Doesn’t say anything about what happens if Adobe drops their particular DRM software or if I need to use the book on the next laptop I’ll buy a year from now.
  • Adobe Digital Edition only supports Windows and Mac and a Sony reader. So using Linux (even though Wrox plublishes books about it) or a Kindle or any other mobile device is out of the question.

So the net advantage of buying an eBook from Wrox (Wiley) is: your guess is as good as mine.

Yeah, you can buy each chapter as a PDF: great value, at US$4.99 per chapter, the book is costing you nearly US$100. You can get the fully printed version for half the cost…
Still, I’ll concede that being able to download a particular chapter can be useful.

The bottom line is: if your book gets printed, it’s going to be pirated and distributed for free in ebook format within a couple of weeks of being published.
While thinking they are protecting their copyright, Wiley is in fact punishing people who buy their books.

I’ll stick with Apress and O’Reilly I think. At least I don’t feel treated like a thief.


12FEB2010 Update: Wrox is now trying DRM-free PDF ebooks. We can only applaud this decision. While the offering is still limited at the moment, most or all of their books will eventually be available DRM-free.


Bookshelf by Charles Kalpakian

3 comments March 11th, 2009


Most Recent Posts

Categories

Links

Posts by Month