• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7121
  • Last Modified:

Access 2003 - Do not have exclusive access to the database.....

Hello everyone.  I am hoping that someone has a simple answer to this problem.  I have an Access 2002 front end to an Oracle database.   The tables are linked into Access and we use ODBC to connect to Oracle.  The .mde file is located on 3 shared drives and potentially accessed by hundreds of users.  The problem is that some of the users have been upgraded to Microsoft 2003 and they now receive an error when they try to open the application - You do not have exclusive access to the database at this time.  If you proceed  to make changes, you may not be able to save them later.   They have to click on OK dozens of times to clear the error and they are never able to use the file.  Everything I have read says that you can no longer make design changes in a shared mode, but this is an .mde file and users are only entering data, no one is permitted to make design changes.  The file name for the application is cms.mde and the locking database is cms.ldb, they are both in the same location.  Can this be the problem?  If so, what can I do to fix it?  

Any help is appreciated.

Thanks,

Josette
0
jkoneil
Asked:
jkoneil
  • 15
  • 10
  • 4
  • +6
2 Solutions
 
Jim P.Commented:
Are you building any tables, queries or forms dynamically.  That is the cause.  You need to give each user an individula front-end.
0
 
puppydogbuddyCommented:
Hope this link helps:
     
         http://www.source-code.biz/snippets/vbasic/10.htm
0
 
WillibobCommented:
I don't use Office 2003 but it would appear that Access 2003 may be attempting to upgrade the Access 2002 database to an Access 2003 database.

Do you have the mdb file that the mde file was based on? If so, open the mdb file in Access 2003 and recreate the mde file. Then see if Access 2003 users can access it.

If they can then you may have to run with a 2002 mde and a 2003 mde as I am not sure if Access 2002 will open an Access 2003 mde (but I wouldn't expect that it would!!).

Hope this helps

Bill
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
jkoneilAuthor Commented:
All of the forms and queries are built in the .mde, they are not built dynamically.  They open when the users login and click certain buttons/menu items.   I do have a function that runs on the Main menu screen that populates a field with the users id.   Is there a way to ensure that the user has the most recent front end when updates are released?  I would need to block access to the database if they are not using the most recent screens.  

I will look at the link.

I do have the .mdb so I will try creating a 2003 executable.

I just spoke to the user that is having a problem and she is not running Windows 20003.  She is running Windows 2000 vs 5 service pack 4.  
0
 
jkoneilAuthor Commented:
Thanks for the link, but it points to a different error.  
0
 
WillibobCommented:
When you said that some users had upgraded to Microsoft 2003, I assumed you meant Microsoft Office 2003. Was I correct in this assumption?

When your users load the mde file, are they actually loading the file from the network share (through a shortcut) or is the network share the location where they get updated from?

To ensure that the latest version of the front end is being used create a local table in the front end with a single field to store a version number, in the Oracle database create a table to also hold a version number. When your front end opens, check that the version stored in the front end matches the version in the back end. If it doesn't then popup a message to tell the user that their front end is out of date. Each time you create an updated front end, increase the version number in both front and back end 'version' tables. Any existing front ends will no longer work until the user updates.

By the way, the ldb file will always be created in the same location where the mdb or mde exists.

Bill

0
 
jkoneilAuthor Commented:
You were correct in the assumption that they upgraded to Microsoft Office 2003.

They load the .mde from a shortcut on the network.

I thought I read that having a .ldb and .mde with the same name in the same location could cause a problem.  

Thanks,

Josette
0
 
WillibobCommented:
If the mde file is opened exclusively then the ldb file is not created.

If the mde file is opened as shared then the ldb file is created by Access in the same location as the mde file and Access uses this file to keep track of who is using the mde file. When the ldb file is present you can open it in Notepad to see who currently has the mde open (the users shown in the ldb file is not always a 100% accurate list).

So, basically, you have no control over the creation of the .ldb file. Access controls its creation i.e. when & where and also its deletion (under normal circumstances but there are occassions where you may need to remove the .ldb file manually after a crash).

On the subject of the .ldb file, if users don't have permissions to create files in the same folder as the .mdb or .mde then the database will only open exclusively and will support only one user. Subsequent users will get an error when attempting to open the file. You may want to check the permissions on the network shares to make sure they haven't been modified.

I believe that the issue your experiencing is as follows:
Your .mde file is an Access2000 or Access2002 database.
I don't think Access2003 can open the database without making a modification (I'll double-check this) but it can't make the modification because the file is an mde.

