5

I have an Access 2010 application that runs on a busy network (separate front/back ends, same version of Access). Lately when I close it, the compaction utility runs. It's just as if the option was checked for "compact on close." But that option is not checked, neither on the back-end database.

I inherited this application, and it started showing this behavior only recently. I know the VBA code and there is no compact routine (I searched on compact in the modules to make sure). The compact happens even if I run Call SetOption("Auto Compact",False) when unloading the main form. The compact also happens if I disable VBA by not clicking on "Enable Content".

I have heard you can launch Access with command-line parameters to get this behavior, but that isn't the case here.

I have also run decompile/repair maintenance, but no change.

Why does the database compact on close?

UPDATE

Beems had the correct answer, which was to remedy a subtle corruption of the file.

  1. new blank DB
  2. set all table links
  3. import non-table objects

If Beems presents this helpful advice as an answer, I'll accept, otherwise I will post the answer and push to Community Wiki (as a kind of courtesy in which I don't collect credit that isn't mine).

Improve this question
10
  • Is the option set as shown here?
    – harrymc
    Commented Oct 1, 2015 at 14:24
  • No. As stated, "compact on close" is not checked.
    – Smandoli
    Commented Oct 1, 2015 at 15:28
  • You are using VBA, so what happens if : (1) you run without your VBA, (2) you run a macro on exit that does Call SetOption("Auto Compact",False).
    – harrymc
    Commented Oct 1, 2015 at 19:11
  • You said it runs on a network...is the front running locally? Is the version of the back end the same as you are running locally
    – CharlieRB
    Commented Oct 1, 2015 at 21:45
  • 3
    I believe the "compact on close" option is set per-database, and that the setting is stored within the MDB/ACCDB file itself. My suspicion is that the setting is corrupted (and since it isn't stored in the registry or separate config file, we can't just reset it to my knowledge). Because the file is downloaded from the server at each run, my suggestion isn't ideal: export the tables to a duplicate database, update the VBA to utilize the new file, and try again.
    – Beems
    Commented Oct 6, 2015 at 15:54

1 Answer 1

Reset to default
0

I believe the "compact on close" option is set per-database, and that the setting is stored within the MDB/ACCDB file itself. My suspicion is that the setting is corrupted (and since it isn't stored in the registry or separate config file, we can't just reset it to my knowledge). Because the file is downloaded from the server at each run, my suggestion isn't ideal: export the tables to a duplicate database, update the VBA to utilize the new file, and try again. – Beems Oct 6 at 15:54

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .