Using MS access as front to MySQL and using MySQL indexes

[carl R]

I have a Access 2007 application that is composed of two parts. One part is the forms and the other is the data. I moved all the data to MySQL, created the various indexes both primary and secondary. No real problems.

The data is linked to the program/form part. i.e. the MS Access form is linked to MS access data or I relink the forms to MySQL data via ODBC using the MySQL ODBC driver.

In general all works EXCEPT for searches.

The program part is loaded with forms that fetch information based on using the "criteria" filter when opening a form. When I use this criteria with the MS access data, the indexes are used to find the data and read it. However, when the form is linked to MySQL, access forms a general select query based on the criteria, but it doesn't use the indexes. So it takes forever to retrieve the data.

Even worse if when a user uses the MS Access "find" to locate a specific item. Instead of access formulating a query to fetch the data using the indexes, it reads all the rows, testing each row for a match. Sometimes this can take a very long time. Whereas if I use the MySQL query browser and compose a select query for the same data, it completes almost instantly. So I know the problem is the indexes are not being used.

My question is this. How does I configure/setup the MySQL connection so the MS access queries via ODBC from the form USE the indexes.