I'm fairly certain that re-creating the .mde file using Access2003 should resolve your problem but I'm going to investigate further and post my findings.

Bill

0
 
Jim P.Commented:
IIRC, if the mdb/mde is launched from a shortcut, the working directory of the shortcut controls the placement of the ldb.
0
 
jkoneilAuthor Commented:
Thank you for the information about the .ldb.  
I will check with our Systems division to see if they modified the permissions on the drive that we use for the application.
0
 
WillibobCommented:
According to Micorsoft, Access2003 will open an Access2000 and Access2002 database without any modifications to the database unless they contain data access pages. I'd still check this is true in practice for mde files by re-creating the mde using Access2003.

Have you tried re-creating the mde using Access2003 yet jkoneil?

Bill
0
 
jkoneilAuthor Commented:
Unfortunately I do not have access to an Access2003 PC.  I am focusing on the other areas since the person having the problem is not one of the people who upgraded to Windows2003, she is using Windows2000.
Thanks,
Josette
0
 
WillibobCommented:
But is she using Access2003 or Access2000/2002?

There isn't a desktop version of Windows2003, it would be XP.

So just to clarify, what operating system and what version of Access are those that are experiencing the problem, using?

Bill
0
 
WillibobCommented:
If your users have been upgraded to Office 2003 then can you not use one of their PCs as they will have Access2003 installed as part of the Office package wouldn't they?

Bill
0
 
jkoneilAuthor Commented:
The people experiencing the problem are running Access 2000/2002 on Windows 2000.  Originally I thought they had been upgraded to WindowsXP, Office 2003 but that is not the case.  

I can not use one of the PCs of the people who have been upgraded, they are in California and I am in Virginia.

Thanks,

Josette
0
 
WillibobCommented:
Have they always accessed the file in the same way?

Has it always been accessed as an mde via a shortcut to a file on a network share? Or has something else changed?

What are those that are NOT experiencing the problem running?

Bill
0
 
Leigh PurvisDatabase DeveloperCommented:
I may be late to this party - and not particularly the bringer of good news - however, here's my thoughts.

Firstly (and just for interest really now - as it's beeb identified that this isn't the issue) Access 2003 shares a file format with 2002.  It is technically now known as the 2002-2003 format.
Access 2003 *should* be able to run mde's created in 2002.  But it isn't at all guaranteed.  There are other things (usually reference based) that can come in to play with mdes.  More often than not - a 2000 format mde would run under 2003.  But again - not necessarily.
(mdb files are less restricted in this way).
Compiling the mde's on a different pc may improve portability (just for your ref) :-)

On to the main crux of the issue.
You have this line in your original question
"The .mde file is located on 3 shared drives and potentially accessed by hundreds of users. "

Any shared Access front end is prone to concurrency problems.
The generally accepted method of distribution has a FE on every users pc - and it links to whatever BE file/server.
I know - the initial reaction to that will be one of horror.  "A logistical nightmare!"
Well - perhaps, but there are commonly employed means of running updates as required - once the installs are in place.
For example - Access itself needs to be installed on each users machine doesn't it?
Well - the UI and code is normally there too.  Sharing the file can lead to problems (with exclusivity - rather than data locking ones).

Jim touched on something earlier I believe.
The shortcuts.
Do you launch these right out of the box pointing at the mde file?

I've known much better results from the (also standard) shortcut target of
"Path to MSACCESS.EXE"  "Path to mde file"
rather than just the path to the mde.
Somehow some users must be opening the mde file exlcusively.
(It's also possible it could be a permissions issue - but you'd know that and have to check it, making sure nobody is doing that - or even *can* do that)?
Anything you do to help prevent that exclusive opening can only be a good thing.
0
 
jkoneilAuthor Commented:
They have always accessed the file the same way.

It has always been accessed as an mde via a shortcut to the .mde.

Those not experiencing the problem are running Windows 2000.

The first thing I asked when the users reported the problem was whether or not anything on their PC had been modified.  All said no.    The mde file itself has the appropriate permissions set.  I am waiting to hear back from Systems about the drive and user permissions.  

I do launch the shortcuts directly to the mde file.  
0
 
Leigh PurvisDatabase DeveloperCommented:
Do you want to try with the alternative path launch?

