Forum Discussion

MarcoM70's avatar
MarcoM70
Copper Contributor
Jun 15, 2022

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

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.

access
  • George_Hepworth's avatar
    George_Hepworth
    Jun 17, 2022

    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.

     

     

     

     

     

     

  • arnel_gp's avatar
    arnel_gp
    Steel Contributor
    Jun 16, 2022
    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).
    • MarcoM70's avatar
      MarcoM70
      Copper Contributor
      Jun 16, 2022

      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.

      • arnel_gp's avatar
        arnel_gp
        Steel Contributor
        Jun 16, 2022
        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.