Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Columbus, OH
    Posts
    36

    Unanswered: Best Practices for Preventing Data Corruption

    Could anyone give best practices for preventing database corruption in Microsoft Access?

    Background:
    We built a multi-user system with a .NET Windows Desktop front end and Microsoft Access as a back end. I am doing a database compact and repair, once a week but I am still seeing some users having their databases corrupted. We chose Access over MSDE because our target audience still had dial up for internet access and MSDE is a 44MB download.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can repair and compact more frequently, as it is not an expensive operation. You could also make it possible for your user/owners to initiate a repair and compact.

    How many users are in the database at a time?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use a server back end! perm any one of the following SQL Server, Oracle, DB2, MySQL, Postgre, Sybase..... ther lis goes on

  4. #4
    Join Date
    Feb 2004
    Location
    Columbus, OH
    Posts
    36

    Number of Users

    There are a maximum of 5 users in the system at a time. The product is sold to dozens of small t-shirt printing shops around the world. How large is MySQL and the other databases you mentioned to install? Do they support the same database file concept that Microsoft Access has? In other words, there is no central server and all access the same file? SQL Server would be overkill for 5 users and really expensive, it would raise the cost of our product 10 times if we bundled SQL Server with it.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Using Access

    Citrix is a nice solution but it'll cost you as well. Using MySQL is also a good solution if you don't want to pay for anything and have a server based table system.

    You can also consider writing your code to do make tables but you'll need to be careful on how you do this and it'll add overhead to the size of the database. Another approach (which I rarely use) is to have a separate database which holds just the tables and another which has the front-end linking to the other Access database with linked tables.

    You shouldn't need to compact and repair that often unless you're updating the tables a lot.

    The best way to NOT corrupt a database is to NOT copy new code while users are in the database. If you're constantly updating and copying new code, you need to make sure all the users are out of the database (no *.ldb files showing) before you copy over the top of the existing database otherwise you'll most assuredly corrupt the database.

    I rarely get databases that corrupt. When I do, it's usually something code related/technique or because I copied new code while users were in the database.

    You can also try coding the database to use unbound forms and create functions to write/update/retrieve records. This works well if all you have is MSAccess to work with. It's more work and code writing but it'll most assuredly save you some headaches from your database corrupting.

    Paul Kohn
    Database Administrator

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    DELETED
    izy
    Last edited by izyrider; 01-29-05 at 05:46. Reason: my comment was covered earlier in the thread.
    currently using SS 2008R2

Posting Permissions

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