"Index all fields used in searching."
I have built indexes matching the fields in the where clause. You think its better to just index the individual fields and let Jet use multiple indexes?
I can't tell - you certainly could try that
"If possible break down searches using cascading combo boxes to limit the search 'pool' each time"
This is an automated process. There is no user involved.
OK but some method of breaking down the search to reduce the number of records being searched
"If using SQL, use DB.Execute rather than DoCmd.RunSQL"
Yes, I use Execute for the action queries.
"Use the JET ShowPlan to optimise your queries"
Didn't know about that, thanks.
See this link http://www.mendipdatasystems.co.uk/show-plan-go-faster/4594460516 and also read the linked article by Susan Haskins
"Consider moving the large BE to SQL Server so you can just filter the data you need at the server rather than grabbing it all then filtering in Access"
I don't a separate server to put SQL Server on. I could run it on the same PC/processor as Access but am not sure that would help.
I meant use a SQL Server BE database - you can host that on the same PC oor on a network server
it won't immediately transform the time needed but it is designed for large scale data & has many features that will allow more efficient retrieval of required data
Thanks for the post.