CODEWITHSUNDEEP
ms-access
mpona
mpona

Reputation: 31

How to avoid Access database bloat?

I'm programming an Access database but I realized that its size increases dramatically as it is being used, growing to hundreds of MB. After compacting it the size came back to 5MB. What normally cause this increase of size and how I can avoid it?

Upvotes: 3

Views: 3862

Answers (5)

John Coogan
John Coogan

Reputation: 1

Here's another possibility that took me forever to discover. I am using Microsoft 365 subscription, ver 2302. Check menu: "File, Options, Current Database". Look for "Track name Autocorrect info" and uncheck it.

I >think< the idea behind this option is that Access will auto correct other instances of objects behind the scenes when you change an object name. Example: You have two queries that are linked and you change the name of one, then Access will automagically update the name change in the separate join query, preventing it from breaking. Pretty cool, but also possibly pretty expensive in terms of database bloat because Access has to write all these object references somewhere!

In my case I have a database with code only, data tables are in a separate linked database. The code database was bloating from 3000k to the 2gig limit in 20 min! My code database is doing a lot of database object and recordset object creation and deletion. When I unchecked the option named "Track name Autocorrect info" it resolved the issue and now the code database only grows by about 500k.

Upvotes: 0

Albert D. Kallal
Albert D. Kallal

Reputation: 48954

You also can turn off row locking. I have a process and a file of about 5 Megs in size. When you run a simple update, it bloats to a 125 Megs. If you turn off row locking, then the file does not grow at all with the update. So you want to disable row locking – this will MASSIVE reduce bloating. The option you want to un-check is this one: File->options->client settings, and then uncheck

[x] Open database by using record-level locking

enter image description here

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. Try disabling the above option. (You have to exit + re-start Access for this setting change to take effect).

Upvotes: 3

Conrad Frix
Conrad Frix

Reputation: 52645

If you're really going from 5MB to hundreds of MB that can be compacted back to 5 MB then as others have mentioned you're INSERTING and DELETING a lot of records. This is usually because you need to create temporary tables.

Most of time temporary tables aren't technically required and can be remove them by either querying a query or using dynamic SQL. If you can't do this, its probably worth while to create a separate temporary database that you link to.

Its important to note that each user have their own copy of the temp database and that it gets destroyed at either the beginning or the end of their session.

Upvotes: 2

wickedone
wickedone

Reputation: 540

Doing any work in an Access database will cause the size of the file to increase. I have several databases that bloat to almost 2GB in size when a morning process is running. This process inserts, updates and deletes data.

One thing that is important when working with MS Access is to use compact and repair. This will shrink the size of the database.

I wouldn't worry about the DB growing to a couple of hundred of MBs, that is still small for Access.

Upvotes: 1

phoog
phoog

Reputation: 43036

Lots of adding and deleting records is one cause of database bloat. If this is your development db, then database bloat is unavoidable as you repeatedly compile and save your vba project; the bloat may be far less pronounced in end-user databases.

Upvotes: 1

Related Questions