?
Solved

Why suddenly can only one user at a time open our Access 2000 database?

Posted on 2013-10-22
21
Medium Priority
?
736 Views
Last Modified: 2013-11-14
Multiple users have been able to use our MS Access 2000 DB simultaneously for many years.

Recently, only one user can access the  DB at a time.  For example, when one user has the DB open and a second user clicks on the front end DB it will no longer open.

This DB consists of a front end DB and two backend DBs.

Any suggestions?
0
Comment
Question by:PDSWSS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 9
21 Comments
 
LVL 58
ID: 39591586
Check directory security first and also those on the share if there is one.

All users need full read/write/delete privs for the directory.

Jim.
0
 

Author Comment

by:PDSWSS
ID: 39591607
By directory you mean the access DBs, correct?
0
 
LVL 58
ID: 39591716
No, not the DB's themselves, but the directory where they reside, yes.  

First user to connect will create an .LDB file, which is used to place locks.  If that file cannot be created, the DB is opened exclusive.

When the last person is out, the .LDB is deleted unless the DB has been flagged corrupt or they don't have delete priv.

Jim.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 10

Expert Comment

by:tmoore1962
ID: 39591830
Sounds like the database is opening in Exclusive Mode... Please check under

Access Options and make sure Shared Mode is selected for all users.
0
 

Author Comment

by:PDSWSS
ID: 39591833
So if the .LDB file is created when the first person opens the DB, the second person should also be able to open the DB if they have the correct permissions in place,  correct?
0
 
LVL 58
ID: 39591869
<<So if the .LDB file is created when the first person opens the DB, the second person should also be able to open the DB if they have the correct permissions in place,  correct? >>

  Yes.  The .LDB file is used by JET to place locks.  If it's not there, then it can't do that and will open the DB exclusive for the first user that connects.

 A good quick check when logged in as one of your users:

1. open explorer and navigate to the directory where the DB is.
2. Right click, select new, then text file.
3. Double click the text file.  Add some text, save it, and close.
4. Right click and delete the text file.

If you can do all that, then the problem lies else where.

With all users out, there should be no .LDB file.  If you see one, delete it.

The other thing to watch out for is a library or workgroup file with the same name as your DB and it's in the same directory.  For example:

myApp.MDW
myApp.MDB

These when opened will result in the same .LDB file name (myApp.LDB) and you'll get all kinds of weird problems as a result.

Beyond that, you want to check and make sure someone hasn't set the exclusive option in Access as tmoore mentioned and also there is no /excl switch on any shortcuts if their used.

  Usually if a setup suddenly stops working, then it's security.

  If your on a terminal services server, it's also possible to have a corrupt Office install, which would then affect all users.   The last thing possible is a corrupt DB.

  Usually though, it's the simple stuff.

Jim.
0
 

Author Comment

by:PDSWSS
ID: 39592086
Thanks for the advice. Will evaluate soon.
0
 

Author Comment

by:PDSWSS
ID: 39607289
After testing the Access DB with another user I see I did not explain the problem
as clearly as I could have.

The second person can open the front end when another user is in the DB but can not open any forms when a second person is using the DB.  Any ideas?
Sorry for not being clearer when I asked the original questions.
0
 
LVL 58
ID: 39608312
<<The second person can open the front end when another user is in the DB but can not open any forms when a second person is using the DB.  Any ideas?>>

 What exactly happens?   Error message?  Does it hang?

Jim.
0
 

Author Comment

by:PDSWSS
ID: 39608976
The user mentioned a couple of error messages. One was regarding the network. The other regarding the MS Access Jet engine...
Will need to retest to get the exact error messages.  Thanks
0
 

Author Comment

by:PDSWSS
ID: 39609411
Hangs and then 2 error messages appear on the screen. The user on the other end told me to the best of her memory - the 2 messages included the following text .

 "jet engine couldn't find a form" and "disk or network error".

