Ways to minimize risk of corruption of an accdb (back end, only tables)

[MarcoM70]

Hi.
Several weeks ago, after compacting (via VBA code) an accdb (back end on a server), I couldn't find anymore some indexes in a (important...) table.
In the compacted accdb, I found the MSysCompactError table (as expected, as compacting didn't go well ...).

Now, after reading some tips on the web on how to remove minor corruptions from an accdb, I created the following procedure, that I would like to run periodically on the accdb back end (in order to minimize the chances of losing indexes or corrupting in any way the accdb):

1) make a local copy ("C") of the back end accdb file ("BE");

2) compact "C" in file "D";

3) export all database objects of "D" in an empty accdb "E" (let's call "T" file "E" after the export);

Note: the 'export' is performed through the execution of this sql string (on all tables of "D"):
" SELECT * INTO [strConnectionStringOf_E].[tableName] FROM [strConnectionStringOf_D].[tableName] "

4) recreate in "T" all the indexes in "BE" (more precisely: the 'expected' indexes that the original "BE" should have);

5) recreate in "T" all the relations in "BE" (more precisely: the 'expected' relations that the original "BE" should have).

Now, this procedure 'worked' at recreating my "BE" (that is the file "T" after 5) in such a way that the next compact on this new file worked fine (without errors and without losing any index).

Does anyone know:
- why this procedure 'worked' (in the above sense)?;
- if this procedure has any sense or is just a waste of time?;
- if (supposing it is not useless) it could be done in a better way?

Many Thanks.

Marco M.

[#2]

if it is not Broken, don't try to fix it.
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).

