Compact & Repair - what does it do with forms? Other options?

[bentheimmigrant]

I've been taking over a sizeable Access/VBA project, and we've been integrating a third party dll based tool. Sometimes, when creating the objects to use the tool, something goes wrong. This is intermittent, and apparently impossible to consistently re-produce.

Unfortunately, I've only found two ways to fix the problem, one is to close and reopen Access (10 times was the highest count), and the other is to Compact and Repair (usually works first time).

I can't tell if the problem is in the tables (all stored in the back-end) or in the forms on the front end.

Unsurprisingly, my boss would like this to resolve itself when there are issues setting up.

Long story short, I'm planning to write a function to (possibly) close the forms, compact and repair, and then re-open and load the view the user was last on. I think there's enough out there on this that I'll be able to cobble something together in VBA. But my real questions are:

1. Does Compact and Repair do anything with the front end forms?
2. If not, can I leave the front form open (with some kind of "please wait" message while it happens?
3. What could go wrong? Do I need to backup everything every time I carry this out?
4. In the event that it's not broken fields in the database (the 3rd party tool takes a long encoded string from one of the tables to load its settings, so why do I only sometimes need Compact/Repair if that's the issue?), and compact & repair isn't strictly necessary, what other options are there to produce a similar effect of unloading and reloading everything?

 

[Ranman256]

I had this problem and it was the PC! Removing and uninstalling Access, and reinstalling did nothing.
Got a different PC and it worked great!
Some PCs have a thing/or something missing/whatever that wont accept the db, so it crashes.

 

[bentheimmigrant]

No, it's definitely not the PC - we've tried these things, and it happens on all PCs, which all have somewhat different environments.

 

[The_Doc_Man]

First a misconception: Even for a split database, the work is done in the front-end, so that is where temporary structures are created. So having everything in the back-end does not stop the front-end from bloating.

As to your numbered list of questions:

1. Improvements for queries, forms, reports, macros, and modules are not likely after the first time they are compacted. A newly created form MIGHT be stored a little funky, but subsequent use after compacting does not cause it to regain any of that funkiness. Only a table is truly dynamic.

2. Leaving a form open? No, because Compact/Repair requires that the database file will be closed and reopened. You cannot have a form open. HOWEVER, if there is a pre-defined "opening" form that is your switchboard or control panel, it will come up again when the Compact/Repair operation is complete and might look like the form stayed open. The better approach for the FE file is to have auto-compact on exit.

3. Doing operations locally on your workstation, the only thing that could go wrong is if you have failing hardware or EVER interrupted a shutdown of MSACCESS.EXE - but if you do this operation over the network, you introduce other sources of failure. Nonetheless, a preliminary backup - or even a simple file-copy to a secondary folder while working - would assure you of far greater safety.

I would say that for the shared BE file, you do best to MOVE (not copy) that file to a subdirectory under the shared folder, then copy the BE file to your workstation, do the Compact/Repair locally, and then recopy the cleaner BE version back to your shared area. During the time that the database maintenance is under way, having moved the file away from the share "shuts off" the database because the back-end just ain't there.

As to the C/R operation on the front-end - do the auto-compact on exit and don't worry about it. Let your users make new copies of the FE from the shared area. NEVER, ever in a gazillion years should you allow them to directly use the shared copy of the FE file. Always force them to use a local copy of same. Carry a two-by-four with big nails sticking out of it when you visit the desk of anyone who violates that rule.

4. You need to understand what a Compact/Repair does. I'll try to explain it.

Almost ALL of the space recovered from a file via Compact/Repair operations comes from having created and deleted something. Opening a query, for example, creates a recordset structure behind-the-scenes. If the query involves a JOIN, then a temporary work space - often called a scratchpad in computerese - is created to hold the results of the JOINed recordset information, even if only a little bit at a time. When the recordset closes, the temporary workspace is marked for deletion.

Each object in the database (table, query, form, report, macro, module) occupies space. If you edit that object, one of the possible changes is that the space becomes fragmented in some way. Deleting records releases space that leads to the table becoming fragmented (i.e. its parts are no longer exactly adjacent to each other.)

Compaction is merely this sequence of events:

a. Open a secondary database usually called DB1 or something like that.

b. For each object in the database being compacted, find each substructured element (recursively as needed). COPY the items to the secondary database so that their parts are adjacent to other. Do NOT copy items marked as DELETED.

c. When all items are copied, close both databases. Rename the old one to something benign. Rename the new one to the name of the old one. Delete the old one.

What this does is it defragments all objects (which improves access because one disk buffer is most likely to contain only the one object and not three or four objects). It also removes all items that became obsolete through deletion of records or objects, thus reclaiming the space they occupied.

 

[bentheimmigrant]

Thank you so much for such a detailed reply. That is exactly what I was looking for.