Microsoft Access Database Bloating Main Causes

I recently released a YouTube video regarding the Compact and Repair command.

My objective with that video was to only summarily touch upon the command, how to run it and explain the 2 main benefits. I was aiming for a video for new users, and introduction to the subject.

I received a negative comment stating that I didn’t explain the causes of bloating …, I didn’t delve into the details of the issue enough. Thus, I thought that today I’d start trying to cover some of the more known root causes of database boating and will most probably create a follow-up in the future to discuss this further.

What is Database Bloating?

When we talk about bloating, at least in the case of Microsoft Access, we are talking about the file growing in size in an abnormal manner.

What is the more worrisome scenario is when the database grows in size with usage and thus grows every time the database is used and thus causes performance issues and can even lead to exceeding the maximum allowable file size (2GB) and lead to the database corrupting.

What Are The Primary Causes of Microsoft Access Database Bloating?

There are a number of potential causes. Below are a some of the more common causes.

Record Locking

Access does not have true row locking, but does have what is called database page locking. So in a roundabout way, if you turn on row locking, then Access just expands all records to the size of one database page – the result is massive bloating.Albert Kallal

So ensure you have Record-Level Locking disabled.

Options -> Client Settings -> Open databases by using record-level locking

and makes sure the checkbox is unchecked.

Insertion/Deletion of Records

Frequent Insertions/Deletion of records causes bloating, this also somewhat goes hand in hand with the use temp tables & make-tables operations. These should all be minimized and eliminated as much as possible!

When absolutely required, such operations/setups should all be done in a temp db linked to the main front-end thus the bloating is done in a disposable temp database which gets refreshed every time the database is launched.

Quick Tips

I would HIGHLY recommend that if you believe you need to use temp tables and/or make-tables in your operations that you take a moment to ask about your approach in a good forum as in my experience this is VERY seldom required and better approaches available which completely avoid any bloating altogether. Remember forums are a great resource to bounce ideas off of other developers and get some great advice!

Insertion/Deletion of Database Objects

Similarly to above, inserting and deleting database objects: tables, forms, reports, … leads to database file size growth. Thus, dynamically generating objects, copying/deleting objects in production is to be avoided at all cost.

I have worked on a number of databases over the years in which the previous developer used VBA to dynamically generate ‘throwaway’ reports. This was disastrous and caused substantial bloating.

Not Releasing Recordset Memory in VBA Code

To avoid consuming unnecessary resources and increasing database size, use the Close method of the Recordset object to explicitly close the recordset’s memory when you no longer need the recordset.Microsoft

So, improper VBA coding techniques can actually lead to database bloating. Taking shortcuts will cost you!

In plain English, be sure to close and free up any resource you open.

Non-QueryDef SQL

Whenever you code an SQL string outside of a querydef, Access must “bind” that statement EVERY time it is run. This process takes a small amount of time and uses a large amount of workspace that is not recovered until you compact the db. Querydef’s are “bound” when they are saved and when the db is compacted. Access saves its execution plan at that time and uses the saved plan at run time.Pat Hartman

Thus, creating in memory (VBA) queries, commonly referred to as non-QueryDef SQL cause bloating.

Sharing a Database Between Multiple Users

Beyond issues with file locking, corruption, … sharing a common front-end database file between multiple users also causes database bloating and is just one more reason why properly splitting and supplying local individual copies of the front-end to each user is so important.

Images

Improper use of images also leads to bloating. Whenever using images in your databases you should always optimize them as much as possible.

What do I mean?

Say you are inserting a logo into your reports, you should figure out the required size and then redimension the source image down to that size thus reducing the file size of the inserted image so as to minimize the bloating caused by the image.

I worked on a database recently which was at 1.7-1.8GB and by simply optimizing the images used in various forms and reports I brought the file size down to 85MB! So when I’m stating that images can cause database bloating I’m not exaggerating.

You may like to use the code found in the following article to quickly and easily find the images used within a database to ensure they are all optimized to minimize there impact or to switch techniques for displaying them in the database.

Access – VBA – Find Images In Forms and Reports

Experienced developers know that we always want to minimize the file size of a database so it loads faster. We always want to squeeze the most performance out of every element and also ensure we never near Microsoft Access’ 2GB file size limit. To that end, whenever I am asked to look over a database, one aspect I always review is the use of images. Novice developers often insert images without first resizing them for their use within the database. For instance, I’ve shrunk a db by over 250MB just by resizing 3 images! In that specific instance, I reduced…

Continue reading →

Image Storage Format

Related to images is the database Storage Format being used. To minimize the footprint used by images, be sure to set the Picture Property Storage Format to Preserve source image format (smaller file size) unless you absolutely require compatibility with older 2003 and prior applications.

