Access Upsizing Wizard

After working on a fair share of .NET based applications I was faced with a Microsoft Access conversion. A client had an Access based application that had become almost unusable after having a half dozen developers and a consulting company dig through it. They were ready to port the data over to SQL Server and design a C# application for the future of the app.

Microsoft Access offers a handy Upsizing Wizard that allows you to simply copy your data over to SQL Server. Unfortunately if the data wasn't very well validated and won't convert over to the SQL Server types then tables with issues won't copy the data over. You'll get a message in Access reading "Tables are skipped or export failed" and unfortunately no other information. You'll also notice that the table was successfully created in SQL Server but with no data. What do you do now?

My solution came from reading for the MCTS test. I'd read about SqlBulkCopy class and had tinkered with this example of the class. I knew I could quickly script out something to test inserting records and get a meaningful exception. Withing a few minutes I got a "date out of range" exception. With a little more modification of the SQL to weed out the bad dates I was able to bulk copy over around 99% of the data and then manually correct the few bad records. No problem and it took less than an hour. I'm sure there are many other better solutions but this one worked well.

Jerry Wayne Odom Jr