(I assume you install all Office apps to the same location on each users pc - so the shortcuts would become the same full path).Just try it on a couple of problem machines - and alternative shortcut.

The problem could be a machine you're not aware of - that opens the mde successfully - but exclusively.
This machine is then preventing the others from accessing it.

But indeed - see what comes back from the permissions side also.

Virtually all the guys and gals here will shout from the hills that individual FE files on each user's pc is the way to go.
Wether you've had it running without that before or not. :-(
0
 
jkoneilAuthor Commented:
Sorry for not getting back to you sooner.  
Will the alternative path launch help to prevent the users from logging in exclusively?  Is there a way to prevent additional shortcuts from being created once the file is on the shared drive?



0
 
Leigh PurvisDatabase DeveloperCommented:
It should make a real difference yes.
As far as not making more shortcuts - well not really unless it's done with OS user permissions.
But it's unlikely that users will go looking for the mde file and making shortcuts to it isn't it?
(or is it? ;-)
0
 
WillibobCommented:
Hi Josette

This thread is becoming a little confusing although maybe just for me ;)

If you have time, could you please list the following:

For those experiencing the problem:
   Operating System:
   Version of MSOffice:
   Version of MSAccess:

For those NOT experiencing the problem:
   Operating System:
   Version of MSOffice:
   Version of MSAccess:

Lastly, the version of the MDE file (i.e. Access97, Access2000, Access2002 or Access2003):

Thanks

Bill
0
 
jkoneilAuthor Commented:
Sorry to cause confusion.  I originally thought the user was using Access 2003 but she is not.  They are just scheduling the upgrade now.  

For those experiencing the problem:
   Operating System:  Windows 2000
   Version of MSOffice:  Microsoft Office XP
   Version of MSAccess: 2002

For those NOT experiencing the problem:
   Operating System:  Windows 2000
   Version of MSOffice: Microsoft Office XP
   Version of MSAccess: 2002


0
 
WillibobCommented:
Thanks Josette.

Do you know the version of the mde file?

Are we saying then that nothing has changed on the users machines at all but that numerous users simultaneously experienced the same error?

Can you determine if the users experiencing the problem are accessing the file from the same share (1 of the 3)?

Have you had any word back regarding the permissions?

Just to clarify, this error normally only occurs when the database has been successfully opened in shared mode and then 1 or more users attempts to make a design change which is obviously not possible in an mde. This is why I thought the database was possibly being upgraded by a later version of Access.

Bill
0
 
jkoneilAuthor Commented:
The .mde file is Access 2002.

According to the users, nothing changed on their PCs.  It didn't happen all at once, first there was only one user having a problem and then a week or so later another and then a few days later someone else.  So far of all of the users only 4 have reported the error.  Everyone else is fine.  All users experiencing the problem access the shared file on the drive in California.  All users in CA access the same file and that is where the majority of the users are.  The other 2 are in VA and have only a handful of users.  

The only possibility that I see as far as making a design change is to a query since nothing else in an mde can be modified.  

I have not heard back about the permissions yet.  

Josette
0
 
Leigh PurvisDatabase DeveloperCommented:
My recommendation stands :-D

I know it sounds like a nightmare - but especially of some users are accessing this remotely - there's ample oportunity for unwanted locking to come along.

Your line in your question "potentially accessed by hundreds of users" these aren't concurrent are they?
If so then I'd maybe have to say (OK regulars - don't faint in shock at this statement from me) - then this probably isn't a job for Access!
:-O
0
 
jkoneilAuthor Commented:
Just thought I would let you know that I tried changing the shortcut from targeting the .mde directly to targeting Access and then the .mde.  That made things worse.  Only one person could open the file through the shortcut, if someone else tried they received and error that the file was already in use.  

Changed from X:\CMS\cms.mde to "C:\Program Files\Microsoft Office\Office 10\MSACCESS.EXE" "X:\CMS\cms.mde"

Moving out of Access to some other front end is probably the ultimate solution but for now I have to try to fix what we have.  The application is being used by the entire organization - which is where the hundreds of users comes from - but I do not think that they will all have to access it at exactly the same time.  There are probably 20-30 people on at any given time but that number is on the rise.

Thanks.

Josette
0
 
peter57rCommented:
I agree with Leigh (Purvis).  Sharing a front-end mdb is bad enough but with an mde and its hard coded references it is almost  inevitable that you have problems.
This site is littered with questions about this issue and I don't believe you will find any developer who would support sharing a front-end file.
I am reminded of a saying relating to Master Documents in MS-Word....
'master documents exist in only two states - corrupt or just about to become corrupt'.

