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" <leavemealone@home wrote in message
...
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