Database locks out other users

[sueb]

I maintain a small database that tracks our work events, accessed by a maximum of nine users. Periodically, the database allows only a single user (sometimes two, which is even more puzzling) to open it, after which any attempt to open it results in an error message claiming something like "a user has the database open exclusively, and it cannot be manipulated programmaticall y."

What seems to work is to have all users close it. Once this is done, anyone can open it, and the problem doesn't recur that day.

This typically happens in the morning, when we all begin to come in for work. During the day, the database is open by multiple users at once, usually for the entire day.

I'm at a loss to know what's going on. Thanks for any advice.

[missinglinq]

The first question that has to be asked is if this is a split database, with the tables/data in the back end, everything else in the front end, and a copy of the front end on each users hard drive?

Having multiple users using a single, non-split database is a sure fire way to corruption as well as all kinds of other problems, including the one you've documented.

Welcome to Bytes!

Linq ;0)>

[sueb]

No, it's not split. I think it was created originally with an extremely simple thought in mind, but has grown in scope since then. The way it gets used tends to keep users off the same record at a time (the records are flat), so this hasn't been a problem up until recently.

Probably, it would be a good idea to split it, as you say, but I haven't ever done that. Is it just a matter of sticking the form (there is just one!) on their machines pointing to the data?

[TheSmileyCoder]

Access has a built in "splitter" wizard. Basicly what it does, is to place all the tables into a separate database (the back-end), and the forms/queries/reports into another (the front-end) with links to the tables in the back-end. You can then distribute the front-end so each user has their own front-end, and each front-end can then support 1 user, so with 9 front-ends, each user could be using the database at the same time.

Remember to take a backup before splitting it.

[sueb]

Huh. Well, THAT was easy! So each user just opens the "plain" file (not the "_be" one) and that's that?

Cool.

Thanks!

[TheSmileyCoder]

Yes. Access will still prevent users from modifying the same record at the same time.

The real challenge is when you need to update the frontend (design changes), but there are ways around that as well, I have for instance made an update function in my databases that automatically rolls out new frontends, when I make them.

[sueb]

Now THAT would be handy to have, as I'm really trying to upgrade this thing, and currently I have to work off-hours so that everyone's out. Would you be willing to share that update function? Or guide me in creating my own?

[TheSmileyCoder]

I have posted my code before, it can be found in this thread:

http://bytes.com/topic/access/answers/883510-how-pick-up-form-reports-property-description-text

If you have any questions let me know.