ODBC Call failed error using Access 2002 and MySQL
Posted on 2009-12-25
I have set up a development environment where I can successfully connect to a MySQL database on a networked 2003 Server from my XP sp3 laptop with my Access 2002 front end app using MySQL ODBC 5.1.
When I go to duplicate the installation at a client site, I can get a successfull connection test when I am configuring MySQL ODBC 5.1 between the user and the MySQL server computer, but when I go to run my app I get "ODBC Call Failed" as my linked tables can't find the MySQL database. (DATABASE = "leanmachinedatamysql") file DNS.
When I try to refresh the links, the Linked Table Manager comes up blank, with no list of any of the linked tables. MySQL is installed successfully on the client server, as I can restore a back up MySQL database using Navicat, and then use a migration tool I built that uses ODBC to import data from a MS Access database. I think I don't understand something about how DNS connections work in order to create the connection settings that will work when installed on the client network. If I call the file connection LeanMachineDataMySQL on my system and set up the same file DNS name on their network, does the fact that the respective servers have different IP addresses break the connection? What's the best way to connect my app to the MySQL database when installed on the client's network?