Each instance of your database application uses some number of connections and data page (or row) locks on the server, and creates a measurable load on the network. Since each additional user contends for many of the same resources, the number of users the system can support is directly proportional to the number of resources each instance of your application requires.
To reduce the number of locks, users should not be permitted to “sit” on unpopulated Recordset objects. The application should populate the recordset as quickly as possible using DAO, the Data control, or one of the background population techniques.
Your design should also include management of user logon IDs and passwords. If your design uses a shared Jet (.mdb) database, you must also address Jet security systems.
Because all users must disconnect from Jet databases (that contain data) for periodic maintenance, you should include a way to notify users to disconnect from the shared Jet database or provide a way to signal applications to disconnect automatically on their own. If the maintenance operations are executed during nonpeak hours, and applications automatically disconnect from the Jet database after a length of idle time, maintenance programs can execute without disturbing uncompleted result sets or pending updates.
For More Information See “Managing DAO ODBC Connections With Jet” later in this chapter.