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
