Recently I was involved in a migration project. Client was/is using ancient application built on Access 2.0 (yup … its 2.0) … I think it was started as a small app for just couple of users and with time it grew larger and soon it became mission critical application which still is built on old technology. Now here I am not gonna discuss about advantages of SQL Server over Access … because fact is both have their place in Data Processing Systems. In my native language we have a nice saying which loosely means that “sometimes we can’t do something by knife, that has to be done by needle” … having little bit interest in element of life and Zen … I consider myself self-proclaimed philosopher smile_wink, ( if you have problem with that then … I really don’t care ) … oki back to discussion … what I meant to say is, in a situation where you need very small application that will be used by 1-2 or very few people, with very little data to process (v2.0,95,97 everyone had 1GB physical size limit), have very little or no budget, easy to maintain,deploy and design solution then I guess access is pretty good fit. But if there are high number of concurrent users (user > 5-10), high data storage capacity, requirement of better ( wayyy better ) security then SQL Server is ideal solution.

With that said, let me explain the situation that let me to write this blog post. I had already installed, patched and configured SQL Server properly on our database server, I had also prepared the workstation too, which was able to connect to database server. Now, I thought to try for test run of a sample migration. It was an access 97 file (which was originally access 2.0, converted to access 97 to be compatible with SQL Server import.) with just 2 database and had no other stuffs (or say views). I first opened that access file in my workstation to be sure that it is working (I mean not corrupted), after that I fired up SQL Server Migration Assistance (SSMA, is a great free utility by Microsoft which allows users to migrate access database to SQL Server, which can also be done by making package in SSIS).

And I was greeted with this message in SSMA console …

Starting conversion...

Analyzing metadata...

Access Object Collector error: Database

     Retrieving the COM class factory for component with CLSID {CD77B9-4FD-42C5-AE42-8D281F0419} 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. Please run SSMA in 32-bit (or 64-bit) mode or consider updating your connectivity components from http://go.microsoft.com/fwlink/?LinkId=197502.
     An error occurred while loading database information.

Conversion finished with 0 errors, 0 warnings, and 0 informational messages.

There is nothing to process.

More >