Is this enough text from the error messages to  troubleshoot? thanks
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 39609636
Is everyone sharing the same FE?  They should not be.  Everyone should have their own copy.

That's not the reason per say that your having a problem all of a sudden, but it's closely related.  Users can share a FE, but it's not a great idea.

One problem is that A2000 was the first version that required exclusive use of a DB for design time changes.  If something is being done in the app like that, then it causes problems.  However with that said, it's more gracefull then what your getting ("you don't have exclusive use of the database at this time" is the usual error message), so that's still not the answer.

I would start with the test I outlined above from a users workstation to ensure that security is correct and the directory is read/write.

Jim.
0
 

Author Comment

by:PDSWSS
ID: 39613173
Users were able to
1. open explorer and navigate to the directory where the DB is.
2. Right click, select new, then text file.
3. Double click the text file.  Add some text, save it, and close.
4. Right click and delete the text file.

Now one of them is getting  a  "disk or network error" when trying to open some of the forms.
This is when no one else is using the DB. I have not seen this happen before. Any ideas?
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 39613703
Either:

1. You truely are having network issues.

2. The database is corrupt.

On #1, what if anything, has recently changed in either hardware or OS software?

As for #2:

1. Create a new MDB file.
2. Import all objects into it - Make sure you click advanced options and import any relationships, toolbars, and import/export specs
3. Save a copy of the old DB.
4. Replace the old with the new DB.

 Do this for both the front end and back end.

Jim.
0
 

Author Comment

by:PDSWSS
ID: 39620245
Still testing - One user getting network errors when another is in the DB.
She can access everything in the DB fine when no one else is using the DB.

Does not sound like corruption. Maybe something specific to her computer.
0
 
LVL 58
ID: 39621854
<<Does not sound like corruption. Maybe something specific to her computer. >>

 Does the same thing happen to other users?

 Any network hardware been replaced lately (like a hub or router)?  Any new additions to the network (new stations, Win 7 is now in the mix, etc).

 Odd problem.  But if it's been running fine for years, then it's either the DB got corrupt or it's something that affects all users, which would be the network and/or server.

Jim.
0
 

Author Comment

by:PDSWSS
ID: 39625851
Does affect all users.
Since it affects all users most likely  a change in the network or server?

Any specific possibilities?

Thanks,
0
 
LVL 58
ID: 39627382
It still not clear to me what exactly we are dealing with.  Is this a split application with a seperate front end and back end DB?   If so, are all users sharing the same front end?

And just to confirm, when one person is in the DB(s), it works fine with no errors?

No matter what, where I would start is re-building the DB(s) involved by:

1. Creating a new DB
2. Importing all objects into it from the old DB.

 If you still have the errors, then it is related to the server or network.  That can be verified by taking the DB's and placing them locally on a station.  It should work fine locally.

 You can further extend that test by using this as the new "server" and sharing the DB's from this station.   If multiple users can then use the DB, then it's the server.  If there are still issues, then it's the network.

 As to what netwrok wise, it could be anything; bad cable, router, hub, NIC in a station.   You'd need to do some network snooping at that point to figure out what.

 Again, I'd focus on the "suddenly"; what's been changed recently?  hardware? software?  If nothing, then I'd look at Windows updates that have been applied recently.

 I'd also take a look at the event logs of both client and server and see if there are any issues.

Jim.
0
 

Author Comment

by:PDSWSS
ID: 39646857
After much testing, it appears that one of the DBs was corrupt.
After replacing it with an earlier uncorrupted version from a backup and giving users their own FEs, the problem has appeared to go away.

Thanks for your expert advise.  Will assign points now.
0
 

Author Closing Comment

by:PDSWSS
ID: 39646864
JDettman: Thanks again for staying with this question even though it took a while to solve this issue.
0
 
LVL 58
ID: 39647631
Glad to hear you got it sorted out.

Jim.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question