Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quitting .mdb causes multiple db1.mdb, db2.mdb,etc (A2k-SR1)

    Hi,

    I have three .mdb's on a server shared by a small group of users. I have implemented Access security on all of them to prevent design changes by anyone except myself. For only one of these db's, there is some condition that causes multiple copies of the db to be created when one (or more ?) users quits the db. The files created are db1.mdb, db2.mdb, etc. which I presume are the temp db's used for compaction when a user quits because I have enabled the "Compact on close" option in all of them. All users are using a common system.mdw on the server. What is causing the unsuccesful compactions ? How do I find out which user is causing it ? Does this mean the original db is not updated properly when the 'rogue' user quits ? Any suggestions appreciated.

    Thanks,

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

    Re: Quitting .mdb causes multiple db1.mdb, db2.mdb,etc (A2k-SR1)

    I'm still stuck with Access 97, so I may be wrong here. But compacting a database while it is in use by another user seems impossible to me, so enabling the "Compact on close" option in a multi-user database shouldn't be attempted. If it caused no problems with the other databases, maybe you were lucky that there was only one user at a time.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Quitting .mdb causes multiple db1.mdb, db2.mdb,etc (A2k-SR1)

    Compact on close is only going to happen when the *last* user quits. It won't run if anyone else has the database open because it requires an exclusive lock. Otherwise, it just gets skipped.

    The multiple "no-name" databases are actually the compacted copies of the database that Jet couldn't rename because it couldn't delete the original database, and I've seen this occur with local databases as well, so it seems to be a "feature" of A2k/Jet 4.0, not just of compact on close. I've even seen it using JetComp.

    What happens is that the compact process compacts the database into another database (db1.mdb, etc.) and then deletes the original and renames the copy. This always happens in a compact, even if you can't see it. For some reason and on some machines, it doesn't always manage to delete the *original* database and so it isn't able to rename the copy.
    Charlotte

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

    Re: Quitting .mdb causes multiple db1.mdb, db2.mdb,etc (A2k-SR1)

    Building on what Charlotte said, I'd suspect it has to do with rights and privileges in the folder containing the database. Users need full rights to this folder, including the Delete privilege.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Quitting .mdb causes multiple db1.mdb, db2.mdb,etc (A2k-SR1)

    <<Users need full rights to this folder, including the Delete privilege.>>

    Which is what always makes me wince when I deploy a Jet back-end database. (So we use SQL Server whenever we can.) We also try not to use compact on close if we can avoid it. Then we don't let the user actually delete the database - if you do, they can destroy a whole days work. In most cases we actually have a separate process we run that does the compact on a scheduled basis, run by a user that has the appropriate priviledges. The only place we typically use compact on close is where we are creating a significant number of temporary tables in a front-end database located on the user's workstation. Even then it can be a pain, as it may take 20 or 30 seconds to close. This probably doesn't help much with your situation, but it may be of value in the future.
    Wendell

  6. #6
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quitting .mdb causes multiple db1.mdb, db2.mdb,etc (A2k-SR1)

    Hi,

    Thanks for the suggestions.

    Charlotte mentioned that the *last* user would need an exclusive lock. This might be a problem because I have prevented any of the users except myself from being able to "open exclusive". I don't know if them being able to exclusively lock records is different from being able to open the db exclusively ?

    The multiple db1, db2, etc files are larger than the parent db and I would therefore assume not compacted versions, but some intermediate step ? It seems that the record count in some of the tables that I have looked at in the temporary db's is the same as the original, so hopefully I am not losing information when the compaction fails to complete properly.

    As far as users' rights go, all domain users have full rights to this particular folder (scary) so that doesn't seem to be the problem.

    I guess I'll carry on backing up the .mdb's and deleting the db1's <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Quitting .mdb causes multiple db1.mdb, db2.mdb,etc (A2k-SR1)

    I think you've identified the problem in this case. When Access does a compact on a database, it must take exclusive control in order to compact the database. However you've set the users so they can't do that. I would remove that restriction and see if that doesn't fix the problem. Either that or I would remove the compact on close, and setup a regular schedule where you do a compact and repair once a week. Access 2000 databases do grow fairly rapidly if you are creating lots of temporary objects, but we usually don't see a problem with weekly schedules.
    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
  •