'UPDATE' sql statement

[Fred]

I have an ODBC driver to access a database but I can't figure out how to run
an UPDATE statement from Excel VBA.
I can create a query to return data and below is the recorded macro which
works fine and so shows that I can connect to the database OK.
So, how would I change this to run a simple UPDATE statement like
"UPDATE root.CLNDR CLNDR SET CLNDR.YN='Y'
WHERE CLNDR.DATE_=20041001;"

Sub Macro1()
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=TIMS.udd;", _
Destination:=Range("A1"))
.Sql = Array("SELECT CLNDR.DATE_, CLNDR.DAY, " & _
"CLNDR.NUMA, CLNDR.NUMB, CLNDR.YN" & vbCrLf & _
"FROM root.CLNDR CLNDR")
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With
End Sub

Thanks
Fred

 

[Tim Williams]

I'm not sure the built-in query table functionality will handle
updates.

Add a reference to the ADO 2.x library in the VBE and try this
(untested - you may have to fix the connection string a bit)

Tim.


Sub Tester()
Dim sSQL

sSQL = "UPDATE root.CLNDR CLNDR SET " & _
"CLNDR.YN='Y' WHERE CLNDR.DATE_=20041001"

MsgBox RunUpdate(sSQL)
End Sub



Function RunUpdate(sSQL As String) As Long

Dim lngRecs As Long
Dim oConn As ADODB.Recordset

Set oConn = CreateObject("ADODB.Recordset")
oConn.Open "ODBC;DSN=TIMS.udd;"
oConn.Execute sSQL, lngRecs, adCmdText
oConn.Close
Set oConn = Nothing

RunUpdate = lngRecs

End Function

 

[Fred]

Thanks Tim but the ADODB.Recordset object is not recognised and I get an
error when I compile the project "User-defined type not defined.
I did add the reference to "Microsoft ADO Ext 2.7 for DDL and security". I
also tried adding "Microsoft DAO 3.51" but ADODB.Recordset is not
recognised.
I am running Office '97. Do I need a later version?

Fred

 

[Bob Phillips]

Try it with late binding. Change

Dim oConn As ADODB.Recordset

to

Dim oConn As Object

 

[ob3ron02]

I'm not too familiar with this stuff, but this is something that worke
for me in Excel2000


Code
-------------------
sqlstring = "SELECT DISTINCT EQUIPMENTSPEC.EQNUM, EQUIPMENTSPEC.ASSETATTRID, EQUIPMENTSPEC.ALNVALUE FROM MAXIMO.EQUIPMENTSPEC EQUIPMENTSPEC"
connstring = "ODBC;DSN=maximo;Database=maximo"

With Worksheets("Data").QueryTables.Add(Connection:=connstring, Destination:=Worksheets("Data").Range("B1"), Sql:=sqlstring)
.Name = "Maximo Query for Data Worksheet"
.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 Wit

 

[TK]

Hi Fred
To use ADO objects in an application add a reference
to the ADO component. From the VBA window select

Tools/References< check the box

"Microsoft ActiveX Data Objects 2.x Library"

Update table-name
Set Field_Name = 'new value'
Where Field_Name = Condition
i.e
Update Products
Set apples = 'Red Delicious'
Where State = Washington

Good Luck
Tk