Solved

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

Posted on 2013-10-22
21
540 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
  • 11
  • 9
21 Comments
 
LVL 57
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 57
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
 
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 57
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 57
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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 57
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 57
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 57
ID: 39647631
Glad to hear you got it sorted out.

Jim.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now