We all love to hate Microsoft don’t we? :-) If there is something that [tag]Microsoft[/tag] have got spot on is [tag]Microsoft SQL Server 2005[/tag]. In my opinion there is no better Database engine. So you can take Oracle and its ridiculous price tag and click uninstall, because MSSQL 2005 is here to stay.

Anyway, back to the topic of this post - [tag]Upgrading[/tag] [tag]Microsoft Access[/tag] to Microsoft SQL 2005. I had this challenge while on my adventure recently. We had to convert 4 Access DB’s to MSSQL 2005 because we needed the data in order to compare with other 2005 DBs. We tried using linked servers across network domains, but that can really be a pain because of user rights, and really, no commercial application should be written in Access.

Tools needed:
Microsoft Access
[tag]Micrsoft Excel[/tag]
Microsoft SQL Server 2005 - any edition
2 Hours
1 Keyboard
1 Mouse
1 box of smarties. - just in case u get hungry.

There are two ways of tackling the upgrade. In Access using the Upsizing wizard should be the you first port of call.
Tools > Database Utilities > Upsizing wizard
Follow the prompts and all should be bright and breazy. Well maybe not quite. After the upsize has happened, Access creates a nifty report that tells you what was converted and if there were any errors. For those tables that Access couldn’t upsize, you can try re-upsizing until you are blue in the face, but it just won’t happen. For those “problem” tables you are going to have to take alternate action. Excel to the rescue!

In Access, right click on a “problem” table, and click Export…
Save the table as a Microsoft Excel file. Rinse and repeat for every “problem” table. (For those of you that might think that you will have millions of tables that might not upsize, out of our +-1000 Access tables we had less than 10 “problem” tables.)

Now that you have all the “problem” tables as Excel files, we are going to import them into [tag]MSSQL 2005[/tag] using Data Transformation Services. Right click on the Database in the object explorer of MSSQL 2005 that you are going to import the “problem” tables into and click Tasks > Import Data… and follow the prompts. That should solve any problems Access was having upsizing and exporting the different datatypes. Seems Access has problems with importing datetime fields, but going through Excel, everything seems to work just fine.

As a tip, you might want to drop the “problem” tables in MSSQL 2005 that Access created before importing from Excel as those tables would be dataless.

So there you have it. My first tutorial outside of the web. Want to check out my web tutorials? The Internet Series part 1 and part 2 are still there… :-)