Linked table via ODBC to MySQL

[Guest]

Hi

I have a MySQL database stored on a webserver I use. The database collects
customer details once they are input through my website.

I have linked a table in Access 2002 to this database and it displays all of
the information in the online table. Unfortunately there are two problems.

1. I cannot seem to update/delete any of the data in the table
2. There is a speed issue here as well. Tables is reasonably small 10k lines
with 12-15 fields but queries on the table take a while to execute.

I have the following drivers installed on my machine but don't know which
one i am using to connect
1. MySQL 2.50.39.00
2. MySQL ODBC 3.51 Driver

Could someone give me any advise on how I might be able to solve my problems?

TIA

 

[Guest]

A few thngs:

Can you find out which version of MySQL you have running? Hopefully, it's
4.1 or higher.

Next, you need to check your ODBC configuration (Control
Panel->Administrative Tools->Data Sources (ODBC) to see what connection your
Access DB is using to connect to the MySQL database. You should use the
latest version of the drivers that are appropriate for your MySQL version.
Most likely, the driver should be 3.51.

When you find the DSN you are using to link the table in the "ODBC Data
Source Administrator" window, click "Configure" and check what user is being
used to make the connection. Then check your MySQL server to make sure that
user has update privileges on that table. If the user has connections
limited to certain hosts, make sure the IP of the PC with the Access DB is
allowing that user update permission. If all that is setup correctly, you
should be fine.

As for the speed issue, have you ruled out a network problem? If you can,
try running the Access DB from the server itself. If that works fine, then
your issue is most likely network related. Other than that, just make sure
you have the latest version of the drivers.

Hope this helps.

-Todd

 

[Ron Hinds]

My understanding is that older MyODBC drivers *must* be removed before
installing a newer version. I would suggest uninstalling both versions then
reinstalling only the 3.51 driver. I think that this is your speed issue.
Now for the inability to update: Access won't allow you to make updates to
linked tables unless there is a field (or fields) that Access can use as a
Primary Key. When you link the table, if there is no Primary Key defined,
Access will open a dialog box asking you to select the field(s) that are
unique. If you don't select any, that table will always remain "read-only".