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

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


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com