Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2002
    Posts
    23

    Unanswered: Access 2002 DB Corruption

    I have a access 2002 database stored on a Windows NT server.
    Tt is being shared between 5-10 users runing Windows 2000.

    We are having constant problems with the database getting corrupted.
    Then a message comes up saying that "the database needs to be repaired".

    How can this be resolved?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Set the file to read only.

  3. #3
    Join Date
    Jun 2002
    Posts
    23
    All of the users are entering data into the db so it can't be read only.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by Bspahr75
    All of the users are entering data into the db so it can't be read only.
    You may want to consider splitting into front and back ends. Alternately you can drop the file to a common format.

  5. #5
    Join Date
    Jun 2002
    Posts
    23
    How would I drop the file to a common format?

    Is the problem I am having due to to many users?

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by Bspahr75
    How would I drop the file to a common format?

    Is the problem I am having due to to many users?
    To be perfectly honest, I'm not sure what the exact problem is, but I have to deal with it as well.

    I have an application designed in 2k3 that works just dandy on other machines running 2k3, but tells me the database is corrupt and needs to be repaired if the client is running 2k.

    If I either compile the project on a computer using 2k, or set the client application to read-only, I no longer receive this error.

    As far as the multi-user environment, I wouldn't be comfortable with access supporting more then 5 concurrent users without splitting it into a front/back end situation.

  7. #7
    Join Date
    Jun 2002
    Posts
    23
    Is it difficult to split the db into a front and back end?
    How would I do this?

  8. #8
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59
    There's a utility that will do it for you automatically. But here's the manual way (it's always best to know how the wizards work anyway):

    1) Make sure no one is using your database.

    2) Make a copy of the database and set it aside

    3) Create a new, blank database.

    4) Once inside the new database, go to File>Get External Data>Import... on your menu bar.

    5) An Import dialog box (just like a File Open one) will pop up. Select your existing database and press <Import> (not LINK TABLES!).

    6) Click on the Tab labelled "Tables."

    7) Press "Select All".

    8) Press "OK". This will bring in FULL COPIES of all the tables in your old database.

    9) Save the new database. Most people use a naming convention that implies the relationship between the two dbs. I've seen "_be" appended to the name of the front-end database to make the name of the backend. For example: MyRecordCollection.mdb and MyRecordCollection_be.mdb. Be smart and do something like that.

    10) Make certain you've backed up the original database.

    11) Make doubly-certain you've backed up the original database.

    12) Close the database and open up your original one. This is now your front-end database. The one with your tables is the back-end.

    13) Delete all of the tables in this database (now you see what backups are so important <grin!>. Really! Delete them.

    14) Go to File>Get External Data>Link Tables (NOT IMPORT) and do the same thing as before, this time selecting your BACKEND database.

    15) Select all the tables, press OK. You will see all the tables return to your database but their icons will look a bit like shortcut icons (there'll be a little arrow by each suggesting the link).

    16) Test your link by opening a table and seeing if you can get to the data.

    IMPORTANT THINGS
    -----------------------------------
    Save the backend database to a common directory on the network (unless you're doing some kind of table synchronization thing - unlikely)

    Consider making an MDE file of your front-end database. This can be downloaded to each desktop or run from the network.

    If you don't make an MDE file, make your front end application Read-Only, they don't change data on the front end so this is okay.

    Magee

  9. #9
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59
    Or you can use Tools>Database Utilities>Database Splitter.

    I really recommend that you do it manually so you know what the splitter does. That way if there are problems in the future with the front-end finding data you know how to handle/troubleshoot them.

    Once you've split your database, familiarize yourself with Tools>Database Utilities>Linked Table Manager. It's where you can refresh links or change them if things move or seem to misbehave.

    Magee

  10. #10
    Join Date
    Oct 2003
    Posts
    706

    Exclamation

    If you are having consistent, repeatable problems of this type with an Access database, then I think it unreasonable to assume that splitting the database into two parts will have any measurable effect.

    (The operative words are consistent and repeatable. Any such problem must have a physical cause of some kind, and it will persist until you find the root cause of the problem. This certainly seems to be the case here.)

    I suggest that you search the MS Knowledgebase at http://support.microsoft.com for relevant problem reports.

    Very likely, a cacheing related problem (see "opportunistic locking") will prove to be the culprit. This is where various workstations hold out-of-date copies of parts of the information in their memory caches, and wind up writing them to the file... corrupting it. MS has written a number of articles and summaries on the subject.

    A good overall reference topic is: http://support.microsoft.com/default...b;en-us;300216
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by sundialsvcs
    ...stuff...
    I really don't think that's the issue.

    There is some bug that causes an access app with custom vba to not want to open on previous versions.

    As I mentioned, I maintain a live application that displays identicle behavior, and merely setting the read-only property to true solves the problem.

    I'm not sure how record-locking would effect this.

Posting Permissions

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