[AccessD] OLE DB provider, AddNew doesn't add record to MySQL table

Gustav Brock Gustav at cactus.dk
Thu Apr 19 12:10:41 CDT 2007


Hi all

In A2003, if I try to add a record to a MySQL table via the OLE DB provider, nothing happens. No errors, it runs fine, but no record added.
Any ideas why?

  Dim cnn     As ADODB.Connection
  Dim rst     As ADODB.Recordset
  
  Dim lngRow  As Long
   
  Set cnn = New ADODB.Connection
  Set rst = New ADODB.Recordset

  cnn.ConnectionString = "Provider=MySQL Provider;Data Source=10.20.30.40;User ID=root;Password=password;Initial Catalog=TestDb;Mode=Read|Write"
  cnn.Open
  cnn.CursorLocation = adUseClient

  With rst
    .Open "Select * From tblSample", cnn, adOpenKeyset, adLockOptimistic
    For lngRow = 1 To lngRows
      .AddNew
        .Fields(1).Value = "Some text"
      .Update
    Next
    .Close
  End With
  
  Set rst = Nothing
  Set cnn = Nothing
  
End Function

If I link the table (via ODBC) and run an append query, records are appended as expected.

/gustav





More information about the AccessD mailing list