Options -> Current Database -> Picture Property Storage Format (which in in the Application Options section)

Attachments

Just like images, using the Attachment data type leads to database bloat.

Anyone that follows this blog knows that I have long affirmed that attachments should be avoided and instead files/attachments should be stored on a central directory on a server and simply the path and filename stored in the database. This is the proper way of handling attachments!

You can learn more about the proper approach of storing files/attachments by reviewing:

Adding Attachments to an Access Database

When Access 2007 came out Microsoft introduced a new Attachment data type to allow users to insert attachments within their databases. I urge you to NEVER use Attachment data types for a variety of reasons, but mainly: Leads to database bloating Can impact performance You need code to interact and extract the attachments from the database Instead, the tried and tested approach of simply storing the file path and filename (with the extension) remains the preferred technique to use.

OLE Objects

OLE Objects should be avoided at all costs!

Let me state that again, OLE Objects should be avoided at all costs!

They are EVIL!

I’m not going to spend much time on this point, but inserting files as OLE Objects leads to substantial bloating.

If you want to learn about other potential issues that arise from the use of OLE Object then review:

OLE Object Data Type - Just Don't Ever Do It!

I know I’ve made posts in the past regarding not using attachments but in case it wasn’t clear in that post let me extend the advice to include ANY embedding of files within a database by ANY means. Thus, please, please, please, NEVER use the OLE Object data type to save files within a database! It always seems like a brilliant plan at the time, but I PROMISE you that it will nip you in the ass down the road!

Track name Autocorrect info

Now this was a new source of bloating to me, but I have seen multiple postings regarding the fact that having Track name Autocorrect info enabled caused bloating and thus the recommendation is to disable it.

In fact, disabling this option has long been a Best Practice, probably why I never encountered the issue in the first place.

Options -> Current Database -> Track name Autocorrect info

Automated Checking of Existing Settings

Below is the beginnings of a routine to check these settings quickly via VBA:

Sub CheckDBProperties4BloatingProperties()
    Dim vVal                  As Variant
    Dim sOutput               As String
    On Error Resume Next

    'Options -> Current Database -> Track Name AutoCorrect Info
    '**********************************
    'Application.GetOption("Track Name AutoCorrect Info") 'Use SetOptions to change it
    ' CurrentDb().Properties("Track Name AutoCorrect Info")
    vVal = Application.GetOption("Track Name AutoCorrect Info")
    sOutput = "'Track name AutoCorrect info' is: " & _
              IIf(vVal = 0, "Disabled", "Enabled") & _
              "    => " & IIf(vVal = 0, "OK", "Potential bloating issue")

    'Options -> Current Database -> Picture Property Storage Format
    '**********************************
    'Application.GetOption("Picture Property Storage Format") 'Use SetOptions to change it
    ' CurrentDb().Properties("Picture Property Storage Format")
    vVal = Application.GetOption("Picture Property Storage Format")
    sOutput = sOutput & vbCrLf & "'Picture Property Storage Format' is set to: " & _
              IIf(vVal = 0, "Preserve source image format (smaller file size)", "Convert all picture data to bitmaps (compatible with Access 2003 and earlier)") & _
              "    => " & IIf(vVal = 0, "OK", "Potential bloating issue")
              
    'Options -> Client Settings -> Open databases by using record-level locking
    '**********************************
    'Application.GetOption("Use Record Level Locking") does not work!  The name of the setting does not match the label in the form!!!!!
    Application.GetOption ("Use Row Level Locking")
    vVal = Application.GetOption("Use Row Level Locking")
    sOutput = sOutput & vbCrLf & "'Open databases by using record-level locking' is: " & _
              IIf(vVal = 0, "Disabled", "Enabled") & _
              "    => " & IIf(vVal = 0, "OK", "Potential bloating issue")

    Debug.Print sOutput
End Sub

when run it will output something like:

'Track name AutoCorrect info' is: Disabled    => OK
'Picture Property Storage Format' is set to: Preserve source image format (smaller file size)    => OK
'Open databases by using record-level locking' is: Enabled    => Potential bloating issue

So there you have it, some of the more common causes of database bloating. As you can see, most, if not all, can be minimized if not eliminated by proper design practices and coding techniques.

To combat bloating we use the Compact and Repair command. To learn more about it, refer to my article:

Microsoft Access Compact and Repair

What is Compact and Repair? What does it do exactly? Why do most people recommend it so much? Well, these are some of the question that I am going to try to answer in this article. So let’s take a closer look at Microsoft Access’ Compact and Repair command.

DEVelopers HUT