update record only works when there is no auto_increment
- by every_answer_gets_a_point
i am accessing a mysql table through an odbc connection in excel
here is how i am updating the table:
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
when the schema of the table is this, updating it works:
create table batchinfo(datapath text,analysistime text,reporttime text,lastcalib text,analystname text, reportname text, batchstate text, instrument text);
but when i have auto_increment in there it does not work:
CREATE TABLE batchinfo ( rowid int(11) NOT NULL AUTO_INCREMENT, datapath text, analysistime text, reporttime text, lastcalib text, analystname text, reportname text, batchstate text, instrument text, PRIMARY KEY (rowid) ) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=latin1
has anyone experienced a problem like this where updating does not work when there is an auto_increment field involved?
connection string:
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
also here's the rs.open:
rs.Open "batchinfo", oConn, adOpenKeyset, adLockOptimistic, adCmdTable
