MS Access with Mysql link table connection failed

[pelhughes]

CF error:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] ODBC--connection to 'ProdTracking' failed.

In our MS access table we have a "link table" to the MySql database on a Linux
box.
We have tested the MySql server by using MS access through MYODBC link table
to get to the database on the MySql
server and it works.
We have tested the MySql server by using a SQL query to the MySql database and
it works.

The middleware we are using is: MyODBC 3.5.1.11

We have installed the HotFix SequeLink driver 5.4

The app was working on Friday and the only thing that was done to the server
was MS Critical updates. On Monday, the app
refused to work giving the error above.

Part of the same MS access database uses links to a FoxPro database and this
part of the app works. Another part CF app accesses the same table that was
linked inside the access database but is being accessed directly through CF
MySql
driver and this part of the app works as well.

The only part of the app that does not work is where the CF app is trying to
access the table via a link table inside of the MS
access database.

Thanks in advance for any assistance

[OldCFer]

I have never been able to do this. I'm using Access to link to MSSQL, and I
tried
just out of curiosity but didn't follow up much. I figured it was a security
issue because
you're allowing a non secured DB (Access) to connect to secured DB (MSSQL),
which
was fine as long as it was thru Access itself. To allow another ODBC
connection to
connect to Access and thus linked tables seemed a security breach to me. Maybe
the
critical update did something. Out of curiosity, why are you connecting to a
linked
MYSQL table via Access instead of connect directly to the MySQL DB? It must be
really slow.

[pelhughes]

>> Out of curiosity, why are you connecting to a linked MYSQL table via Access
instead of connect directly to the MySQL DB? It must be really slow. <<

Larry,
The reason why we've done this was because we have already developed an
application in MS Access, and have many stored query in the DB. Linking the
tables stored in our MySQL DB was a quick solution at that time. Yes, this is
slow for our application, but was our quickest solution at that time. It worked
for over a year.

I just found out that the MS Critical updates was installed after the
connection failure, so that voids our original assumption that the updates
might have caused this failure.

We have fought this for almost 3 days already, and have no choice but to
migrate the MS Access DB to MySQL.

We still like to find a fix for ithe ODBC connection failure.

If anyone has any suggestions, we?ll greatly appreciate it.
Thank you

[Unregistered]

Probably your MYSQL database does not allow remote connections. Enable that on the linux box and you will be fine. Use %'s if you have dynamic IP addresses. You can also just use % to allow a connection from ANY IP address. Not as secure and not recommended.

10.10.2.%
or
192.168.%.%
or
%

Your IT department will know what I mean. There is a specific command you can run to allow remote connections for an already existing user.

Here are a couple of scripts you can run from the linux command prompt after you have loaded the MySQL database:

(Connect to the database from the command prompt)
$ mysql -u [username] -p [password]

To setup on a database level:
mysql> update db set Host='202.54.10.20' where Db='webdb';

Or to setup on a user level:
mysql> update user set Host='202.54.10.20' where user='webadmin';