You can link MS Access tables to a MySQL database very easy, all done behind
the scenes with code.
You use a FileDSN which is configurable by hand in Notepad or using VBA or
doing it interactively via the Control Panel ODBC.
This assumes the UID/PWD are already setup on the MySQL side. Change the
Server to fit your setup. Obviously you must have MySQL ODBC installed.
' The contents of the .dsn file is just a text file
' must have the .dsn extension...
' [ODBC]
' DRIVER=MySQL ODBC 5.1 Driver
' UID = Winbatch
' PWD = WBTPassword
' OPTION=3
' Database = test
' SERVER = localhost
' TIMEOUT = 300
Private Sub LinkMySQLForums()
Dim constring As String
Dim db As Database, tdf As TableDef
constring = "ODBC;FileDSN=C:\Writing\Winbatch\MYSQL\VBS-MySQL.dsn;"
Set db = CurrentDb
'Create a new table def, named as per the target table
Set tdfLink = db.CreateTableDef("MYSQLForums")
'Identify the target table for the link
tdfLink.SourceTableName = "mysqlforums"
'Identify its path
tdfLink.Connect = "ODBC;FileDSN=P:\VBS\VBS-MySQL.dsn;"
db.TableDefs.Append tdfLink
Set tdfLink = Nothing
Set db = Nothing
End Sub
That's all it takes.
More MySQL ODBC options at
http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html
>
Thanks, Jay
Edited 1 time(s). Last edit at 03/29/2009 07:45AM by Jay Alverson.