derek:b3studios

Access 2007 .Transfer bug, the fun…….

I just spent 20 hours of my life, debugging one hell of a problem in Access 2007. I thought I would share this with you, incase anyone else maybe tearing their hair out - and the problem is similar.

Here is the setup. Access 2007 Database - Upsized/Migrated to SQL Server (2008 in my case). Everything worked fine before, exporting a Text File, or a Spreadsheet of the data. Now when you click on the button - It crashes the application. The stranger part - it doesn’t happen EVERY TIME!?

No in fact this problem, doesn’t even happen if you use the OutPutTo function instead…….

So after, all the tinkering eliminating the much more obvious offenders, what was the little damn stinker?! A UnionQuery…. A pesky UnionQuery was bombing out the .Transfer. It worked fine when I ran it, Access didn’t complain about it, nor did SQL server.

What I found was a pesky little ORDER BY on one of the UNION members. This trashed the .Transfer for some reason. SO there you have it. Remove your ORDER BY in one of your UNION statements, and life should be grand again!