Excel/Access VBA - Cant get last used row because of query refresh

Question

I'm refreshing a query on Access and looping over the table, but the loop overruns the refresh - so I dont get the correct last row.

After refreshing, a should equal 2746, but it's returning 1478 which is the last row before refreshing.

vPath = ThisWorkbook.Sheets(vHome).Range("LogTblFolder")
vFile = ThisWorkbook.Sheets(vHome).Range("LogTblFile")
    Set wb = Workbooks.Open(vPath & vFile)

        Sheets("Main").Select
        ActiveWorkbook.Connections("CTA_DB_Full3").Refresh
        ActiveWorkbook.Connections("CTA_DB_Full3").Refresh

            Call WaitForRefresh(1)
            a = 1:Do Until Sheets("Main").Cells(a, 1).Value2 = "":a = a + 1:Loop
                  vArray = Sheets("Main").Range(Cells(2, 1), Cells(a, 94))

    wb.Close False

---

Public Function WaitForRefresh(intSeconds As Integer)
Dim dblStart As Double

If intSeconds > 0 Then

    dblStart = Timer()

    Do While Timer < dblStart + intSeconds
    Loop

End If
End Function`

Help is much appreciated!!!

Answer 1

This may be a start

Sub x()

    Dim l As ListObject

    Set l = ActiveSheet.ListObjects(1)

    l.QueryTable.BackgroundQuery = False

    Debug.Print l.ListRows.Count
    l.Refresh
    Debug.Print l.ListRows.Count

    varray = l.DataBodyRange.Value

End Sub