excel:mysql: rs.Update not working
- by every_answer_gets_a_point
i am updating a table using an ODBC connection from excel to mysql
unfortunately the only column that gets updated is this one:
.Fields("instrument") = "NA"
where i am assigning variables to .Fields, it is putting NULL values!! what is going on here? here's the code
Option Explicit
Dim oConn As ADODB.Connection
Private Sub ConnectDB()
Set oConn = New ADODB.Connection
oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
"SERVER=localhost;" & _
"DATABASE=employees;" & _
"USER=root;" & _
"PASSWORD=pas;" & _
"Option=3"
End Sub
Function esc(txt As String)
esc = Trim(Replace(txt, "'", "\'"))
End Function
Private Sub InsertData()
Dim dpath, atime, rtime, lcalib, aname, rname, bstate, instrument As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
ConnectDB
With wsBooks
rs.Open "batchinfo", oConn, adOpenKeyset, adLockOptimistic, adCmdTable
Worksheets.Item("Report 1").Select
dpath = Trim(Range("B2").Text)
atime = Trim(Range("B3").Text)
rtime = Trim(Range("B4").Text)
lcalib = Trim(Range("B5").Text)
aname = Trim(Range("B6").Text)
rname = Trim(Range("B7").Text)
bstate = Trim(Range("B8").Text)
' instrument = GetInstrFromXML(wbBook.FullName)
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("datapath") = dpath
.Fields("analysistime") = atime
.Fields("reporttime") = rtime
.Fields("lastcalib") = lcalib
.Fields("analystname") = aname
.Fields("reportname") = rname
.Fields("batchstate") = bstate
.Fields("instrument") = "NA"
.Update ' stores the new record
End With
' get the last id
Set rs = oConn.Execute("SELECT @@identity", , adCmdText)
'MsgBox capture_id
rs.Close
Set rs = Nothing
End With
End Sub
