Solved Compact on Close despite setting, accdb split database FE (1 Viewer)

[The_Doc_Man]

OK, if you knew about it then my comment isn't applicable. But that topic popped up when I did my searches.

 

[HalloweenWeed]

RE: Compaction (FE)

compacting the FE doesn't make sense unless you're storing local data in it, which you normally shouldn't. Instead, just get a fresh copy of the FE each time you run it - no need to compact it when you're done (you'll get a fresh copy next time anyway).

Well I've (only just) rebuilt my FE from scratch, only importing the (small) tables to the FE, manually copying everything else (copied SQL into Queries), manually copying object names (queries, forms, & reports). I imported and copied in this order: Tables, Queries, Forms, Reports, and then vba. And here is what I have discovered when using compact afterward:

• After adding all the tables and queries, compact had virtually no effect on file size.
• After adding the Forms, compacting reduced the file size by almost 1.5MB.
• After adding the vba, compacting reduced the file size by 1.5MB.

No database operations had been run, not even the default main menu loading.

 

[HalloweenWeed]

OK, if you knew about it then my comment isn't applicable. But that topic popped up when I did my searches.

No, I didn't know about it, but it appears to have been only a problem if you are accessing a USB drive remotely.
I do appreciate your concern Doc.

 

[The_Doc_Man]

Don't know about your other problem, but that observation regarding compaction on forms and VBA is simple. In anything that is being developed, if you make a big change, there is a substitution of something new to replace something old. Just like for tables, form and VBA updates do not discard obsolete info until you do the C&R. The forms also have class modules with VBA and they are subject to the same factors as the General Module VBA code. So it isn't a surprise that your C&R had some extra things to remove.

I'm pretty sure that the way tables are imported is record-at-a-time but we don't really know how modules (class or general) are imported so Access can easily have brought along some VBA baggage.

 

[HalloweenWeed]

Well, after the following procedure, I have corrected the problem, along with speeding up the load/exit cycles, it now closes in 10 seconds (remotely):

1. Started new empty FE database.
2. Imported the (small) tables from the original.
3. Linked to the BE tables manually.
4. Forged each Query from scratch copying the SQL code from the original FE version.
5. Created each Form & Report manually, setting each property for each form/report, then copied all textboxes/controls/buttons/labels from the original.
6. Created & copied the vba into the modMouseHook module.
7. Created & copied the vba into the custom functions module.
8. Copied all the vba from all the forms and reports.

All seems to be well so far, and I have added "Option Explicit" to all modules. It took me about 10-12hrs. work, but I think it may be well worth it, as I have seen no hint of instability after compacting, so far. It used to take 90 seconds just to close (remotely), now just about 30, compact is faster too. Thank you all for your help.

 

[Micron]

Glad to hear your hard work paid off. I hope you have at least one local and one remote backup for this.

 

[HalloweenWeed]

Glad to hear your hard work paid off. I hope you have at least one local and one remote backup for this.

Thank you, oh yes of course I do, even previous step backups.