Results 1 to 10 of 10
  1. #1
    joeserd is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    2

    Unhappy Excel VBA ADO Query fails with leading wildcard works without leading wildcard

    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".

  2. #2
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Do you experience the same issue using the ANSI-89 "*" wildcard character instead of the "%"?

    Sorry - looks like Excel doesn't speak ANSI-89, so never mind.

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Maybe the machines that are not behaving properly have 64bit office vs. 32bit

  4. #4
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    That's what he said, but it's only some of the 64 bit machines.

    joeserd - have you checked the versions of the Access and Excel library references?

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    I was thinking Office not 64bit Operating System.....

  6. #6
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Oh, crud, yeah. 64 bit office/excel vs 32 bit office/excel on 64 bit machine. Yep, that's another item to verify, along with the library references... and also the requirement for / use of 64-bit-safe versions of the DLLs.

  7. #7
    joeserd is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    2
    These are all 32 bit office machines all with the same versions of the various VBA references. Very strange.

  8. #8
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Okay, check all your VBA DLL references to make sure you have the 64-bit-safe version. It's not likely to be the problem, but it's the next on the punch list.

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Dal Jeanis View Post
    Okay, check all your VBA DLL references to make sure you have the 64-bit-safe version.
    Dal,

    What about checking the actual DLL file version on the PC? Seems he is late binding the Access driver with his connection.

  10. #10
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    We've already stripped the low-hanging fruit. If the VBA code is 64-bit-safe, then I'd uninstall/reinstall Office on one of the failing machines, and see if that straightened up the problem. That would fix a DLL version error and/or a registry error.

    As a last resort, I'd verify personal and network permissions from the failing machine (ie make sure that the person logging on at that machine had been successful logging on from a non-failing 64 bit machine.) I can't see how an error there would cause these symptoms, but it's a difference that could be tested. (Like searching for your car keys under the streetlight because it's too dark in the back alley where you lost them...)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 03-18-2013, 11:35 AM
  2. Replies: 3
    Last Post: 10-31-2012, 12:50 PM
  3. Replies: 1
    Last Post: 08-13-2012, 03:38 PM
  4. Problem with a wildcard in a query criteria
    By desk4tbc in forum Programming
    Replies: 1
    Last Post: 08-10-2011, 06:02 PM
  5. Parameter Query & Wildcard
    By Rick West in forum Queries
    Replies: 8
    Last Post: 12-29-2009, 10:54 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums