CODEWITHSUNDEEP
vbams-accessadodb
Acantud
Acantud

Reputation: 508

ADODB in Access - Will not insert, but does not raise error

I've been staring at this the past few hours-- I figure now that there is some subtle and devious bug in MySQL causing this, or it's an incredibly obvious solution and I just need another set of eyes. I'd appreciate any help.

I'm using Access to do some data entry work. I have the tables I am working with linked, but I'm primarily dealing with them through explicit ADODB connections (Which will hopefully make transactions easier and allow me to use SELECT @@IDENTITY). The tables are in MySQL on an server within my environment.

I've done similar work before, but for some reason now no updates are made. It doesn't raise an error, I've checked the Connection's error property...for some reason it just breezes through this code without raising an error. The table being referenced is completely empty now, it has quite a few fields but only 2 are that required - an autonumber ID and the field being populated.

Can anyone see something blatantly wrong here? Option Explicit is on, it seems like it's making the connection fine, otherwise I imagine it would error when opening the recordset or connection. Does anyone have any debugging suggestions?

One last note, the code was more complicated - I'm experimenting with making a class to store my connection, to make those things I described easier. My worst-case thought it something I did there, like beginning a transaction without closing it, has caused something to hiccup in MySQL.

Sub ADODBError()

    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection

    cnn.Open "driver={mysql odbc 5.2a driver};" & _
       "server=my.mysql.server;" & _
       "user=myUser;password=myPwd;" & _
       "database=callcenter"

    Dim firstDayOfWk As Date
    firstDayOfWk = #8/18/2014#

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    rst.Open "WeeklyCallCenterStats", cnn, adOpenForwardOnly, adLockBatchOptimistic, adCmdTable

    With rst
        .AddNew
        .Fields("WeekStarting") = firstDayOfWk
        .Update
        .Close
    End With

End Sub

Upvotes: 1

Views: 336

Answers (1)

Tmdean
Tmdean

Reputation: 9309

When you open a ADO recordset in batch locking mode, the changes you make to the recordset are not actually transmitted until you call UpdateBatch on the recordset.

Change the locking mode in your rst.Open call (I recommend adLockOptimistic unless it's a quickly changing table), or call rst.UpdateBatch after making your changes.

Upvotes: 3

Related Questions