CindyKredo
New member
- Local time
- Today, 06:26
- Joined
- Nov 8, 2019
- Messages
- 19
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):
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?
The following is the Excel code that is used (leaving off the error handling):
Code:
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 IfIf 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?
