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).

  • Is the option set as shown here? – harrymc Oct 1 '15 at 14:24
  • No. As stated, "compact on close" is not checked. – Smandoli Oct 1 '15 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 Oct 1 '15 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 Oct 1 '15 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 Oct 6 '15 at 15:54
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.