Visual Basic Programmer

Microsoft Access Database Programmer

Optimising Microsoft Access Database systems

The number of simultaneous users that an Access File-Share system (i.e. having an Access Front-End and an Access Back-End Database) can support varies from 5 to 30. 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 Client

Their are literally thousands of tips for Client optimisations that can improve performance. For a detailed analysis of your Access database, run the Total Access Analyzer software by FMS.

Optimising the Network

• 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 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 Disc Maintenance 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.
• 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 required.
• 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.