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

Hi Charlotte

No, but if I do so, error 80040e23 is raised ... about a row that is deleted or is waiting to be deleted.

/gustav

>>> cfoust at infostatsystems.com 19-04-2007 19:29 >>>
Are you sure it shouldn't be adUseServer?

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, April 19, 2007 10:11 AM
To: accessd at databaseadvisors.com 
Subject: [AccessD] OLE DB provider, AddNew doesn't add record to MySQL
table

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