Access to MSSQL 2005?

[Dinghus]

Okay, I have a customer who sends over Access db. I upload it to his site and then run ColdFusion script to extract data from it and update a MSSQL 2005 db.

What I am wondering is -- is it possible to write t-sql to connect to the mdb and pull the data? I've never tried odbc with t-sql. Is it possible?

[Terry Van Horne]

You can setup the Replication service to do that. I have done it in the past to take data off a proprietary system and update MSSQL 2005. Could even schedule it!

[freezea]

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 part1 and part2 are still there…