Truncate and Insert SQL from EXCEL macro

[ldevrries]

I want to do other types of SQL commands in an EXCEL macro besides standard "select" queries. In particular, I'd like to do a "truncate table" , and also an "Insert" and an "Update". I recorded a macro to do a "select", thinking I would then just edit the "select" statement to do what I want, but it does not appear to do anything when I execute it. Here's a sample of the code that was recorded. Step 2 will be to pass some parameters (data) to SQL in the insert and update commands. Any suggestions and examples would be most sincerely appreciated. Thanks!

Sub Exec_Query()
'
'
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=lss;UID=userid;PWD=password;DBQ=LSS;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=N" _
), Array("LS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;")), Destination:= _
Range("A1"))
.CommandText = Array( _
"SELECT var1, var2" & Chr(13) & "" & Chr(10) & "FROM master_tbl" & Chr(13) & "" & Chr(10) & "WHERE (var1 = var2)")
.Name = "Query"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

[5631]

This code worked OK when I tried it against my SQLServer base.
But it is not a standard method of updating data.

Please Login or Register  to view this content.

As to Your code what do you mean by Var1, Var2 ?