ExcelBanter - View Single Post

Jamie Collins

(Bernard Soh) wrote ...

if I run the code with BackgroundQuery:=True,
it executes perfectly, table is dropped. However this means that my
next lines of codes will start executing, and that won't do. So I add
a line to check while query is still Refreshing, if not sleep for
1sec, check again, but it never ends.

You should consider using ADO to execute the SQL asynchronously.
Here's a hint:

' --- In a class module ---
Option Explicit

Dim WithEvents m_Con As ADODB.Connection

Private Sub m_Con_ExecuteComplete( _
ByVal RecordsAffected As Long, _
ByVal pError As ADODB.Error, _
adStatus As ADODB.EventStatusEnum, _
ByVal pCommand As ADODB.Command, _
ByVal pRecordset As ADODB.Recordset, _
ByVal pConnection As ADODB.Connection)

End Sub

You should also consider whether you actually need a temp table. Often
a derived table/sub-query in SQL code will do the same.

Jamie.

--