Why does BE size grow so much and how to compact without using Compact and Repair feature?

Question

Hello everyone,

I've got an application which is split into FE and BE. The BE has only the data - there are tables and nothing else: all my temp. tables, queries, forms, reports and anything else I might need is in my FE. Furthermore, the way the users are using this, there are pretty much no row deletions; pretty much all I get are new rows and row edits.

However, the BE grows and grows and when, every couple of months, I check it out and do a compact and repair, the BE will go down to about 60% its original size! I don't understand why, could someone explain where all these extra MB are coming from?

Secondly, if this extra 'data' is normal, I will need to add an automatic feature to compact the database regularly. I have been reading about Compact and Repair and I've seen most people don't recommend it beyond app release. What's more, because of some particularities of the application, whenever I do Compact and Repair it messes up my IDs, so I'd rather not do it... So my second question is: is there any other way to compact the database that will not interfere with autonumbers?

Thank you!

Silvia

Answer 1

There is no other way to perform a compact.  I'm struggling to understand why a compact would throw off your number?

As for growth, the only thing I can suggest is testing to identify what is causing your bloating.  Are you using attachments within your database?  

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

Well if you don't have deletions and only new records, then isn't it going to grow naturally?

Answer 3

Compacting an Access database is unavoidable unless the database is set up to be read-only. See the below link for more info regarding compacting and autonumber columns:

https://stackoverflow.com/questions/13865579/access-autonumber-sequence-changed-after-compact-repair?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 4

"Compacting an Access database is unavoidable"

Some growth, especially initially, is normal, but continual ballooning is not.  Everything has to be in context of usage, but thing should stabilize after an initial growth from first usage and then should only progressively increase based on input from the users.

 

What I have done in the past is export each object to a new blank db so I can get a relative size of each table (forms and report can also be interest to analyze, but that a different issue than the one at hand).  Now do this for a few days and compare the values.  This should enable you to quickly identify which tables are growing.  Then you can start looking at things more seriously if you deem the growth to not match up with usage.

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 5

One thing you can do/set is turn off row locking. This will reduce the BE bloat by a huge amount. You still get growth, but not near as much.

If I turn off row locking, then this simple loop shows DRAMATIC difference.

The database is about 6 megs in size. If I run this simple “edit” loop

   Dim rst        As DAO.Recordset

   Dim i          As long  

   Set rst = CurrentDb.OpenRecordset("contacts")

  

   Do While rst.EOF = False

      rst.Edit

      i = i + 1

      rst!City = "ci " & i

      rst.Update

      rst.MoveNext

   Loop

After I run above, the database bloats to 126 megs!

If I turn off row locking, then the database is the SAME size AFTER running above.

You can set row locking in your startup code (it ONLY takes effect if you exit + re-enter. This is a FE setting to Access (and not the given database). This setting will apply to the BE.

   SetOption "Use Row Level Locking", 0

So just the above simple setting reduces that simple loop bloat by 120 megs – in fact  you hardly see any change in size if you turn off row locking.

Give this setting a try - it is often VERY dramatic in reduction of bloat.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 6

Hi, thanks for the quick answer.

The problem with the IDs comes because whenever you compact, the application looks at the highest ID value and uses it to reset the autonumber of that table... and I don't want that.

This might not be the right way to do things, but it's the only way I came up with: I have an application for a company which has 3 shops. Each shop has it's own accounting, stock, staff... basically, each shop has it's own database. Furthermore, I am in an area where WiFi and even phone reception are not reliable, so having a central server with all the data and connecting each shop to it over the net is not an option. On the other hand, the owner wants to be able to put all the data of all 3 shops in a single database at the end of each month and also they want to control the settings from this 'global' database.

So basically, what I've done is I've assigned a different offset to the ID's of each shop so that at the end of the month I can import all the data to the global one and I won't have any primary key duplicates. So, of course, I need to keep this offset value and I cannot have access just taking the highest ID and running with it...

Like I said, it might not be the best solution, but it was the only one I came up with.

As for the bloating... I've no idea how to test for that, to tell the truth =P I do not use attachments, though... How can I know the size of each of the database objects? (in MB, not in rows, of course)

Thanks!

Answer 7

Yes, of course it will. However, if new rows were the only cause of growth, upon doing a compact and repair, the size of the database wouldn't change because the data remains the same. However, on my last compact, the database went from 25MB to 15MB...

Answer 8

Ok, I'll try that, Thanks!

However, wouldn't exporting a table to a new database also sort of compact the data so I would loose the extra whatever that disappears when I do compact and repair?

Answer 9

Oh! I had no idea about that! I'll try that, thank you!

Answer 10

Hi TraGal,

If you have any update about this issue, please feel free to let us know.

Best Regards,

Tao Zhou

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].