[#3]

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.

[#4]

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.

[#5]

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.

[#6]

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)

[#7]

@MarcoM70 

When you compact & repair the back end, do you retrieve it to the local computer first? Or do you try to do the C&R across a network? If the latter, problems of the sort you describe are more likely.

[#8]

@MarcoM70 

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

[#9]

I compacted it for years across the network, without any issues...(but: when it was an mdb).
I started to have issues after I converted it to accdb.
By the way: I'm doing some tests and discovered that my procedure, so far, worked only on my personal computer at home where I have Access 2016 64 bit. At the office, where I have acc 2016 32bit, it didn't work. After the procedure, trying a Compact & repair within Access, I got these errors in MySysCompactErrors table:
ErrorCode ErrorDescription ErrorRecid ErrorTable
-1011 System Resource exceeded MSysNavPaneGroups
-1011 System Resource exceeded Movimenti
-1011 System Resource exceeded MSysNavPaneGroups
-1305 The microsoft Jet Database engine could not find the object 'MSysNavPaneGroups'. Make sure .... If 'MSysNavPaneGroups' is not a local object, check your network connection or contact the server administrator. MSysNavPaneGroupToObjects
Is there a way to cure my back end, now?

[#10 · Accepted solution]

@MarcoM70 

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.

[#11]

@George_Hepworth 

*And that's probably the missing detail we needed. Does the accdb compile in both environments? *

Yes, the front end compiles in both environments (the front end is an accde). No ActiveX controls.

The referenced libraries are: VBA, Access 16 Object Library, DAO 3.6 Object Library, MS XML v6.0.

In the office, my colleagues have the 64 bit version...

The only guy who uses the 32 bit version is "me in the office" (while "me at home" uses the 64 bit version - which is the main version of development).

I think you pointed out the most probable cause of corruption, which is not the 64-bit version, but the 32-bit version ("me in the office"...).

In the coming days I'm going to follow your suggestions and then report the results as soon as possible.

[#12]

That would be helpful for a lot of people. It might "look like" corruption, but actually be a case where the different versions handle something differently enough to cause problems.

[#13]

I'm trying to make sense of @arnelgp's point about query statistics. Probably this was what he meant....

When a query is run, Access looks for the most efficient way of running it and saves the query execution plan for future use. When you compact a database, the query execution plans are removed. These will be rebuilt the next time each query is run which means the query will run slightly slower the next time.

[#14]

I copied the accdb back end in the office (that I wasn't be able to compact & repair with Acc 16 32-bit version) at home:
I tried an ordinary compact & repair within Acc 16 64-bit version and... it worked fine!
I tried DBEngine.Compact, too: no issue at all, again.

Thus, a possible explanation could be that when one of my colleagues (with the 64-bit Front end version ) performed a compact (via VBA code), the accdb was converted to such a state (not corrupted, as the accdb didn't lose any pieces) that the 32-bit version was and is not anymore able to compact & repair (within Access or via VBA Code).
Maybe, this state comes from the very beginning (when the mdb - with which I didn't have any problems, although it was compacted indifferently by 32 and 64 bit versions of access 2016 - was converted to accdb with my 32 bit version in the office).
I don't know exactly when the problem arised (If I knew I'd probably know also why).

More in detail the issue is:
compact & repair with the 32 bit version now causes the loss of indexes only in one table ("Movimenti"), which is the biggest table in the accdb (150 MB - 312.000 records), which should have 15 indexes with one field and 1 index with 3 fields.
After the compact & repair, 11 indexes are removed...
I suspect that this is not a mere coincidence, as in MySysCompactError table (in the 32 bit version) I reported the memory system exceed memory...

[#15]

Are any of those indexes duplicates, i.e. are there two (or more) indexes on the same field? That can happen unintentionally.

Also, I think that last message may be the key. If the amount of memory required to complete the process exceeds available memory, that could result in the problem you observe. To test that hypothesis, you could export that single table to a new, clean accdb and run the C&R on it by itself to see what happens.

[#16]

By the way, I admire your objective, disciplined approach to trouble-shooting this problem. Continued success with the project.

[#17]

@George_Hepworth 

No, there aren't index duplicates.
But I have to say that some indexes were duplicated in some table (and I discovered them only via VBA...). Unfortunately, I don't remember anymore in which table... (I'm not 100% sure but it is very likely that there were some of them in the table which gave me this problem).
Anyway, I deleted them, and now, the pseudo-corrupted table hasn't any (I checked also with VBA).
The C&R doesn't work also in a clean accdb with only the table that seems problematic/corrupted with the 32-bit Access 2016 but not with the 64-bit Access 2016.
So, what should the conclusions be?

[#18]

@George_Hepworth 

Thank you George for your kind words.

Without your precious help I probably wouldn't have realized (among other important things) that
is preferable not having front ends built with different bitness (although of the same version) working together.

[#19]

Unfortunately, some problems seem to be unique to a particular environment. Without the ability to actually analyze the table, I'm not sure what else to propose could be the problem.
I suppose you could try creating a new table, taking care to add only the indexes you need and the restraints -- such as required values -- that you need. Then append the records from this table to it and see if you can manage to C&R it.

However, at some point, it seems like this goes beyond a repair of a working solution into a pursuit of deeper understanding that may or may not result in useful knowledge.

How many records are in this table, btw?

[#20]

@George_Hepworth 

*which is the biggest table in the accdb (150 MB - 312.000 records)*

The procedure I described in my first post was exactly an attempt (implemented in a working solution) to solve minor corruption issues, mainly through the SELECT INTO clause. I quote myself:

* 1) make a local copy ("C") of the back end accdb file ("BE");

2) compact "C" in file "D";

3) export all database objects of "D" in an empty accdb "E" (let's call "T" file "E" after the export);

Note: the 'export' is performed through the execution of this sql string (on all tables of "D"):
"SELECT * INTO [strConnectionStringOf_E].[tableName] FROM [strConnectionStringOf_D].[tableName]"

4) recreate in "T" all the indexes in "BE" (more precisely: the 'expected' indexes that the original "BE" should have);

5) recreate in "T" all the relations in "BE" (more precisely: the 'expected' relations that the original "BE" should have).*

Now, I initially believed that this fixed the problem, but I was at home with the 64 bit version. When I tried the procedure in the office with the 32-bit version, I realized that it didn't work (then discovered that the pseudo-corrupted back end was not 'really' corrupted as the C&R worked without issues with the 64 bit version).

I'll certainly try an INSERT INTO (tomorrow), but I have autonumbers as PK in my tables:

if an INSERT INTO should work, how could I manage the autonumber fields?