Optimise Microsoft Access Database systems
The number of simultaneous users that an application with an Access Front-End and an Access Back-End Database can support varies from 5 to 100. The big difference in the number of users that can be supported relates to how well the Access databases have been optimised.
Increasing the number of users that can get reasonable performance involves reducing the Client and Server activity, and the consequent Network traffic.
Here are some tips that will help extend the life span of your system.
Optimising the Access Database Client
There are literally thousands of issues relating to Client optimisations that can improve performance. The most common problem is unreferenced fields, macros, tables, etc. This is the result of object name changes or deletions. For comprehensive checking, an Access database analysis software product is needed.
Here are some general hints to improve the quality of an Access Database system:
- Add error handling routines to each and every procedure.
- Create Re-usable modules for frequently repeated code
- Simplify convoluted and spaghetti code
- Gradually replace system functionality with the VB.Net technology
- Use refactoring of the code (See Why have Visual Basic Coding Standards?) to extend the system life span. This will improve the readability of the code, remove dead code, make the code easier to comprehend and maintain.
Optimising the Network Traffic
- Most network adapters support the 10/100 Mbps standard. Check that the access rate across the network is 100 Mbps and not 10 Mbps. For the Network speed, view the Local Area Connection Status.
- Compared to the cost of a software upgrade, a move to a Gigabit Ethernet device (1000 Mbps) should be cost effective.
- Limit the number of fields returned by any query. Do not use "SELECT *" – rather specify exactly the fields required.
- Limit the number of records returned for lengthy transactions by being more selective.
- Network Traffic will be minimised by tuning the Client and the Server databases.
Optimising the Access Database Server
- Compact the Back-End database frequently. This will shrink the database and make retrieval faster. Note that it can only be done when all users are off the system.
- Defragment the disks on the Server. This increases the available contiguous free disk space, and makes record insertions faster. See the page on Improving Disk I/O Performance for further tips on PC Tuning.
- Index all fields that are used for sorting.
- Make sure that all Unicode strings are compressed.
- Use ActiveX Data Objects (ADO) for database record retrieval.
- If VB.Net is used for the Client, ADO.Net can be used to minimise database connections.
- Remove all Objects and Images from the Back-End database, and replace the fields with a reference to Windows files on the Server.
- Every table in the database should have a numeric primary key.
- Set the Table Subdatasheet property to "None", if not needed.
- Choose the smallest data type and size possible for each field.
- Avoid indexing fields that are not used in a WHERE selection.
- Do not add indexes to fields that contain much of the same data (like Yes/No binary fields).
- Save the SQL in the Record Source of Forms and Reports as Queries.