Access 2016 Bloat

Question

I have a very simple Back End .accdb database - just 4 tables. I have emptied all of the tables and run Compact & Repair (several times) but the file is still 53Mb. I recognised this as an issue of many years ago and created another database by importing the empty tables into it. This is a mere 590Kb - so much more what I was expecting! This database will be used and I would prefer it didn't grow to massive size unnecessarily. Does anybody know why this issue should be happening?

(I wonder if there is something to do with a history of attachments having been in the database as I had experienced old attachments reappearing when creating new table records - but hadn't fathomed out why!).

Access version 2016.

Answer 1

Thanks - I tend to only empty the tables etc. during testing and create (by import) a new BE database before going live - but it's disconcerting that the attachments are lying there lurking in the background, ready to come back and haunt at will!

Answer 2

It's a common practice to import to a new, blank database prior to release, so I wouldn't worry too much about that loose end.

I would assume your final build is NOT going to empty your tables as you've done - is that correct? If so, then you have nothing to worry about.

If you do use "dynamic" tables, then the fix for that is to use an external database you can create and destroy as needed.

Answer 3

A common issue and problem is perhaps at one time that database was used for development.

So custom menu bars, and imbedded images in the database are not transferred if you just import the tables in question.

In other words, now that you have this “new” fresh copy, you likely find that a C+R will reduce the size down to a min.

So while a C+R will recover blank unused data space, custom menu bars and especially imbedded imagines in the application are stored in sys tables – by importing to a new database, you learning behind that extra junk.

and it possible that some child tables storing attachments were also orphaned (this would make sense if the database was never used for application development with forms/code/images  etc.)

So the bloat and “stuff” likely was from the past when the database was undergoing development - if not the case, then your ideas about attachments being lost makes sense.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 4

Thanks for replying Scott - appreciated. Sadly though nothing helps. The database is only tables (no code), but I decompiled anyway after compacting, and Office365 is up to date, as is Windows 10 (both set to update automatically, and checked). So still a mystery! (As I said, have got around it by importing into a new database - but I hate loose ends!)

Answer 5

I wonder if there is something to do with a history of attachments having been in the database as I had experienced old attachments reappearing when creating new table records - but hadn't fathomed out why!).

I would suspect this is a clue to the issue. Deleting records should also delete the attachment records, and if it's not doing that then you would likely have issue with either (a) the database or (b) the environment.

If it's the database, then most times your C&R would fix the issue. You may also try a Decompile to see if that works. To do that, create a standard Shortcut with this Target:

"full path to msaccess.exe" /decompile

Run that shortcut, and then open your database from within that session of Access. After opening, compact your database again, and compile your code (click Debug - Compile from the VBA Editor, and fix any errors you find).

If it's environmental, you should ensure that your installation of Office and Windows is fully up to date.