We are creating a database that will have 16 people inputting to it at once. The trouble is that in the past when a number of users have been inputting at once we get problems with records locking.
We are going to split the database so that the tables are on the server and each user has their own forms to enter on, however speed is a big priority and the database may be holding up to 500,000 records.
Can anyone advise of the best way of going about building this to revent locking tables and maintain speed.
Actually, on bound forms you may set RecordLocks property to No Locks. Below I pasted some info from MS Access help:
No Locks 0 (Default) In forms, two or more users can edit the same record simultaneously. This is also called "optimistic" locking. If two users attempt to save changes to the same record, Microsoft Access displays a message to the user who tries to save the record second. This user can then discard the record, copy the record to the Clipboard, or replace the changes made by the other user. This setting is typically used on read-only forms or in single-user databases. It is also used in multiuser databases to permit more than one user to be able to make changes to the same record at the same time.
In reports, records aren't locked while the report is previewed or printed.
In queries, records aren't locked while the query is run.