Recently, three different clients' Access programs have randomly produced an error "Class does not support automation or does not support expected interface". There seems to be no commonality - one is an Access 2003 adp that has been running successfully since 2005, one is a program we began testing in August (Windows 7, Access 2007), and the third is Access 2003 loaded on 15 different laptops - some XP, some Win 7. The laptop program hasn't produced the error with the Win 7 computers but it doesn't produce the error on the all XP's either - or even consistently on any one of the XP computers. With the most recent incident (the adp), a report they use often produced the error. It does populate a table through code, which is where I thought the problem suddenly developed (even though no changes have been made to that code in several years). However, when I commented out the error handler (expecting to have it break on whatever was causing the error) - it ran without the error. With the 2007 program, my client and I used it with no problem two days ago, but yesterday when he tried to enter more data, the error came up when he opened the program.
I've Googled it but the answers seem to point to something involving MDAC - but I don't understand why it's so random, why commenting out the error handler caused it to NOT display the error, or what to do about it. I can't reproduce it "on demand" - I have seen it once or twice on my computer a couple of months ago (Win 7 and I think it was Access 2003 when I saw it) but compact/repair and compiling made it quit, so I didn't get alarmed at that point. That said, I'm not sure I can provide any sample that would reliably show the error.
At some point when this first started popping up (with the laptop program in September), I tried creating a new db and importing everything - but it still happens randomly.
Any ideas? (Fingers crossed!!)