Excel VBA To SQL - Tell me when the Query is Done!

[Hacktastic]

All,

I have a query that gets run in Excel, and everything works fine and dandy, however, sometimes the query takes a bit long in MS SQL.

Is there a way for excel to alert me when the queries I placed are done processing ?

 

[PHV]

You may use the AfterRefresh event procedure of your QueryTable object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hacktastic]

May I ask, How?

 

[SkipVought]

http://support.microsoft.com/kb/213187

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Hacktastic]

Thanks skip,

i understand the premise of afterrefresh, however, I do not know where to call in my sql code ?

Public Sub SQLUpload()

Dim objConn As New ADODB.Connection
Dim objCmd As ADODB.Command
Dim rst As New ADODB.Recordset

Dim strConn As String
Dim objErr As ADODB.Error
Dim DSN, SQL, Rng, clrRange, c As Variant
Dim field, i As Integer

Workbook = ActiveWorkbook.Name


' open connection
strConn = "Description=tpdevdb;DRIVER=SQL Server;SERVER=xxxx;UID=xxxx;APP=2007 Microsoft Office system;WSID=xxxxx;DATABASE=xxxxx;Trusted_Connection=Yes"
Set objConn = New ADODB.Connection
objConn.CursorLocation = adUseClient
objConn.CommandTimeout = 900000
objConn.Open strConn


SQL = "Delete from table1" & _
"BULK INSERT table1 FROM '\\test.csv' WITH ( FIRSTROW = 2, MAXERRORS = 0, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )"

Set objCmd = New ADODB.Command
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = SQL
objCmd.CommandType = adCmdText
objCmd.CommandTimeout = 15000
objCmd.Execute Options:=adExecuteNoRecords
Set objCmd = Nothing

Set rst = Nothing

End Sub


Can you please assist. Thanks

 

[PHV]

...
objCmd.Execute Options:=adExecuteNoRecords
MsgBox "bulk insert of test.csv done"
Set objCmd = Nothing
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hacktastic]

OMG, so obvious!

thanks!