"ODBC connection failed", Access linked to MySQL

[lightinthedark]

Hi.
I'm setting up a system using MS Access 2003 as a front end to a MySQL database using linked tables. The tables would show all data when I first linked them, but after closing and re-openning Access, I was presented with "ODBC connection failed". Using the Linked table manager provided a fix until I next closed Access... clearly not ideal.
I wrote the following code to refresh the link with the intention of having it run automatically every time I started Access; I found that having run it once, the linked table behaves itself now (as of an hour ago). Not sure why this fixes the failed connection, or why the connection failed in the first place (any insight would be welcome), but I know it works now.

---------------------------------------
Sub Link_Refresh()

MsgBox ("Refreshing link")
' This procedure is to get around Access
' failing to keep it's connection
' to a MySQL database once closed
Dim curDb As Database
Dim tdfLinked As TableDef

' Create a reference to the linked table
Set curDb = CurrentDb
Set tdfLinked = curDb.TableDefs("yourtablename")

' Create the connection to the external DSN
tdfLinked.Connect = "ODBC;DATABASE=mysqldatabasename; _
DSN=mysqlDSNname"
tdfLinked.RefreshLink
MsgBox ("Link refreshed")

End Sub
---------------------------------------
I don't claim that this is the best way of doing this, just that it worked for me.

Hope this helps someone.

Hello
-Lightinthedark

 

[zemp]

This sounds like it might be better received in an MS Access forum. Try forum705 or use the 'Find a forum' search at the top of the page to serach for 'Access'.

BTW, welcome to Tek-Tips! When you have time read faq222-2244 on how to get the most from Tek-Tips.

zemp

 

[thesenator22]

I'm having the exact same problem. Just getting started with Access and MySQL. I'm trying to convert old Access backend to MySQL and still use Access as front end. Doing just 1 table for now, link it and it works. Shut down Access and reload, then I get the Failed ODBC error like you said. Make a new linked table, then the first one works again. Close Access and start again, but then ODBC connection fails. If I just close the DB but not Access, and then reload the DB I don't get the error.

If you figure anything out let me know.

Thanks.

 

[thesenator22]

Here's what I found:

http://forums.mysql.com/read.php?37,10223,10223#msg-10223

Solution appears to be use older version of MyODBC (3.51.9) and have no password.

Note: DBTools Manager Professional would not let me create a user without a password, but MySQL Control Center would.

-
Sen