Wayn on April 30 posed a similar question. He apparently discovered the answer himself, stating that MDAC 8 sucks. Since I don't believe I installed MDAC 8 on my system, his anwer left much to be explained.
Simply put I have one machine out of three XP machines that produces the error above I would really like to solve the problem. The same machine which is now failing handled everything properly a week ago. The original code is too large to show here, so I simply created a sample from a microsoft example and it fails the same way. The code runs a copy of the OpenMyDB subroutine shown in a microsoft explanation of ADO as follows:
Dim cnnNorthwind As New Connection
Dim rsCustomers As Recordset
'Create the connection.
cnnNorthwind.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
'Create recordset reference and set its properties.
Set rsCustomers = New ADODB.Recordset
rsCustomers.CursorType = adOpenKeyset
rsCustomers.LockType = adLockOptimistic
'Open recordset and print a test record.
rsCustomers.Open "Customers", cnnNorthwind, , , adCmdTable
Debug.Print rsCustomers.Fields(0).Value, rsCustomers.Fields(1).Value
The code fails with the Ado connection failed open method. The DB exists, the path is correct, but the method fails. My only comment at this point, is something must have changed and resulted in an incompatibility between components, dll's or something.
Thanks in advance for any assistance