Opening recordsets with MySQL backend

[303factory]

I have to ask why you're using ADO for ODBC linked tables. With linked tables, DAO is preferred, as the data is coming through Jet, anyway, so you ought to use Jet's native interface. Using ADO means you're using Jet, ODBC, ADO and OLEDB, instead of just DAO, Jet and ODBC. I'm not suggesting this would resolve any problem, just that the use of ADO with data being handled by Jet (i.e., any ODBC linked table) makes no sense.

Now, if you had a MySQL OLEDB driver and used it directly (instead of using ODBC linked tables), then ADO would be perfectly sensible. But that doesn't appear to be what you're doing.

I must confess I'm such a n00b in this area I don't really understand what you're saying here.. I set things up the way I did following these instructions here:

http://dev.mysql.com/doc/refman/5.0...nector-odbc-examples-tools-with-access-import

http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-dsn-windows.html

If you can suggest a better way to do things you may have to spell it out with glove puppets (or perhaps link me to some instructions) but I'd certainly be interested.

 

[Banana]

Well as you may know, there are several ways to skin a cat. What DFenton is concerned about is that you're skinning the cat twice.

Generally speaking, Access developers have two data access libraries that are similar in function and scope but different in goals. Access's "native" library is named Data Access Objects (DAO), while we can also use another library more suited for connecting to various source named "ActiveX Data Objects" (ADO).

In context of MySQL/Access application, there are two general methods to get what you need.

1) Use linked table and DAO.

Whenever you create a linked table, you are using Jet (and thus DAO). The same still applies when we're using stored queries (with exception of pass-through query which goes direct to ODBC layer, IINM).

2) Use ADO upon MySQL

Instead of referring to linked tables and other Access objects, you can just open a connection on MySQL, issue commands directly to MySQL and return a recordset.

You still can bind forms to a ADO recordset but you cannot do so through Access UI; it has to be done in code.


You can use ADO upon Access/Jet objects, but that is not Jet's native library and when we're using linked table, we're actually making it even complicated.

To illustrate in rough terms:

Using ADO on MySQL:

ADO -> ODBC -> MySQL

Using DAO on linked tables

DAO -> ODBC -> MySQL


Using ADO on linked tables

ADO -> Jet -> DAO -> ODBC -> MySQL


DFenton mentioned OLEDB, which is basically a newer and supposedly a successor to OLEDB, but in MySQL context, ODBC driver is in active development and mature compared to OLEDB driver provided by a third party, so in that context, it's ODBC though you can choose between ADO or DAO, and even can use both in the same application but it's necessary to know how to use either correctly.

I hope that help....

 

[303factory]

Well as you may know, there are several ways to skin a cat. What DFenton is concerned about is that you're skinning the cat twice.

Generally speaking, Access developers have two data access libraries that are similar in function and scope but different in goals. Access's "native" library is named Data Access Objects (DAO), while we can also use another library more suited for connecting to various source named "ActiveX Data Objects" (ADO).

In context of MySQL/Access application, there are two general methods to get what you need.

1) Use linked table and DAO.

Whenever you create a linked table, you are using Jet (and thus DAO). The same still applies when we're using stored queries (with exception of pass-through query which goes direct to ODBC layer, IINM).

2) Use ADO upon MySQL

Instead of referring to linked tables and other Access objects, you can just open a connection on MySQL, issue commands directly to MySQL and return a recordset.

You still can bind forms to a ADO recordset but you cannot do so through Access UI; it has to be done in code.


You can use ADO upon Access/Jet objects, but that is not Jet's native library and when we're using linked table, we're actually making it even complicated.

To illustrate in rough terms:

Using ADO on MySQL:

ADO -> ODBC -> MySQL

Using DAO on linked tables

DAO -> ODBC -> MySQL


Using ADO on linked tables

ADO -> Jet -> DAO -> ODBC -> MySQL


DFenton mentioned OLEDB, which is basically a newer and supposedly a successor to OLEDB, but in MySQL context, ODBC driver is in active development and mature compared to OLEDB driver provided by a third party, so in that context, it's ODBC though you can choose between ADO or DAO, and even can use both in the same application but it's necessary to know how to use either correctly.

I hope that help....

I see that makes more sense. Option 2 isnt really feasable at the moment because the client is a huge monster of code and forms and I need to keep additional development to a minumum.
So presumably using DAO on linked tables would make more sense here?

Any chance you can point me at instructions on how to change this? I've dont some googling but it there doesnt seem to be an obvious tutorial on the subject..

 

[Banana]

If you've already devleoped using linked tables, then yes #1 will work just fine.

If you go to VBA editor, and enter "DAO ADO code", the first help entry should be "Converting DAO to ADO", which was written back when Microsoft was pushing everyone to use ADO instead of DAO (but they've since reversed the stance, leaving the developers with options to develop with DAO, or ADO or both).

The article gives you a side by side comparison of DAO and ADO and some general forms. See if it help you get started.

HTH.