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

Hi Gustav:

I did not know there were OLE DB drivers for MySQL that would run through
Access... only ODBC drivers. There are a number of .Net examples but I do
not believe they are cross-compatible. This may not be the case as I was
working with such a configuration but it was about 4 or 5 years ago and
things should have progressed since then.

I have seen a configuration example... not tried. It shows "Provider=MySQL
Provider" code looking like "Provider=MySQLProv".

Below is a list of links to the latest software pieces:

1. MySQL - latest version from http://www.mysql.com/downloads
2. MySQLODBC (reqd for Access to MySQL) - http://www.mysql.com/downloads
3. MyOLEDB - http://www.mysql.com/downloads (should be MySQLProv.2.5)
4. WinMySQLAdmin - http://www.mysql.com/downloads
5. Access To MySQL -
http://www.mysql.com/downloads/win32/myaccess2000_1_4.zip
 
HTH

Jim 

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