Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Database Corruption (2000)

    Our company has moved from Access 97 to Access2000, and we seem to be having a daily problem with corruption of a database.
    I didn't design this database, but this is as i see it..

    <font color=black>Description:</font color=black>

    Front end is 7,912k and sits in the same directory as the Back End (23,104k)
    Shortcuts lead to the front end from around 40 Pc's..
    6 of these Pc's have workgroup permission to write to the tables.

    In an attempt to stop the daily corruption, i changed the record locking information , in options> <font color=blue>advanced</font color=blue> > <font color=blue>default record locking</font color=blue>

    to > <font color=blue>Edited Record</font color=blue> .

    From what i read, i thought this is supposed to lock any record thats being amended so that corruption won't occur when 2 people try to amend the same table....

    but this doesn't seem to be affecting much, the database is still corrupting now on a daily basis (ironicly when we were on Access 97 , it only use to corrupt about once a week)

    Anyone have any suggestions to stop this corruption ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database Corruption (2000)

    One idea is to give everyone a separate copy of the frontend, instead of a shortcut to a single frontend. In my experience, the cause of corruption is not so much a conflict over editing a record, but more often that Access tries to save the design of a form when the user closes it. When several users have the object open at the same time, this leads to conflicts. If every user has his/her own copy of the frontend, this kind of conflict can't occur.
    If you prefer to keep a single frontend, you must make sure that users don't save the design of forms. I do that by setting the System Menu property of the form to No and providing a command button cmdClose that closes the form without saving it:

    Private Sub cmdClose_Click()
    DoCmd.Close acForm, Me.Name, acSaveNo
    End Sub

    There are also methods that prevent the user from closing the form any other way. If you do a search for "prevent close" in this forum, you'll find threads dealing with that.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Database Corruption (2000)

    I agree with Hans, the best thing you can do is give each user a copy of frontend (regardless of whether/not it solves your current problem).

    BTW, I don't think you said which database is being corrupted? If the frontend, moving it to local drives should solve that. If it is the backend, then it's hard to say. This may seem like a silly question, but has the backend been converted to Access2000? I've heard of situations in which companies migrated from A97 to A2000, so the backend stayed as A97 during the time that the users were eventually updated (so some were still on A97 while others were on A2000).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Database Corruption (2000)

    Are you actually getting a corruption message that says you can't open the database, it's an unrecognized format, or something like that? Or is it a locking problem where someone opens an object in design form, and then nobody can get into the database until they exit? Access requires that a user take exclusive access before editing an object in design mode. Another question I have relates to the conversion process you used to convert - did you let Access 2000 do it for you, or did you import everything into a new empty 2000 database (recommended)? Finally, what do you mean by
    <<6 of these Pc's have workgroup permission to write to the tables>>
    Is this permissions at the network level, or are you referring to the Access security file?
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Corruption (2000)

    Thanks for the suggestions guys.

    I did do a fair bit of research on this last night, and it surely looks as though there is no quick way of stopping the corruption.

    The database has been converted properly (imported into a new blank 2000 database)

    Wendell: No, its database corruption, not an exclusive rights problem. the rights to amend the tables is controlled via the workgroup.

    What i really couldn't understand was the increase in corruption since the move from Access97.

    I guess what we really need is an SQL server ? i understand this sort of corruption doesn't occur with that.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Database Corruption (2000)

    FWIW we had a case where there were 6-7 users of a front end that was on the network, and the problem turned out to be as each person was exitting a form that other people were in it would ask if you wanted to write the form back. We solved it by giving each user the FE on their PC.

    Pat

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database Corruption (2000)

    If you have a SQL Server backend with a single Access frontend, you still run a high risk of corruption of the frontend, as pointed out by me in <post#=240710>post 240710</post#> and by patt in the reply immediately before this one.

    You must either give every user a local copy of the frontend (this has been recommended by everone who reacted to your post), or build in extra code to prevent users from saving the design of forms.

  8. #8
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Corruption (2000)

    Thanks for the clarification Hans.

    So there is a possibility that i could prevent some of this corruption if i copy the existing frontend to the desktop of the people who have write access to the database ?

    (There will be no future amendments to the Front end, and there are only 6 users)


    I had a look at the Front end, and for some reason there are 3 tables in there that are not linked from the back-end, presumably i should import them into the back end, then set up a link to them in the front end ?
    The 3 tables look fairly important to me, i can only assume they are additions for some reason or another.... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database Corruption (2000)

    Steve,

    Yes, everyone agrees that giving every user his/her own copy of the frontend prevents problems that occur with a single frontend.

    You should probably move the three tables from the frontend to the backend and then link them in the frontend, but before you do that, check whether these tables are manipulated in code (in the Visual Basic Editor, do a project-wide search for the name of these tables). If the tables are deleted and then re-created in code, you would have to adapt the code to make it work in the backend. Otherwise, I don't think you will have problems.

    To be on the safe side, make backup copies of the frontend and backend database before you do all this.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Database Corruption (2000)

    You don't indicate whether the corruption is occurring in the front-end or the back-end. If it's in the front-end, SQL Server won't help at all.
    Wendell

Posting Permissions

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