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 
, ( 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.
Hmm, at first it got me confused … because I had SQL Server, OS and SSMA all 64bit, and it was telling me that I have issue with connectivity component that is 32bit !! But then suddenly I realized that I have MS OFFICE 2010 installed in workstation, which is 32bit … duh, I just imagined that if you have 64bit OS, when you install a software and if that software has 64bit package included then it will automatically install 64bit application … but nooooo, OFFICE installs 32bit by default, if you want to install 64bit version then you have to go to CD drive and have to specifically install 64bit version !! (now, why in the name of all goodness I have to manually select that ??) …
 |
 |
 |
So, now I had to do exercise to remove all office application (which was Office 2010 + Visio 2010), and may be have to go through another reboot (not new for MSFT products) … so rather then going that way, I directly installed 64bit runtime in our test server and then ran the whole process again … and as I had assumed … it went super well.
I guess I learned that next time when tech ask me to install any software in my workstation, I will tell her to install only 64bit s/ws … 
… but not sure how many 64bits apps are there…
That’s it for now …
It’s Just a Thought … 
