PowerQuery ODBC Refresh

Copper Contributor



I have an excel file setup with about 40 ODBC connections (to QuickBooks using QODBC application). Each connection corresponds to a separate table and worksheet in the file. In order for the tables to update, they need to be refreshed individually, one by one, otherwise the QuickBooks application will crash.


When I first open my file and refresh the first table, it triggers a refresh of all tables simultaneously, causing the a backlog of connections and ultimate crash (each ODBC runs through the same program QuickBooks via QODBC).


Does anyone know why it is attempting to refresh all connections at one time as opposed to just the individual table I am attempting to refresh? Below is both the SQL query and VBA code used to start the table refreshes?


VBA Refresh


Sub refresh_tables()

Dim r As Integer
Dim e As Integer
Dim key As Worksheet
Dim asheet As String
Dim delay As String
Set key = Sheets("Key")
r = 2
e = 0

On Error GoTo error
For r = 2 To Cells(Rows.Count, 13).End(xlUp).Row
delay = "0:0" & key.Cells(r, 16)
asheet = key.Cells(r, 13)


If key.Cells(r, 14) = "on" Then
ThisWorkbook.Worksheets(asheet).ListObjects(1).QueryTable.Refresh False
key.Cells(r, 14) = "complete"
key.Cells(r, 15) = Format(Now(), "YYYY.MM.DD.HH.MM")
Application.Wait (Now + TimeValue(delay))
e = 0
End If

If key.Cells(r, 14) = "on" Then
Application.Wait (Now + TimeValue(delay))
e = e + 1
If e = 3 Then
key.Cells(r, 14) = "failed"
e = 0
r = r - 1
End If
End If

On Error GoTo -1
Next r

End Sub




= Odbc.Query("dsn=DSN NAME", "sp_report GeneralLedger show AccountFullName, Account, AccountName, AccountNumber, AccountType, RefNumber, TxnNumber, TxnType, LastModifiedBy, ModifiedTime, Date, Name, Memo, Class, Debit, Credit

parameters IncludeAccounts = 'InUse', DateMacro = 'All')




0 Replies