Truncate, Insert, or Update SQL command in EXCEL Macro

[Guest]

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

 

[Guest]

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.

 

[Guest]

Seems I may not have all the correct "object libraries" selected to use ADO,
but cannot determine which to select when I go to Tools/References. The
error message I get for the very first dimension statement is "Compile Error:
User-defined type not defined".

Any suggestions?

 

[Tim]

Look for "Microsoft ActiveX Data Objects 2.x"

Exact versions on your PC may vary - pick any of the later point releases
and you should be fine.

I have