Others

Fix Microsoft Access Database Bloat by Checking Record-Level Locking

Aamir Naveed AkramAamir Naveed Akram Nov 13, 2026 868 views

Question details

The user needs to identify and reduce rapid file growth in a split Microsoft Access database.

Fix Microsoft Access Database Bloat by Checking Record-Level Locking
Product
Microsoft Access
Device & OS
not provided
Scenario
Several users add records through a split database during the workday.
Observed behavior
The back-end file grows from approximately 200 MB to 700 MB or more in one day, and compacting it temporarily reduces its size.
Before you start

Identify every workstation that opens the database and arrange a maintenance window when all users can close Access. Note the back-end file size before and after a representative workday so you can compare the results of changes.

Solution 1Recommended

Disable record-level locking on each Access workstation

Test this per-workstation setting first to see whether it contributes to the rapid growth.

This is a troubleshooting step, not a guaranteed fix. Disabling the option changes how locking is handled and may increase contention between users, so test the database's multiuser behavior afterward. This option is separate from form and table locking settings.

1
Open client settings

On a workstation that uses the database, open Access and select File > Options > Client Settings.

2
Change the locking option

Under Advanced, clear the checkbox labeled Open databases by using record-level locking, and select OK.

3
Apply the change everywhere

Repeat the setting change on every workstation that opens the database, then close and restart Access on each one.

4
Compare file growth

Have users resume their normal work and check the back-end file size in File Explorer at the end of the day. Compare it with the size recorded before the change.

Disable record-level locking on each Access workstation
Check for connection conflicts: Review code that opens the back end directly, including DAO OpenDatabase calls, because connection options may affect locking independently of the client setting.
Free Microsoft Office alternative

Use WPS Office for Documents and Spreadsheets Alongside Access

WPS Office is a free, lightweight alternative for everyday documents, spreadsheets, and presentations. Its familiar interface and Microsoft Office format support make it easy to move compatible files between suites. It does not repair or replace a Microsoft Access database.

Track database file sizes and troubleshooting results in a spreadsheetOpen and edit Microsoft Word, Excel, and PowerPoint formats, including DOCX, XLSX, and PPTXUse familiar editing tools for a smoother transition from Microsoft OfficeWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Where is the record-level locking setting in Access?

Open File > Options > Client Settings and look under Advanced for Open databases by using record-level locking. Change it on each workstation, then restart Access.

Will disabling record-level locking always stop database bloat?

No. It is one troubleshooting step. If growth continues, inspect temporary tables, repeated inserts and deletes, failed append queries, embedded files, and unclosed DAO recordsets or connections.

Why does the back-end file grow again after compacting?

Compact and Repair reclaims unused space, but it does not necessarily remove the cause of growth. Check the application's queries, temporary data, and code to identify operations that repeatedly create or delete data.

Can I compact the back end while other users are working?

Arrange for all users to close the database first. Open the back-end file with exclusive access, then select Database Tools > Compact and Repair Database.

Does WPS Office fix Access database bloat?

No. Use Microsoft Access to troubleshoot and compact the database. WPS Spreadsheets can help you record file sizes and track the results of your checks.