Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Dec 2002
    Location
    Rochdale, Gtr Manchester, England
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access DB corruption (XP)

    I have developed a small database with about 8 tables, a few queries and some data entry forms based on the tables and queries. When one person is using it - all OK. When more than one person are using it (with the .MDB file stored on a W2K server), all is OK until one person comes out of the db after editing a record (via the form). Then, when they try and go back into it, they get a message that the database needs to be repaired since a user may have unexpectedly quit access while the DB was open.

    I can repair it OK and carry on - but I have no idea why this is happening.

    I have tried changing the record locking defaults. I have tried Access 2000 DB format and Access XP DB format.

    If I use Access 2000, it seems to work (but I haven't tried it in anger).

    Any help appreciated

    Thanks

    Alan

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

    Re: Access DB corruption (XP)

    If this is a single database, you might consider splitting it into a backend database (that contains the tables) which will still sit on the server, and a frontend database (which contains everything else) for which each user has a copy on local disk. This usually cures alot of corruption problems.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Lounger
    Join Date
    Dec 2002
    Location
    Rochdale, Gtr Manchester, England
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access DB corruption (XP)

    OK - excuse my ignorance, but how do I do that ?

  4. #4
    Lounger
    Join Date
    Dec 2002
    Location
    Rochdale, Gtr Manchester, England
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access DB corruption (XP)

    It fails on Access 2000 as well. It's not my exit buttons causing the problem. I'm stumped.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access DB corruption (XP)

    Tools | Database Utilities | Database splitter will work. I view this as one of the ill-documented aspects of Access development.

    The corruption of your DB is most likely due to the Forms and functions behind the forms. In my (limited) experience, i have found that Access corrupts the data part of the database when the function part is in the same file; hence the need to split the database to (1) preserve your sanity; (2) improve performance; (3) make independent development updating the functions without mucking with the data.

  6. #6
    Lounger
    Join Date
    Dec 2002
    Location
    Rochdale, Gtr Manchester, England
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access DB corruption (XP)

    Peter,
    thanks for that. I have just finished manually splitting out the data (tables) from the rest of the database. I have put the data .mdb file on the server and the forms and queries .mdb file on everybody's C: drive. So far (10 mins testing) it hasn't corrupted yet (Ha - famous last words !!!!).

    So, did I do it right ? I didn't use the DB splitter - I imported the tables into a new file. Then, in the original, I deleted the tables and set up links to the tables in the other DB. It seems to work OK....

    Alan

  7. #7
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access DB corruption (XP)

    There is the matter of the Relationships table ~ where does it go? or where should it go? My assumption is that it is better to have it with the data. Perhaps a more seasoned DB developer could comment?

  8. #8
    Lounger
    Join Date
    Dec 2002
    Location
    Rochdale, Gtr Manchester, England
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access DB corruption (XP)

    Mine seems to be with the data - but how can you tell ?

    Alan

  9. #9
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access DB corruption (XP)

    Remember what I said about 'documentation'? <img src=/S/grin.gif border=0 alt=grin width=15 height=15> If you go to Tools | Options | View and check Show System Objects and Hidden Objects, MSysRelationships will show up, if it is in that DB. My surmise is that it should be in the back-end DB.

  10. #10
    Lounger
    Join Date
    Dec 2002
    Location
    Rochdale, Gtr Manchester, England
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access DB corruption (XP)

    Ahh - thanks. No, the relationships are in the FE bit.

    But it;s working, so I'm not touching it !!!!!

    Thinking about it, the relationships are defined by the forms and reports I want to show - so maybe they should be in the FE bit. But then again, what do I know - a mere neophyte at this stuff (although I was designing databases back in the early 70's - before most of you were born I guess)

    thanks

    Alan
    Alan

  11. #11
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access DB corruption (XP)

    chicken! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> I just split a small db using the Database Splitter. It puts a copy of MSysRelationships (and all the system tables) in BOTH dbs. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    The '70's, eh. Not only was MOST of me born before then ~ ALL of me was born before then. I probly got you by a few years.

  12. #12
    Lounger
    Join Date
    Dec 2002
    Location
    Rochdale, Gtr Manchester, England
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access DB corruption (XP)

    You're right - I just looked. The client side one is empty and the server side one (with the data) has the relationships. I guess I'll leave it alone. It's working.

    I've been in computing since 1971 (September). Done most jobs in my time. Now a "Solutions Architect" for HP. Means I take a grand view of things and tell others what to do !

  13. #13
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access DB corruption (XP)

    I took my first computing "course" in ... hmm ... 1960 seems to be a good guess. Sounds like we're at the same point of telling others what to do. I am also in the delightful position of being able to tell others where to get off. <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>

  14. #14
    Lounger
    Join Date
    Oct 2002
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access DB corruption (XP)

    Peter,

    Greetings from Richmond. You're signatur URLs left out the www.

  15. #15
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access DB corruption (XP)

    hey there virginia!

    yeah, i tend to leave out what the machine will supply. <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>

Posting Permissions

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