I am using A97 to create an application which will involve a primary table of over 400,000 records, in which several fields are resolved through a variety of secondary lookup tables. Essentially, it is a browser application, and I would like to be able to read these primary tables from a CD.

In order to perform a variety of sorts on various fields or combinations of fields, I have created a number of indexes. Then, if I open the table directly, using the sort functions is extremely fast, as expected.

Next I created a "continuous forms" form which access this data, and used the various lookup tables to resolve data in the relevant fields. I would like to provide a "sort" button to allow the data to be sorted according to the various indexes I have created.

If I do not use any joined tables, Access seems to use the indexes, but if I base the form on a query which joins the lookup tables, the indexes do not seem to be available or used when I change the sort order, and so of course performance suffers. I have tried to resolve this by basing the form on the 'raw' table, and deferring resolution of the lookups to the form itself. However, this approach results in a delay in repainting the form as the lookups are resolved. When the source data is on a CD, the lookups seem to fail altogether.

I would be grateful for any suggestions about how to provide fast sorting for queries based on large tables like this.