Truncate, Insert, or Update SQL command in EXCEL Macro
"LD" wrote:
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. The query is passed to an ORACLE database through an ODBC
connection.
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(Arra y( _
"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
One way to do that is using ADO, Connection and Command objects. Pass the
Command object strings containing SQL queries and then use Execute
Sub ADOTest()
Dim Cnn As ADODB.Connection
Dim Cmd As ADODB.Command
Set Cnn = New ADODB.Connection
Cnn.ConnectionString = _
"Data Source='MyDataSource';"
Cnn.ConnectionTimeout = 30
Cnn.Open
Set Cmd = New ADODB.Command
Cmd.ActiveConnection = Cnn
strSQLCreate = "CREATE TABLE MyTable " & _
"(Field1 int , " & _
"Field2 char(20) NULL, " & _
"Field3 datetime NULL)"
Cmd.CommandText = strSQLCreate
Cmd.Execute
strSQLInsert = "INSERT INTO MyTable " & _
"VALUES (987, 'some value', '11/25/2006')"
Cmd.CommandText = strSQLInsert
Cmd.Execute
strSQLUpdate = "UPDATE MyTable " & _
"SET MyTable.Field2 = 'another value' " & _
"WHERE Field1 = 987"
Cmd.CommandText = strSQLUpdate
Cmd.Execute
strSQLTruncate = "TRUNCATE TABLE MyTable"
Cmd.CommandText = strSQLTruncate
Cmd.Execute
strSQLDrop = "DROP TABLE MyTable"
Cmd.CommandText = strSQLDrop
Cmd.Execute
Cnn.Close
Set Cnn = Nothing
End Sub
I use SQL Server, but I think you can easily change the queries to work with
your database. You also need to change the connectio string.
Hope this helps some.
--
urkec
|