ADO recordset seems to cache old results

Question

I am having an issue where I create a connection string (Excel) and query a worksheet, I can get the results, placed into a recordset, and then transposted into a destination worksheet.

The problem is that for some reason, if I go back and edit this worksheet (without saving), the recordset is caching the OLD results. eg: I first queried 10 rows, returned 10, deleted 7 of them, execute the query again but it returns the original 10 as opposed to my expectation for the remaining 3. I've used this method thoroughly and have never had this issue and believe it to be memory related somehow...

Please help...

Public Sub sbTest()

Dim wb As Workbook

Dim wsData As Worksheet, _
wsTmp As Worksheet

Set wb = ThisWorkbook
Set wsData = wb.Sheets("Data"): wsData.Cells.ClearContents
Set wsTmp = wb.Sheets("Temporary")



sSQL = "SELECT * FROM [" & wsTmp.Name & "$]"
Call mUtilities.sbRunSQL(sConnXlsm, wb.FullName, sSQL, wsData.Cells(1, 1))

    'Cleanup
Set wb = Nothing
Set wsData = Nothing
Set wsTmp = Nothing

End Sub


Public Const sConnXlsm As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=zzzzz;Extended Properties=""Excel 12.0 Macro;HDR=YES;IMEX=1"";"

Public Sub sbRunSQL(ByVal sConn As String, ByVal sSource As String, ByVal sSQL As String, ByVal rDest As Range, _
Optional ByVal bHeader As Boolean = True, Optional ByVal bMsg As Boolean = True)


Dim oCn As ADODB.Connection, _
oRs As ADODB.Recordset, _
oFld As ADODB.Field

Dim vArr As Variant

    'Setup
On Error GoTo Cleanup

    'Handle DELETE and INSERT INTO Access queries seperately from other types
If (UCase(Left(sSQL, 6)) = "DELETE" Or UCase(Left(sSQL, 11)) = "INSERT INTO") And sConn = sConnAccess Then

    Set oCn = CreateObject("ADODB.Connection")
    oCn.Open Replace(sConn, "zzzzz", sSource)

    sSQL = Replace(sSQL, "FROM ", "FROM [Excel 8.0;HDR=YES;DATABASE=" & ThisWorkbook.FullName & "].")
    oCn.Execute sSQL

        'Exit if successful
    oCn.Close
    Set oCn = Nothing
    Exit Sub

Else

    Set oRs = Nothing
    Set oRs = New ADODB.Recordset
    oRs.Open sSQL, Replace(sConn, "zzzzz", sSource), adOpenForwardOnly, adLockReadOnly

    If Not (oRs.BOF And oRs.EOF) Then
        vArr = oRs.GetRows
        vArr = fTranspose(vArr)                                 'The .GetRows process tranposes the data so we need to undo this

        If bHeader = True Then
            For i = 0 To oRs.Fields.Count - 1
                rDest.Offset(0, i).Value = oRs.Fields(i).Name
            Next i
            Range(rDest.Offset(1, 0), rDest.Offset(UBound(vArr, 1) + 1, UBound(vArr, 2))) = vArr
        Else
            Range(rDest, rDest.Offset(UBound(vArr, 1), UBound(vArr, 2))) = vArr
        End If

            'Exit if successful
        oRs.Close
        Set oRs = Nothing
        Exit Sub

    End If
End If

    'Cleanup
Cleanup:
If bMsg = True Then
    MsgBox "Critical error!" & vbNewLine & vbNewLine & _
    "Error: " & Err.Description & vbNewLine & vbNewLine & _
    "SQL: " & sSQL, vbCritical + vbOKOnly
End If

Set oCn = Nothing
Set oRs = Nothing

End Sub

Answer 1

For what it's worth, I was able to solve this and the issue seems to be related to some kind of latency bug if multiple instances of Excel are open. I've simply forced only one book to be open in such cases.

Set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
Set oProc = oWMI.ExecQuery("SELECT * FROM Win32_Process WHERE NAME = 'Excel.exe'")

If oProc.Count > 1 Then
    MsgBox "There are " & oProc.Count & " instances of Excel open." & vbNewLine & vbNewLine & _
    "Only 1 instance is allowed open in order to update database.", vbCritical + vbOKOnly
    GoTo Cleanup
End If