Hi Jill
First and most obvious thing is to use a good data structure, with astute
indexing. Index the fields that are regularly used for finding or sorting
data (such as surnames). Your foreign key fields are already indexed if you
created a relationship with referential integrity.
For your forms, don't load the entire 600k records. You may be able to set
up the interface so it loads just the single record the user wants, by
setting the RecordSource property of the form. You can also use this
approach in place of filters.
When you do set criteria, let it use the indexes wherever possible.
For example, these can use an index:
Surname = "Smith"
SaleDate Between #1/1/2004# And #12/31/2004#
MyText Is Null OR MyText = ""
whereas these cannot:
Surname Like "*Smith*"
Year([SaleDate]) = 2004
Nz([MyText], "") = ""
Naturally you also want to limit the number records in any combo/listbox to
a few thousand at most. And don't load dozens of subforms that you don't
need.
For other general suggestions, see Tony Toews' article, the Performance FAQ
at:
http://www.granite.ab.ca/access/performancefaq.htm
There a many, very important suggestions in that article, including Name
AutoCorrect, Subdatasheets, etc.
Taking those approaches, you can end up with a very snappy database, where
no form takes longer than 2 seconds to load and most interface things feel
instantaneous. Naturally things like bulk updates or huge reports will take
longer.
