I have a split MS Access database, with the back-end on a shared network drive and the front-end on each user's PC (about 15 users). Our network is pretty slow, and when loading records to edit it can take up to 60 seconds to load, which seems to get slower the more users are connected to the back-end.
All that is used are forms which use queries as their data source to limit fields, but the query links 3 tables (all via a shared primary key, which is an auto-number on the main table) so that it's editable. Testing seemed to make this faster and less error-prone than using sub-forms. Also I need to filter the query based on fields from 2 of the 3 tables.
The slow-speed is only when querying the data-set, but since it's constantly being edited, I need to re-query every time when moving between records. This is because the query filters to records that have been unassigned, and every time a record is opened, it is assigned to that user.
My question is are there any suggestions for speeding this up? It may be simply that having so many users is causing the slowdown, or that my query is inefficient. I can't post the code (very security conscious company) but am happy to give more details.
I've tried a few suggestions found online, such as keeping a database connection open, but this didn't make any noticeable difference, and running a query on fewer fields (only those necessary to filter) to find the next record, and then just open that record (using a where statement) using the main query, but this seemed even slower. I've also read that if you use fields in your where clause that are not indexed the whole data-set is passed to the client, which then filters it, so I've thought of indexing every field in the where, but haven't tried this yet.
Apologies if I haven't given enough details.