5
0
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.
- new blank DB
- set all table links
- 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).
Smandoli
Posted 2015-07-30T21:25:09.677
Reputation: 159
Is the option set as shown here?
– harrymc – 2015-10-01T14:24:13.713No. As stated, "compact on close" is not checked. – Smandoli – 2015-10-01T15:28:40.020
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 – 2015-10-01T19:11:03.817You 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 – 2015-10-01T21:45:44.673
Same version of Access. FE is downloaded to user's C drive with every launch. – Smandoli – 2015-10-02T13:59:55.423
The other questions above are now answered within the post. – Smandoli – 2015-10-02T17:45:31.987
3I 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 – 2015-10-06T15:54:25.727
@Beems - thanks, and may I suggest you post that as an answer? That way you can have some nice credit, and it suits "the SO Way". Plz clarify.... surely I can re-make the database one time only? but this seems unclear. – Smandoli – 2015-10-06T16:33:07.823
Yes, the intent would be that you simply export it once (not copy to a new file, but export the data out, then re-import in a new database), then henceforce use the newly-created version. It seems based on your ability to handle the VBA portions in your original question that you don't need instructions on exporting-->importing, correct? – Beems – 2015-10-06T17:31:27.327
How did you come out? Did exporting the data and re-importing work for you? – Beems – 2015-10-28T15:29:52.650