Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Location
    in a mug..
    Posts
    15

    Unanswered: Best methods to prevent Access Corruption

    What are the common and best practices in preventing Access Db corruption during manipulation in VB.

    Does size actually matters in this?
    Not all who wander are lost, except maybe Waldo.

  2. #2
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Arrow Re: Best methods to prevent Access Corruption

    Originally posted by blacksm1th
    What are the common and best practices in preventing Access Db corruption during manipulation in VB.

    Does size actually matters in this?
    The only rule of thumb regarding size I have heard is very roughly 100mb for each db, but depending on lots of factors, a very rough rule of thumb.

    I regularly (several times per day if doing a lot of work) both close and reopen access, backing up the file, running Compact and Repair on a new copy before I continue editing.

    Example: for one of my own db's (front-end, no data) I do this when the size is around 10mb (7-15 depending on when I check, what has been done). (that one is normally around 3mb after a Compact) .

    You can also both Export modules from VBE, ane from time to time create a new db importing all objects using the new db as a "fresher" backup as long as you do not discover any signs of "early" corruption, like for example form names/module names not appearing properly but as a bunch of mixed characters.

    D.

  3. #3
    Join Date
    Apr 2004
    Location
    in a mug..
    Posts
    15
    Thanks for the interesting suggestions.

    I have known some people who actually ran their Access Db over 1 Gig... I wonder how they do it...

    These are the things i follow:
    1. At least achieve level 3 NF (Normal Forms) for normalization.
    2. Use separate Database file for non-related data. In the event of a Db file corruption, at least other information are not gone as well. Also relationships in Access seems to have a special ability to bloat the Db file very quickly.
    3. Use data controls when a grid is required.

    I am looking for the best practices in working with Access. Any other suggestions are welcome regarless of DAO or ADO and other methods.

    thanks
    Not all who wander are lost, except maybe Waldo.

  4. #4
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question

    Originally posted by blacksm1th
    I have known some people who actually ran their Access Db over 1 Gig... I wonder how they do it...
    1. At least achieve level 3 NF (Normal Forms) for normalization.
    2. Use separate Database file for non-related data. In the event of a Db file corruption, at least other information are not gone as well. Also relationships in Access seems to have a special ability to bloat the Db file very quickly.
    3. Use data controls when a grid is required.
    I am looking for the best practices in working with Access.
    Hm. interesting. maybe they only use those big files as storage, no functionality, perhaps not even relationships.
    Would be interested to hear more stories / examples myself.
    1. What does level 3 mean? Unfamiliar with that related to Acc.
    2. Do you mean 2 back-ends, one for tables with relationships, another for stand-alone tables which are controlled by code in the front-end?
    3. Could you elaborate on that , please?

    D.

  5. #5
    Join Date
    Apr 2004
    Location
    in a mug..
    Posts
    15
    I just learn about 3NF - 3rd Normal Form, not too long ago. Db normalization have a total of 5 levels. 1NF to 5 NF.

    Here's a link on that topic: http://databases.about.com/library/weekly/aa080501a.htm

    I don't know how exactly others do things, hence being in this forum in hope others will have better ideas and prove me wrong. :P

    When working with Access, ONLY with Access.. I tend to break Data into a few levels based on degree of critical operation follow by how often one group of data needs to be related to others. And how often those groups get edited and updated.

    Eg. I might break away data that is constantly being updated and modified during operation away from User Db which doesn't change that frequently.

    Eg. Db tables with 'Tight' relationships get place into a file while very loose relationship gets kicked into another file. Hence relationship is created on an ad hoc basis using coding. A retail Sales Database can get pretty huge if not manage carefully. Hence i break this group away from other groups of data.

    [!!!] Do note that i am talking about working with MS Access Db. I won't use the same approach for MySQL and MS Sql.

    based on that, I split the Database into separate individual Access Dd files instead of one huge file with many tables but i keep them centralized in a system. The pros are:
    - Able to isolate and contain problems better when one exists
    - During certain Software operation, i could backup one Db very quickly instead one huge file. However, certain mechanism has to be coded to prevent other clients need modifying the file during the backup process.
    - Limit excessive file bloating problem when too many relationships exist in a single Access Db file.
    - Program has a better chance of operating when only one Db file is corrupted instead one huge centralized Db file. Check points in the program will detect when the Db has failed and prevent further access to features related to that Db.

    However i have to say that the problems of spliting related information into separate Db files will have performance expense. Yet, it is barely felt under normal usage.

    So far i have multiple network clients concurrantly working with a set of centralized Access Db files and never had an operational problem. Except Db corruption in power failure during Db update... (Yikes!) Yet the Database corruption was only isolated to a Db file, and 99% data recovered. During the Db corruption time, my customer System was 75% running as the rest of the Db files operates normally.

    * Important: When a Db access fails, it would be neat feature to be able to run automated Db Repair & Compacting.

    Another approach i took often is establish a Data Archive feature in my software to allow the clients to move redundant Data from certain date into a separated Db file.

    As i work closer to the limits of Access and before i move to MySQL, i hope to LEARN more about creating enough room for existing customers working under Access.

    The above approach is my concept of Hardening my software to deal with Access failure.

    Thanks for your previous ideas. I really appreciate it.
    Last edited by blacksm1th; 04-18-04 at 16:10.
    Not all who wander are lost, except maybe Waldo.

  6. #6
    Join Date
    Nov 2003
    Posts
    267
    That is interesting about your comments of relatiohships causing a Access Db to bloat. I have not expereinced this problem and I have DB's that included tables with so many relatiohships against then that I have maxed out the number indexes allowed with a table (and they are all relationships (except the PK). I prefer to rely on the DB to maintain data integrity rather then program it in. I am curious as to where your info came from, I would like to look into this some more.

    S-

  7. #7
    Join Date
    Apr 2004
    Location
    in a mug..
    Posts
    15
    regarding the bloating effect, i think i read it somewhere either in this forum or somewhere else. It happens when Access Db gets to certain size...

    I did not verify the accuracy of that claim because it is difficult and time consuming.

    To cut down on the bloating i also explicitly close the recordset if VB's DAO was used.

    I suspect the bloating has to be something to do with closing DAO or replicating without splitting the Db... somthing to do with Jet's playing with hidden tables. I'm not sure.

    Fundamentally, whenever a customer don't have a budget for MS SQL, i tend to look at breaking Access into a few Db files to prevent the senario where a corrupted Db causing my software to cascadingly collapse across the network.

    I don't really work with separated Db between front and backend. I think if i do that, a real replication method might do a better job.

    Thanks for all the ideas...
    Not all who wander are lost, except maybe Waldo.

  8. #8
    Join Date
    Apr 2004
    Location
    in a mug..
    Posts
    15
    How often any of you work with one single Access Db?
    Not all who wander are lost, except maybe Waldo.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •