Connecting to MySQL at Startup

[WPW07]

Hello,

I've installed the ODBC connector for MySQL and can go into Access and
manually link to the MySQL tables. Everything works fine this way.

But rather than go into the Windows Data Source Adminstrator on each
user's computer, how would you code an MS Access front end to connect
to MySQL tables at startup? Where would you put the code and how
would you call it when the Access database is opened?

Thanks for your help!

 

[Albert D. Kallal]

It will be assumed that your database gets installed on each pc.

I explain why here:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

Then, you can re-link with what is called a dns-less connection, and not
have to open/use the data source administer.

The following shows how:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

Using DSN-Less Connections
http://www.accessmvp.com/djsteele/DSNLessLinks.html


ODBC DSN-Less Connection Tutorial Part I
http://www.amazecreations.com/datafast/GetFile.aspx?file=ODBCTutor01.htm&Article=true
HOWTO: Use "DSN-Less" ODBC Connections with RDO and DAO
http://support.microsoft.com/?id=147875
ODBC DSN Less
http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm

 

[WPW07]

Thank you Albert for the excellent information. Just one question.
I'm looking at the tutorial part I. I'm still unclear where you put
the code that calls the connection function. Do I need to put it in
every form/report that links to the MySQL tables? Or can I set it up
so the application calls the function once when started? If so, where
would the code go? This is the code in the tutorial I'm referring to:

Function LinkToPubsAuthors()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb()
Set tdf = db.CreateTableDef("Authors")
tdf.SourceTableName = "Authors"

tdf.Connect = "ODBC;FILEDSN=C:\Desktop\Pubs.dsn"

db.TableDefs.Append tdf
db.TableDefs.Refresh

Set tdf = Nothing
Set db = Nothing

End Function


Thanks again...

 

[Douglas J. Steele]

You'd run the code once when the database opens. You can have the Autoexec
macro call your function, or you can put it in the Open event of the initial
form.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you Albert for the excellent information. Just one question.
I'm looking at the tutorial part I. I'm still unclear where you put
the code that calls the connection function. Do I need to put it in
every form/report that links to the MySQL tables? Or can I set it up
so the application calls the function once when started? If so, where
would the code go? This is the code in the tutorial I'm referring to:

Function LinkToPubsAuthors()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb()
Set tdf = db.CreateTableDef("Authors")
tdf.SourceTableName = "Authors"

tdf.Connect = "ODBC;FILEDSN=C:\Desktop\Pubs.dsn"

db.TableDefs.Append tdf
db.TableDefs.Refresh

Set tdf = Nothing
Set db = Nothing

End Function


Thanks again...

 

[WPW07]

Hello,

Can anyone point me to some simple examples of an ODBC connection
string function? I just want a simple function that will connect to
my MySQL database. I don't need it to create a table, etc. I just
want a connection. I will put it in a module and call it from the
AutoExec macro.

Thanks!