Excel VBA Wait for Querytable Refresh to Finish

[medberg]

Hey everyone,
In Excel VB I have a querytable that I use to send update queries to a SQL server. It is embedded in a for loop that sends thousands of queries. Each time through the for loop, the query is constructed, and I use the command

Sheet3.QueryTables(1).CommandText = varSQL

varSQL is the UPDATE query. After this, I need to call

Sheet3.QueryTables(1).refresh

to make the update query actually run. Now my issue is I cannot have this query refreshing in the background, so after this refresh I call

Sheet3.QueryTables(1).cancelrefresh

Sometimes, the refresh is not finished when it cancels, and the update is not actually stored in the database. Is there any way to make Excel wait for the refresh to finish once before canceling the background refresh? I've tried many things, and unfortunately

.refresh backgroundquery:=true

is not an option. Thanks!

 

[John_w]

Try:

Sheet3.QueryTables(1).refresh BackgroundQuery:=False

and get rid of the CancelRefresh.

 

[medberg]

Thanks for the really quick reply. When I do that, do I include the .refresh? I tried it with and without and got both times "Runtime error '1004': Application-Defined or Object-Defined Error. Thanks.

 

[John_w]

No, you only need one .Refresh. The whole thing should be:

Sheet3.QueryTables(1).CommandText = varSQL
Sheet3.QueryTables(1).refresh BackgroundQuery:=False

 

[medberg]

Thanks again for the reply. Well, my code is weird. I basically check to see if there are any connections, and if there is not, I run If

ActiveWorkbook.Connections.Count = 0 Then
            With Sheet3.QueryTables.Add(Connection:="ODBC;DSN=" + DSN + ";Trusted_Connection=Yes;APP=2007 Microsoft Office system;DATABASE=" + DATABASE + ";" _
            , Destination:=Range("Sheet3!$A$1"), Sql:=varSQL)
            .rEFRESH BackgroundQuery:=False
            End With
        End If

If there is a connection, I want to just change the query, so I run

If ActiveWorkbook.Connections.Count > 0 Then
            Sheet3.QueryTables(1).CommandText = varSQL
        End If

Is there a better way to be doing this? The code I have here complies to what you suggested, but I am getting that same 1004 error on the .refresh backgroundquery line. Thanks for any more advice in advance.

 

[John_w]

I have Excel 2003 which doesn't have the Connections object, and I don't know if there is a relationship between Connections and QueryTables. You could check ActiveSheet.QueryTables.count, or try ActiveWorkbook.Connections(1).ODBCConnection.Refresh (see http://msdn.microsoft.com/en-us/library/bb216064.aspx).

 

[medberg]

Ok, thank you for all of your help! I appreciate it.