[AccessD] Converting to MySql

Steve Schapel miscellany at mvps.org
Mon Jul 9 15:16:18 CDT 2007


Rocky,

I have not done this from the point of view of MySQL database as 
either/or to Jet database.  But I have a couple of apps that link to 
MySQL tables *and* Jet tables, and transfer data one to the other via 
Update queries or Append queries etc.  The link to the MySQL database is 
via ODBC, so you need the appropriate MySQL ODBC driver installed, and 
the creation of a DSN (well, I think you can do it dsn-less as well).

So I imagine what you are trying would be reasonably easy.  If the user 
chooses the MySQL BE, then delete the linked Jet tables, and create the 
links to the MySQL tables.  Here is a sample of the code I have used to 
link to MySQL talbes:

     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 = "NameYouWantTheTableToHaveLocally"
     srcTblName = "NameOfTheMySQLTableToLinkTo"
     Conn = 
"ODBC;DATABASE=NameOfMySQLDatabase;DESCRIPTION=description;DSN=NameOfDSN;OPTION=0;PORT=3306;SERVER=NameOfServer;;TABLE=NameOfTheMySQLTableToLinkTo"
     Set tdf = dbs.CreateTableDef(tblName)
     tdf.SourceTableName = srcTblName
     tdf.Connect = Conn
     dbs.TableDefs.Append tdf
     dbs.TableDefs.Refresh

I guess one potential hiccup could be the use of non-corresponding data 
types.

Regards
Steve


Rocky Smolin at Beach Access Software wrote:
> Dear List:
> 
> I have a request to make my manufacturing package compatible with MySql.  I
> use some bound forms, and a lot of DAO.  The app is split FE/BE with linked
> tables pointing to the BE.  I also allow the user to select the BE they want
> so they can run multiple BEs if desired.
> 
> What would be involved in doing this?  Would it be enough of a rewrite that
> it would require a separate product?  Or can the current product be made
> 'switchable' between an Access BE and a MySql BE?



More information about the AccessD mailing list