You certainly should not even contemplate using different versions sof Access to open the same front-end.

Pete
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Josette,

  Just to toss in another comment; this is the worst way to run an Access app.  By sharing the MDE, you're pulling a lot over the network that you don't have to.  Every time a user opens an object (Form, report, table), your pulling all that over the network.  It's easy enough to install a FE locally with just a batch file and do a version check at startup.  Also, wizards may not work properly if your using them.

  As has already been mentioned, check the permissions on the directory where the MDE is.  All users need full rights (read/write/delete) for locking to work properly.  Database also needs to be opened shared.

  Also make sure you don't have a MDB with the same name in the same directory.  If you do, an attempt to create a locking file for that will cause a problem when the MDE is opened.

  Last, if your going to have different versions of Access, there is nothing saying you can't have multiple versions of the FE.  I've always tried to make sure that a database is always in the same version as the latest installed version of Access on the client.

 Apologies to all for anything I might have repeated.  I don't have time to look through the whole thread right now.

Jim D.
0
 
jkoneilAuthor Commented:
I understand that sharing the front end is not the ideal solution.  I read through the site before posting the question.  Unfortunately, I do not have final say on how the users will access the file.  I recommended distributing the front end and was asked to exhaust all other possibilities first.  

I do not have an .mdb on the shared drives, only an mde.  

I am waiting to hear back from Systems about the permissions.  They said the permissions on the drive have not changed but the users having the problem may need to be put back in the group.

I would like to assign points to Leigh (Purvis)  and Willibob (Bill).  Information provided earlier in the thread from Bill was able to help me resolve another problem with exclusive access and Leigh's recommendation of distributing the front end will be the eventual solution.   Please let me know how I can assign the points.

Thanks,

Josette

0
 
Jim P.Commented:
The help section on Closing Questions: http://www.experts-exchange.com/help.jsp#hs5
0
 
WillibobCommented:
Just one more thing Josette,

Have you tried Compacting & Repairing the MDEs (remeber, you'll need exclusive access to the file to do so)? If the one in CA gets heaviest use then it may be getting corrupted.

Alternatively, it may be worthwhile copying the file which is on one of the shares in VA to the drive in CA? (Backup the one in CA first of course!!)

Regards

Bill

0
 
jkoneilAuthor Commented:
Bill,

I compact and repair the .mdes regularly to improve performance.  I tried replacing the CA version with the master copy that I keep in case one of the files needs to be updated.   It did not fix the problem.

Systems is researching the problem now.  They placed an audit on the file and I was able to locate one of the users that was opening the file exclusively.  Systems is checking her PC now to see why Access is trying to open the file in exclusive mode.

I will post another question requesting the steps for creating a batch file to install the new version of the front end.

Thanks,

Josette
0
 
jkoneilAuthor Commented:
Hopefully the points were assigned correctly.  The assisted answer and accepted answer are reversed.  

If the mde file is opened exclusively then the ldb file is not created. <------------ this is what I wanted to accept as the answer.  We found a user that had the application opened but an ldb had not been created.  She received the error message if someone else was using the file when she tried to open it.

0
 
Jim P.Commented:
Was/is she launching from a shortcut.  It could be the working directory for the shorcut is pointing to the wrong plus.
0
 
jkoneilAuthor Commented:
She is launching from a shortcut.  We checked and the shortcut is pointing to the correct place.
Thanks.
0
 
HarbourGroupCommented:
Here's an alternate solution...
I was encountering this same error message with a single user.  If she was the first to open any of our db's, all other users were locked out.  If someone else had the db opened before she did, this error resulted.  Found out that the user had installed the Avery Label add-in for MS Office.  Once I disabled the add-in, the problem went away.  
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Found out that the user had installed the Avery Label add-in for MS Office.  Once I disabled the add-in, the problem went away.  >>

  Thanks for posting that!

Jim Dettman
MS Access PE
0
 
ANAHostMasterCommented:
Had to laugh! I experienced the same problem with a single user. I read through all of the above exchanges, and at the bottom was my answer. This user had also installed the Avery Label add-in and that is what caused the problem. Sometimes, it pays to scroll to the bottom first! Thanks for everyone's input!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 15
  • 10
  • 4
  • +6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now