Hi,
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
Else
End If
error:
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
Else
r = r - 1
End If
Else
End If
On Error GoTo -1
Next r
End Sub
SQL ODBC Query
= 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')
Thanks!
