Empty ADODB Recordset - not making sense

[CindyKredo]

I'm not sure which category to post this in, since the code has been confirmed to be good - but a recordset is going out to lunch! My code is sitting in an Excel file. The excel code pulls in Access data - the source points to one Access file (which also serves as a front end user file), which is linked to two other Access back end files. This has worked fine for years through different Access Versions. I recently installed Access 2019. No clue if that's a factor. The same subroutine is called multiple times - creating different recordsets. No error messages are displayed, and the code is working properly each time it is called EXCEPT for in one situation where it seems to run but the recordset is empty. It doesn't appear to be a code issue at all - but maybe something to do with the database having been modified in 2019.

The following is the Excel code that is used (leaving off the error handling):

 Public Sub getAccessData(sql As String, TargetRange As Range, sActiveShName As String)

Dim adors As Object
Dim adoconn As Object

Dim filenm As String
Dim xlsht As Excel.Worksheet

filenm = glFilePth

'make sure path is valid (this was checked in the past too, but leave it here)
If FileFolderExists(filenm) = True Then

    Set xlsht = ActiveWorkbook.Sheets(sActiveShName)
    Set adoconn = CreateObject("ADODB.Connection")
    
    adoconn.Open "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & filenm & ";"
     
    Set adors = CreateObject("ADODB.Recordset")
    adors.Open sql, adoconn, 2   '2 = adOpenDynamic
    
    'copy below the header row
    TargetRange.CopyFromRecordset adors

Else
    MsgBox "The database cannot be found.", vbOKOnly, "Please contact IT"
End If

If I run the SQL that is passed to this procedure, it returns the expected records. When the code is run, however, the recordset is empty. (BOF,EOF true in locals window). For future passes against the same database, (different sql) the code works fine. Even crazier is that if I use an old backup of the Access file (from a month ago, before I installed Access 2019), the recordset DOES return records (exact same source query, just sitting in an older front end file).

The query that is the source for the recordset contains multiple tables - (coming from the 2 back ends mentioned previously - both linked to the file that is referenced in the "filenm" global variable.) I'm stumped where to even begin troubleshooting given that the SQL DOES return records, and given that other sql pulling from the same two databases also works. Any thoughts?

 

[theDBguy]

Hi. When you said you tried the code again on an old copy of the same db and it worked, was that by using the same machine that was upgraded to 2019?

 

[pbaldy]

What's the SQL that fails? ADO wants % as a wildcard rather than *.

 

[CindyKredo]

Oh gosh - I forgot all about the % as the wildcard with ADO. The Where clause gets dynamically generated, but the base query that I'm starting with does have a wildcard in it (which it did not have in the past). I'm guessing that points me in the right direction - will be able to test this by Wednesday and will report back. (And yes, same PC dbguy but I am thinking it IS that wildcard). Will duly post the thanks after testing!

 

[theDBguy]

Hi. Good luck. Hope you're right.

 

[CindyKredo]

Sure enough - the new version of the database used the asterisk wildcard in one of the base queries, which wasn't needed in the old version. Thank you pbaldy, and thanks dBguy for the outreach!

 

[theDBguy]

Sure enough - the new version of the database used the asterisk wildcard in one of the base queries, which wasn't needed in the old version. Thank you pbaldy, and thanks dBguy for the outreach!

Congratulations! Glad to hear you got it sorted out. Cheers!

 

[pbaldy]

I'm happy I was able to help.