Forum Discussion
Ways to minimize risk of corruption of an accdb (back end, only tables)
Some people do report that they've not had problems doing a C&R on an mdb/accdb on a network share. If you want to continue that practice, it's up to you.
"... at home where I have Access 2016 64 bit. At the office, where I have access 2016 32 bit, it didn't work. "
And that's probably the missing detail we needed. Does the accdb compile in both environments?
To be sure you've recovered it fully, what I would do is create a new, empty accdb in whichever version you intend to use when actually working with this accdb, presumably in the Office, where you have the 32 bit version. Then import the existing tables from this borked accdb into it and make sure the relationships, indexes, etc. are all restored properly.
Then I would NOT mess with it again on the 64 bit Access computer. No guarantees one way or the other that that is the source of the problem, but, while you can run the accdb under either version--assuming the code has been properly converted to run in both environments and there are no incompatible 32bit ActiveX controls in the accdb--I would be very reluctant to introduce changes by altering it in the 64 bit version, including a C&R.
you only compact when there are issues.
you don't compact everytime, since you lose statistics
of your query (and get rebuild again when you again run it).
Hi arnel_gp,
thank you for your answer.
In my experience, it seems that accdb back ends need to be compacted less often than mdb back ends (in my experience, accdb doesn't grow as much and as often as mdb).
However, if I'm not wrong, Microsoft suggests to perform periodically a compact operation on the back end, regardless you find issues (not only in order to shrink the file but for other reasons linked to performance issues, query execution and so on).
Maybe, it could be reasonable to perform an ordinary compact each 10 days (so to say), leaving the procedure I propose in order to try to fix a corrupted accdb (in my case, the procedure worked in order to regenerate my accdb back end on which compact operation could be performed again without errors).
That said, I still have some doubts on the following point:
- in a split database (as in my case, where I have forms, reports, query, VBA code, etc. in a Front End accde and only tables in the accdb back end), what exactly impact on statistics of queries: compact on the back end or on the front end (or both)?
Thanks.
- Compact & Repair has the greatest impact on file size in two situations. One, is the back end, where data is added. The other is in a working accdb where a lot of temporary tables are created, filled and deleted.
In a Front End accde, if you are NOT working with temporary tables, the bloating problem should be minimal and C&R should not be required very often. I think the fear of introducing problems due to regular Compact and Repair probably isn't all that great.
I'm looking forward to an explanation of the statistics on queries.- I need to compact often (both the back end - where additions are very frequent and deletions are common - and the front end, where temporary tables are used extensively).
The problem is that it's not so rare (as it happened to me... and I'm sure I'm not alone...) to lose important indexes & relations (losing also referential integrity) without realizing that (if you compact via VBA code), after a compact operation of a corrupted back end.
So, after a compact operation, I'm going to check if all the indexes and relations are still there.
If not, I'm going to launch the procedure I described in my first post, for which I'm still waiting for anyone who can tell me if it is good, bad, useless, useful .... The only thing I can state about the procedure I described is that it fixed in some way the back end (after the procedure, the new back end could be compacted again without losing pieces...), without exactly knowing why... (was it the export that fixed corruption issues? is compact (step 2) really necessary? and so on)"The problem is that it's not so rare (as it happened to me... and I'm sure I'm not alone...) to lose important indexes & relations (losing also referential integrity) without realizing that (if you compact via VBA code), after a compact operation of a corrupted back end."
I've never seen this happen, except when the back end is already corrupted. Therefore, I have to assume that the problem lies in the corruption, not in the C&R per se. It is true that Access will drop constraints on corrupted tables in order to preserve as much data as possible, but that's a two-edged sword. I'd say it's probably better to resolve the corruption first, therefore, and then complete the Compact & Repair.
- Compacting can either make it good or worst.
With what microsoft is doing right now (bugs after bugs shows up on each Update), i better leave the BE as is or at least make a backup of it.