CODEWITHSUNDEEP
excelvbaodbc
Jonas Helium
Jonas Helium

Reputation: 59

vba ODBC refreshing

I got a problem with waiting for my query refresh until further code execution.

ThisWorkbook.Connections("ConnectionName").Refresh
Debug.Print ThisWorkbook.Connections("ConnectionName").ODBCConnection.refreshing 'Prints True
While ThisWorkbook.Connections("ConnectionName").ODBCConnection.refreshing
DoEvents
Wend
Debug.Print "updated"
[...]

It seems like ThisWorkbook.Connections("ConnectionName").ODBCConnection.refreshing is never turning false as the data connection doesn't finish while the macro is running.

I have another query depending on the result of another query (first ODBC connection and then UNION all with some additional data from Excel) I can't block Excel for the query runtime, therefore BackgroundRefresh: False is not an option.

Thanks

Upvotes: 0

Views: 2377

Answers (2)

Don
Don

Reputation: 1

Dim con As ODBCConnection
Set con = ThisWorkbook.Connections("ConnectionName").ODBCConnection
con.BackgroundQuery = False
con.Refresh

Upvotes: 0

Jonas Helium
Jonas Helium

Reputation: 59

I solved it using

Application.CalculateUntilAsyncQueriesDone

Upvotes: 1

Related Questions