[AccessD] MySQL OLE DB Provider

Hi all

I had some troubles locating such an animal (subject) but found this site:

  http://luggle.com/~sean

I was about creating a provider for MySQL this way using the generic MS ODBC wrapper:

  strProvider = "Provider=MSDASQL.1;Data Source=MySQLTest";Persist Security Info=True;User ID=root;Password=myPassword
  cnn.Open strProvider
  rst.Open "testtable", cnn, adOpenKeyset

where MySQLTest is a database source for MySQL as defined in the ODBC manager telling which database (Initial Catalog, see below) and port to connect to.
rst.RecordCount returns -1.

But with Sean's Open Source driver you do like this (beware of line breaks):

  strProvider = "Provider=OleMySql.MySqlSource.1;Data Source=192.168.1.100,3306;Initial Catalog=test;Persist Security Info=True;User ID=root;Password=myPassword"
  cnn.Open strProvider
  rst.Open "select * from testtable", cnn, adOpenKeyset

Note that a table name on its own cannot be used; you must use an SQL sentence.

rst.RecordCount returns the actual record count.

Speed of the two methods seems to be identical, and one method may have advantages and limitations over the other - I haven't tested it that much.
However, both are capable of reading longs from fields of BIGINT datatype which you (as far as I know) can't by ODBC and DAO (at least not if the BIGINT field is the primary ID).

But the OLE DB driver is much easier to install: Just one dll and registration of this - and inclusion of the usual libmySQL.dll if you are not installing on the machine running the MySQL server.