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

Hi Jim

And so I did - the developer is now organised as Cherry City Software:
http://cherrycitysoftware.com/CCS/Home/Default.aspx

<quote>
We never supported upadate/delete/add function on the fly. Instead, you have
to use script or SP call to do that.

We know it would be nice to that work. However, we are currently short of
resources.

Regards,
Cherry City Software Support Team
</quote>

So no luck. You have to run an SQL "Insert Values" command. Not very fancy.

/gustav


>>> Gustav at cactus.dk 20-04-2007 16:02 >>>
Hi Jim

Sorry, I forgot to mention that this provider is non-MySQL stuff. It is for download here:

  http://luggle.com/~sean 

I better address my question to the author ...

/gustav


>>> accessd at shaw.ca 19-04-2007 21:35 >>>
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