[AccessD] Access with MySQL BE

Steve,
Gives me great comfort to know that you are doing this and the example was
of great help to get me started. I eventually want to make this an online
app with a web interface but I just don't have the time to redo it now so
this will be a perfect solution for me!

Many Thanks,
John B.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Schapel
Sent: Friday, August 01, 2008 3:28 AM
To: Access Developers discussion and problem solving
Subject: Re: [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=33
06;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