|
Home
Contact
Clients
Downloads
Examples
Search
Links
About
|
|
Access is a multi-user desktop or file server database. The number of
concurrent users that it can handle will depend upon the type of user and
the design of the data structure and the way the data is being
manipulated. Generally speaking, for changes to data, about 20 users is
considered to be the limit. For viewing of data this figure could be
increased considerably.
Choose Optimistic Locking to make the database more multi-user.
Pessimistic Locking guarantees that your updates will take place, but
excludes other users from using the system. Generally pessimistic locking
should be avoided other than under special circumstances, e.g.
administration and maintenance. The type of locking should also be
specified in any code that attempts to change data.
The database should be split into a data file back-end on a shared
network and a front-end installed on each user's machine. A runtime
installation on each user's machine does not require a license. The
front-end contains everything except the main data tables.
Bound forms can cause problems. If a user starts to edit a record and then
goes for a coffee. the record and neighbouring records will be locked until
the changes are either accepted or abandoned.
Any code that attempts to change data should have an error handler that
checks for locking problems. If the records are locked, the code should
attempt the operation a pre-determined number times with a tiny random
delay between each attempt. If after this the operation still fails, the code
needs to take the failure into account.
The front-end can be made to check the number of users accessing the
database, and if the figure is greater than the figure that you have set
as a maximum, inform the user that the system is busy and to try again
later. This way the system can be available to a considerable number of
users but still only allow about 20 at any one time. The figure of 20 can
be varied to suit the circumstances, or different types of users can be
excluded from the figure so that readers can still gain access.
Corruptions can occur, and for this reason, backups are important, but
they should be very infrequent. Not daily, weekly or even monthly
occurrences. If they are, something is wrong in the design of the system
or there are network problems. Compact the tables regularly, this will
speed up the system as well helping to avoid corruptions. Most corruptions to data can be repaired
OK. Corruptions are also possible in forms and reports, or their VB
modules. This normally only ever happens in the development stage and can
be trickier to put right, though often it is possible to repair the
offending item. This is another good reason for splitting the system into
front and back-ends. If the front-end needs to be restored from a back up,
there is no danger of losing any data.
Access databases can be upsized to SQL Server or Oracle if the the
number of users really has outgrown the original system. But a poorly
designed Access database will translate to poorly designed SQL database if
the problems are not addressed. As of Access 2000, SQL server tables can
be used and managed within Access in place Access' own tables.
|
|
- Use a split front-end back-end system
- Use Optimistic Locking as much as possible
- Avoid bound forms where intensive updating is likely
- Allow for locking in any code that attempts to change data
- If possible, count the number of logged on users and apply a
restriction
- Make sure that back-ups are taken frequently
- Compact the tables frequently
- Consider upsizing to a client server database
|
