Here’s a Massive Question to Answer:

This always makes me think of a quote from Access MVP Alumni John W. Vinson

Black art indeed!  The simple fact of the matter is there is no one answer that solves the issue for every database and will miraculously speed up your database.  The reality is that there are a multitude of potential aspects that can be perfected, improved upon, tweaked, each contributing to improving the overall performance.  There are db admins that spend their careers only doing optimization.

Where To Start

Today, I will cover those aspects that I have found can have the biggest impacts on performance improvements, mainly:

• Wired LAN!
• Persistent Connection(s)
• Indexing
• Record Source, Row Source, …
• Complex Forms
• Domain Functions
• Dynamic Forms
• Antivirus

Let us briefly touch upon each.

Please note, that I am purposely omitting proper database design in the present discussion.  For me, proper database design is the most critical aspect of database performance, no doubt, but it is way out of the scope of any optimization article and is a subject onto itself.
 

Wired LAN!

When I start working for a new client, the very first thing I review is how their network is setup.  Access is an absolute network hog, this cannot be over stated.  As such, it is critical that everyone using an Access database be connected to a LAN via wires (Ethernet cabling).

The use of WANs, OneDrive, Google Drive, DropBox, … are all big No-Nos.

If you are stuck in a situation where you have no choice but to run over a WAN, wireless network, … then review my article

Beyond that, you want to evaluate the performance of the Network as a whole. Is it relatively fast and stable? Does that fluctuate throughout the day? If you have a slow or unstable network, then that needs to be addressed first and foremost.
 

Persistent Connection(s)

Once I know the database is running on a properly setup wired LAN, the second thing I immediately review is whether or not the database has been setup to automatically establish a persistent connection at the startup.  This element alone can have a drastic impact on the speed of your database depending on your network.

You can learn more about persistent connections in my article:

Indexing

Another critical aspect of performance is proper indexing of table fields.

Case and point, I once had a database that would take a query over 10 minutes to complete and I just couldn’t understand why.  I added a single index to one of the tables and the query would basically return the results instantaneously.

So the question then becomes, what do you index then?

As a general rule of thumb, you want to ensure PK fields, fields used to related tables (build relationships upon), fields that are used in WHERE or HAVING or involved in JOIN clauses, fields used in object Filter properties are all indexed.

Don’t forget about those fields you filter on in VBA (think OpenRecordset for instance)! Also think about the fields you use to restrict (Filter/Where) when opening objects (Forms/Reports) using the DoCmd.OpenForm/DoCmd.OpenReport methods. So on and so forth.

So why not index every field?

Well, indexes come at a price, (i) they take up space, (ii) they slow down Insert, Update statements/processes as indexes need to be adjusted/rebuilt.

Indexing is truly an art and sometimes it can take experimentation to get just right.  So don’t be afraid to experiment around a bit and compare the performance of indexing or not indexing fields.  You’ll be amazed by the speed you can gain from taking some time to analyze your database queries, filters and where clauses and properly indexing your tables.
 

Record Source, Row Source, …

Always take the time to review object Record Sources, control Row Sources, and the likes. A lot of people simply use a table, thus pulling all the fields. You should always build a proper SQL Statement and restrict the fields to only those you truly need. By doing so, you reduce the amount of data being pushed/pulled back and forth by your forms/reports/control. The less data that needs to be juggled by Access, the faster it will behave.

So don’t blindly bind Forms/Reports to tables, or queries (unless already optimized). Don’t use SELECT * SQL statements. Take a moment to build Record Sources, Row Sources that only retrieve the specific fields you actually need.
 

Complex Forms

All to often I take over projects where the developers has literally put the content of every table in a single form.    There is no point in making Access retrieve all the data from all the tables in this manner.  This is pure madness and users never need or use all that information.

This may seem self evident, but instead of using a massive all inclusive form, use a simpler form with pop-up forms as required.

Another option is to create customized forms for different user roles so each role see just what they need and remove all the useless data.  By simplifying form and subforms, simplifying the underlying recordsources, you might be amazed at the performance gains that can be accomplished.
 

Domain Functions

Another thing I’m always on the lookout for when reviewing a database is the use of Domain Functions like DLookUp, DCount, DMax, …  These can have a truly detrimental impact on overall performance if over used.  It is fine to have a couple, but I’ve seen case where there were 25+ calls to retrieve all sorts of information via DLookUp calls and the form took 10+seconds to load, same when switching records.  By simply adding the necessary tables to the recordsource or by creating a single call in the form’s open event and getting all the values from a single VBA query, I was able to eliminate the delay.

So if you use a lot of these functions, look for better approaches, or use a delayed technique to get the information to not slow down the form/report loading.
 

Dynamic Forms

Another very powerful improvement you can make, especially on those heavily loaded forms, is to employ Dynamic Loading of various controls.

A prime example of this would be a form with a tab control with multiple pages each housing multiple subforms.  The reality is that there is simply not reason to load the tab content by default.  Instead, using VBA, only load the content of each page if, and when, a user actually clicks on it to view it.

The same can be true of controls such as combo boxes.  Why make the database read lookup tables unless the user actually clicks on the control?

This idea was first introduced to me by the following article by Danny Lesandrini.

Antivirus

One last item I will mention in passing is that I have seen some over zealous virus scanners that caused detrimental performance effects on Access databases in the past.

What I’m suggesting here is to perform a quick test by temporarily disabling your antivirus for a couple minutes and try out your database to see if this speeds things up, especially the initial loading of the database. Then immediately restart your antivirus.

If you did notice an improvement when the antivirus was disabled, then you may wish to add an exception to your antivirus software for your database file.

Whatever you do, do NOT permanently turn off your antivirus!
 

Taking Things Even Further

So the aspects discussed above are where I always initially set my attention, but there are many, many more aspects that can impact performance.

I long ago came across a great article on the subject.  If you’ve already covered the above optimizations, then perhaps you’d like to look at other areas that it discusses.  Don’t be worried about the fact that ‘2007’ is mentioned in the title or article content as it applies to any version of Access (nothing has changed in decades!).