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.
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?
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.
- I'm probably too late for this occasion, However for future use, you may find my article https://www.isladogs.co.uk/recover-deleted-objects/index.html worth reading.
*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.*
Hi George, that 'something' is encryption, as you can read in the message I posted to myself some days ago.
---
The last days I've tested my hypothesis not very rigorously...
I took if for granted that encryption didn't matter... I was WRONG.
So, initially, I missed the other important detail:
my accdb back end is encrypted.
I think I've come to the end of the story. Here what I discovered.
With Access2016 32-bit with my PC (8 GB RAM, win 10 PRO), import/export/C&R work as expected (no issues) if the destination accdb is NOT encrypted (although the source accdb IS encrypted).
If the destination accdb is Encrypted, import/export/C&R fail (with error: system mem exceeded. No problems with records; problems only with indexes, which are not created properly).
Now I'm happy, as I found the reason of the conundrum.
Thank you guys for your help (hope to be useful to someone else...).
Replying to myself:
The last days I've tested my hypothesis not very rigorously...
I took if for granted that encryption didn't matter... I was WRONG.
So, initially, I missed the other important detail:
my accdb back end is encrypted.
I think I've come to the end of the story. Here what I discovered.
With Access2016 32-bit with my PC (8 GB RAM, win 10 PRO), import/export/C&R work as expected (no issues) if the destination accdb is NOT encrypted (although the source accdb IS encrypted).
If the destination accdb is Encrypted, import/export/C&R fail (with error: system mem exceeded. No problems with records; problems only with indexes, which are not created properly).
Now I'm happy, as I found the reason of the conundrum.
Thank you guys for your help (hope to be useful to someone else...).
*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*
I tried an export of the (seemingly) faulty table (from the accdb back end that was compacted - via DBEngine.Compact() - with no issues by one of my colleagues using Access16 64bit Front End), first to a clean accdb created by Acc16 32-bit and then to a clean accdb created with Access16 64bit.
I then tried (twice) a C&R with Access16 32-bit (with both accdb files), and...:
- I had no issues with the accdb created with Access16 32-bit;
- I had issues with the accdb created with Access16 64-bit.
In the first attempt, I was able to export the table, but I lost two indexes.
In the following attempt, I got the System Memory Exceeded error during the export.
In both attempts, all records had been exported, but in the first attempo I lost two indexes and in the 2° I lost 4 indexes...
The C&R generated the MSysCompactError in both attempts (and not always the same error...).
Things went slightly better using import instead of export:
no issues starting from accdb 32bit; I had only C&R issues starting from accdb 64 bit.
Thus, it seems that an accdb created by Access2016 64-bit is not fully manageable by Access2016 32-bit (it seems that there aren't issues with read/write/delete/append operations, but it seems there is an issue, at least, with export and C&R).
In the coming days I'm going to verify the following conclusion (that seems reasonable to me, after the above results):
- in my environment (with 3 accde front ends built wit Acc16 64-bit and 1 accdb / accde built with Acc16 32-bit), the C&R ( in my case, a DBEngine.Compact() ) fails only when run by the 32-bit front end without having first exported (or imported) all the objects of the back end to a clean accdb created by Acc16 32-bit.
I will let you know the results.
*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?
- 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? 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.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?- By the way, I admire your objective, disciplined approach to trouble-shooting this problem. Continued success with the project.
- 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. - 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... - 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. - 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.
*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.
