By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,573 Members | 3,008 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,573 IT Pros & Developers. It's quick & easy.

Database locks out other users

sueb
100+
P: 379
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 programmatically."

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.
May 3 '10 #1

✓ answered by 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.

Share this Question
Share on Google+
7 Replies


missinglinq
Expert 2.5K+
P: 3,532
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)>
May 3 '10 #2

sueb
100+
P: 379
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?
May 3 '10 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
May 3 '10 #4

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

Cool.

Thanks!
May 3 '10 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
May 3 '10 #6

sueb
100+
P: 379
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?
May 3 '10 #7

TheSmileyCoder
Expert Mod 100+
P: 2,321
I have posted my code before, it can be found in this thread:
http://bytes.com/topic/access/answer...scription-text
If you have any questions let me know.
May 3 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.