Access FE Very Slow

[Paul Strauss]

I am using an Access 2000 Front End, via ODBC, and the simplest queries run very slow. For example, it takes a second or two to open a bound combo box, where I am only projecting two columns from the the table. The RowSource is a Pass-Through Query. The table has a PK, which is one of the projected columns, and the other is a text column, also indexed. The combo box is used to select a row to display in the bound form that it is on. When I select a row from the combo box, it takes about 2 seconds to retrieve exactly one row:

Select * From Table Where ID = " & Me!cbo.column(1).

The table is MyISAM and has about 50 rows. It was InnoDB, and that seemed to be even slower.

I have "Don't Optimize Column Widths", "Return Matching Rows", and "Change BIGINT to INT" all checked in the DSN. I am using 4.0.18 of MySQL and Version 3.51 of the ODBC driver. Both the Access application and MySQL are installed on my desktop and I am running XP Pro.

I have spent somewhere upwards of 16 hours on this problem and I haven't been able to find anything to try that makes much of a difference. I really would like to use MySQL for my backend instead of SQL Server (and I really, really don't want to do that), but if I can't find a way to get acceptable performance, I'll have to move.

I thought of using unbound forms, but that is not going to solve the problem of the combo box being so slow. And if it takes so long just to retrieve one row, I don't think an unbound form is going to be any faster.

Is there anything else I can try or should do? Does anyone have any experience using ADO with MySQL?

Thanks,
Paul Strauss