Experiencing slow run times when Access linked via ODBC to MySQL table

[H L]

Our company has an Access database that we propose to move to a more robust platform. The initial investigation has involved running the Access code against tables linked ODBC to external databases.

Our first timings were running Access code against the internal Access tables. With the .MDB file on the network drive, our Task took 8min37sec. We moved the .MDB to the local hard drive, and the run time went down to 2min17sec.

Our second step was to move the data table (189,000 records and 94 fields) into a database on Microsoft SQL Server. We linked from Access to the table on SQL Server, and got run times of 2min08sec.

Our third step was to move the same table into a MySQL table. We linked from Access to the table in the MySQL database on the network drive, and ran out of patience before the run completed. Step 3B was to move that MySQL database to the local C: drive, and link to that. Our run time was 32min27sec.

Summed up; Moving Access .MDB to local drive made things faster, linking to MS SQL Server on network made things faster, but even MySQL on the local hard drive is really much slower.

I hope that I have done something wrong, perhaps I have some setting configured incorrectly. I have all the latest files, since I just downloaded them, and I accepted the default options during the installs.

Any advice that you can give to a newbie like me will be greatly appreciated.