MS Access...Access Problem

July 14, 2009 8:15 AM

Microsoft Access question: I have an Access 2007 database hosted on a server. I have 10 staff members who use the database. I can get in the database even if someone else is in it. One of my coworkers cannot. Is this typical?

I apologize that I have very basic language for this, but this is a pretty basic database. In Access 2007, should multiple users be able to get in the database at one time? We generally would only have 2 or 3 people in it at once, but this one coworker can't get in the database if just one other person has it open.

I've tried working with our IT department on this, but no one there seems to know what to do (or if anything can even be done). How do I troubleshoot this issue? Is it just something we have to deal with in Access?

posted by bibbit to computers & internet (9 comments total)

Can he get into any shared mdb? It may be that he is trying to open it exclusively. What does the message say?
posted by okbye at 8:22 AM on July 14

Can you clarify "can't get in the database"? Is he seeing an error message?
posted by odinsdream at 8:25 AM on July 14

Argh, I'm being a bad IT question poster by not having the exact wording, but yes, there is an error message - it says something to the effect of "this database is locked because user is using the database." That's not quite it, but I'll get it as soon as my coworker is back in the office in an hour.

What happens: if I am in the database (I have the database open), when my coworker goes to open the Access file, she gets the error message and she can't get in to the actual database at all. She has to find who has the database open, ask them to close out, and then she can get in it.
posted by bibbit at 8:51 AM on July 14

(make a copy of the DB before you try this; and you should also know that my knowledge of access is decent but spotty)

Try splitting the DB - Tools> Database Utilities> Database Splitter.

This takes a few seconds and will create two db files: one contains only tables (where your data are stored, or the back-end), and the other contains the queries, forms, or reports you may have (the front-end). You can then send a copy of the front-end to each user and they can run it from their respective desktops. This also allows you to continue developing work on the front end without having to kick out users.
posted by preparat at 9:08 AM on July 14

If she gets the message when simply trying to open the mdb then I'd first look under her options settings to make sure she is opening it as shared rather than exclusive. Get that out of the way.
posted by okbye at 9:20 AM on July 14

Adding a datapoint, preparat's solution works pretty well.
posted by COBRA! at 9:46 AM on July 14

Ok, I'll try that. I just checked with my coworker, and while she used to get a message saying someone else was in the database, now when she tries to get in, it just refuses to open. She'll double-click on the short cut icon she has on her desktop that goes directly to the database, and then....nothing. This only happens when another person is in the database.
posted by bibbit at 9:58 AM on July 14

A few things: 1) the best forums on the web for all things Access are at utteraccess.com, 2) Splitting the database is always a good practice, in multi-user environments, but the fact that only one of the the users can't get it make me suspect it is a user permissions issue (i.e. file-level Windows problem, not Access), 3) Access DBs can be opened "exclusively" or in the usual way. If one person opens in exclusive mode, no others will be able to open it until that person closes it. That's probably not your problem, but shadow your users and see how they're opening it. Double-clicking the icon or a shortcut to it would be preferable, as you have to use the Office Button/File menu to open exclusively.

Access is quite capable of multi-user scenarios, so long as the number of concurrent users stays fairly low. Your user group is tiny. This shouldn't be a problem.
posted by wheat at 10:58 AM on July 14

Splitting the database seemed to have worked! No problems to report so far, though I will continue to keep my fingers crossed....Thanks, folks, for helping solve a problem that lasted way too long!
posted by bibbit at 8:30 AM on July 16