Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Hazen, ND, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Anyone else have problems with Access 2007 and multiple users?

    We've got a new Access databases in a folder where 1 user has full rights to the folder and many others have read/execute only. When the full rights user has it open, everyone else gets a message that the database is locked. Back in Access 2003 we could get into the same situation, but our security person could modify something in the associated .ldb file which allowed multiple simultaneous users. We tried the same thing with Access 2007 but have had no luck.

    Everything we've read while Googling seems to say that what we're seeing is how it works now, and that the only way to get around it is have the data in one database and each user would have their own front end for forms/reports...so if we have 15 users we'd need 15 separate front end copies.

    I just can't accept that that is correct. If it is, we'll have to move away from using Access I guess. Anyone have any insight?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Is the user with full rights opening the database in Exclusive mode? That would cause that kind of behavior. A question - what version of the database structure are you using - .MDB or .ACCDB? You imply that your database is not split into a front-end and a backend. If not, you are courting disaster. Every experienced Access developer I know will tell you to split your database. We learned the hard way when we had 80 some users trying to use the same Access 97 front-end, and it went corrupt because a workstation crashed. We have applications being used by several hundred users quite successfully where the front-end is deployed to the individual user workstation using a deployment manager and it seldom gives any trouble. The simplest solution is to use a batch file each time the user logs in and copy the master production copy to their hard drive. If you want to get more sophisticated, there are some commercial products that will do the trick and let you manage the deployment of design changes as well. You might find our tutorial on the subject of interest.
    Wendell

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Hazen, ND, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'll have to verify they're not opening in Exclusive mode, but I don't think so. We're using .accdb. Since we have only 1 user updating the database, we started out with a single database...but after encountering this issue, we split it into a backend db & frontend db...same issue though.

    Your comments echo what we've read about copying the frontend db to each workstation from a master copy. That's fine for 1 database, but we've got hundreds of databases (if not thousands) spread thruout our plants and managing making multiple copies of each of those isn't going to happen.

    With Access 2003 & .mdb we never had this issue. Very occasionally we did get a corrupted db, but like you said experience showed us to split the multiple user programs into a frontend and backend...but only a single frontend that everyone used...never had a problem with corruption. Is this issue something new to Access 2007? How about the next version of Access?

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I frankly have very little experience using the .accdb format - virtually all of our applications are still running the .mdb 2002/2003 format - but I don't find any recent mention of locking issues with 2007 in either format. There were some early reports of issues with locking when you double-clicked the database to start it. I presume you have the latest Office service packs installed (should be SP2).

    By updating the database, I presume you mean that user is updating data, not making design changes to the forms or reports. Any design change (or something that Access thinks is a design change) will lock the database, but that has been standard behavior since Access 2000. A small tip to help checking to see if someone is opening it in exclusive mode - check to see if there is a .ldb file with the same name as the database. If not, then it has been opened in exclusive mode.
    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
  •