I have an application that is in use on 100+ computers, Vista and Windows 7, running Excel 2007 and Excel 2010. This problem has appeared on only 4 computers, all 64 Bit Windows 7 computers. Other 64 bit Windows 7 computers do not have this problem.
I have a 0.25 GB Access 2010 database with 2 tables. The "Documents" table has 6 fields and 800,000 records. I am using Excel 2010 to query the database. This code executes fine on all computers:
Set adocnn = New ADODB.Connection
adocnn.Open "DSN=MS Access Database;DBQ=C:\MatrixD.accdb;DriverId=25;FIL=MS Access"
Set adorst = New ADODB.Recordset
adorst.Open "SELECT COUNT(*) FROM Documents WHERE Title LIKE 'SOP%' ", adocnn, adOpenDynamic, adLockReadOnly
However, when I change the select statement to
"SELECT COUNT(*) FROM Documents WHERE Title LIKE '%SOP%' "
I get the following error on the 4 computers:
Run-time error '-2147467259 (80004005)' Cannot open database ''. It may not be a database that your application recognizes, or your file may be corrupt.
All machines have the same version of the Excel VBA references for ADO connections, "Microsoft ActiveX Data Objects 2.8 Library" and "Microsoft ADO Ext. 2,8 for DDL and Security".