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

Hi Marty

Tried that, but no trace at all. The OLE DB provider seems to bypass the ODBC driver.

/gustav

>>> martyconnelly at shaw.ca 19-04-2007 19:47 >>>
Could be cursor type or location.
Try running an ODBC trace log works for other DB's too.
http://dev.mysql.com/doc/refman/5.0/en/myodbc-configuration-trace.html 
or look through here
http://dev.mysql.com/doc/refman/5.0/en/myodbc-connector.html 

Gustav Brock wrote:

>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
>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada