I work with a business partner in developing Access application. We have worked together for over 15 years and always operated in the same manner, which is exchanging MDB's back and forth between machines to make enhancements or changes.
Usual workflow: I work on the MDB, complete my changes send it to him. He makes his changes and sends it back to me. This has worked seamlessly, without issue, for a very long time thru several different versions of Access.
Recently I purchased a new Win 7 Ultimate system. Now there are issues every time I send him an MDB. I don't have issues with the revised MDB's that he sends to me.
If I revert back to developing on my old WIn XP machine there is no issue. However on the new machine iIt happens every time and we have seen a pattern with the error and solution but have no idea how to resolve the problem permanently.
I have also had the same issue sending MDB's to client running Win XP/Access 2003. I serviced these clients on my old XP machine for years without issue.
The error always occurs on a "Set rs = New ADODB.Recordset" statement. The message is something along the lines of "does not support automation".
The foolproof way (so far) of resolving the issue is for him to go into the "Tools","References" option. He then clicks/checks on the first non clicked reference and then clicks/unchecks the reference. The end result is no change to the reference list. However, for some reason the problem goes away .
The reference list:
Visual Basic For Applications
Microsoft Access 11.0 Object LIbrary
Microsoft Active X Data Objects 2.5 Library
Microsoft DAO 3.6 Object LIbrary
My machine: Win 7 Ultimate, Access 2003 SP3
His machine XP Professional, Access 2003 SP3
Any ideas on what the issue is or how to resolve this ongoing problem?