[AccessD] Access with MySQL BE

Hi John,

I think using a DSN is simplest.

You need to obtain the MySQL ODBC driver from 
http://dev.mysql.com/downloads/connector/odbc/5.1.html and install on 
the local machine.

Then create a system DSN based on this driver, to point to the MySQL 
database.  You will need to know connection details for the MySQL database.

After that, from within your Access application, you will simply be able 
to link to the MySQL tables, via the File|Get External Data menu.  And 
then you will be able to interact with the data in these tables just the 
same as any other linked tables.

In one of my examples, we have a Scheduled Task that runs daily, runs 
Delete queries to clear out all the MySQL tables, and then a series of 
Append queries to re-populate the MySQL tables with current data derived 
from the Access tables in the main application database.

In another example, the MySQL connectivity is required from the main 
application according to user requirement, which in practice is 
infrequently, so we connect to the MySQL database in code.  Here is a 
sample snippet of the code I use:

     Dim dbs As DAO.Database
     Dim tdf As DAO.TableDef
     Dim tblName As String
     Dim srcTblName As String
     Dim Conn As String
     Set dbs = CurrentDb
     tblName = "LocalTable"
     srcTblName = "MySQL_Table"
     Conn = 
"ODBC;DATABASE=<databasename>;DESCRIPTION=<xxx>;DSN=<MyDSN>;OPTION=0;PORT=3306;SERVER=<ServerName>;;TABLE=<TableName>"
     Set tdf = dbs.CreateTableDef(tblName)
     tdf.SourceTableName = srcTblName
     tdf.Connect = Conn
     dbs.TableDefs.Append tdf
     dbs.TableDefs.Refresh
(… repeated for each applicable MySQL table)

In this case, we chose to run this code on the Open event of a form, and 
on the Close event we have:
     CurrentDb.TableDefs.Delete "LocalTable"
… for each linked MySQL table.

Hope that gives you something to go on.

Regards
Steve


John Bartow wrote:
> Hi Steve,
> Data is minimal. It is a membership list with some attributes that drive
> various web page lists. Keeping it in the Access BE and just uploading it to
> MySQL would actually be just great. Care to share your methods?