If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > MS Jet Database Engine cannot open file.. It is already opened exclusively by another

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2008
Posts: 7
MS Jet Database Engine cannot open file.. It is already opened exclusively by another

I am using Access 2003 SP3. Two workstations are shared. The back end is loaded on machine 1, with a front end loaded on each machine. If I compact the tables on machine 1, then attempt to open the database on machine 2, I received the following error:

The MS Jet Database Engine cannot open the file ' ' It is already opened exclusively by another user, or you need permission to view its data.

The file referenced is the back end tables from machine 1. After much troubleshooting, it seems to be related to the compact. The entire folder is shared, all permissions given, wide open. If you copy the tables to another file name, then you can get in. The work-around I am having the client use is to perform the compact from machine 2.

But, of course, they want to know why? and so do I? Any ideas what might be causing this? The only search results I can find on this error have to do with SQL or ASP, neither of which is being used here (except small SQL statements in queries, etc.)

Thanks for your help.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: metro Detroit
Posts: 633
is the .ldb file being deleted after the compact?
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2008
Posts: 7
I'm not sure if the .ldb is being deleted. Is this something that the user should do manually after compacting? Usually that file just goes away when the tables aren't open, correct?
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2008
Posts: 7
One other item to note is that I cannot recreate this on my setup at my office. I have 2 Dell machines with Office 2003. Simply using a Shared folder to store the tables in. Unable to recreate problem after compacting or in any situation. Which would have led me to believe it had something to do with windows permissions, something I am not overly familiar with.

Also, no db security is in place.
Reply With Quote
  #5 (permalink)  
Old
Moderator
 
Join Date: Dec 2004
Location: Madison, WI
Posts: 3,925
Not sure I would go the route of having a separate frontend interface on each individual computer verses a central frontend on the server somewhere (and have a routine which clones the frontend and opens the cloned file or just have the users use 1 frontend mdb/mde) but I don't want to tell you how to set it up. Regarding the compacting and *.ldb file...if a user has a frontend open (which has linked tables to the backend), and you attempt to compact/repair the backend, you'll most likely get an error that the backend is locked. I wouldn't recommend attempting to compact/repair the backend while a user has the frontend open. If you look at the folder the backend is in and see an *.ldb file, someone has the frontend open which has the linked tables to that backend or has the backend open itself (or the backend is in a corrupted state.)
1. Make sure everyone using the frontend also has read/write permissions to the folder the backend resides in (this can cause problems and leave an *.ldb file hanging.) You never want to see an *.ldb file in the backend folder if everyone is out of the frontend and backend. This means the backend wasn't closed properly or is in disarray (or someone has it/the frontend open). But users need read/write permissions to the folder (so an *.ldb file can be created when the user opens the frontend that links to the backend) and read/write permissions to the backend mdb file. The way it works is this....When a user opens the frontend on their machine, a *.ldb file is created for the frontend and a *.ldb file is created for the backend. You'll see the *.ldb file in the same folder the *.mdb file is in. For example, I open the frontend called: MyFrontEnd.mdb on my machine in FolderX which links to a backend called: TheBackEnd.mdb on the server in a folder called CentralFolder. I should see MyFrontEnd.ldb in FolderX and TheBackEnd.ldb in CentralFolder. When the user closes their frontend, their *.ldb file dissappears and the *.ldb file for the backend may or may not dissappear depending on if all the other frontends are closed as well (and again, no one has the backend mdb open.) If all is good and done, you shouldn't see any *.ldb files hanging out there when everyone is closed out.
2. Make sure the backend has enough disk space. If not, when compacting/repairing you could very easily trash the mdb.
3. Make sure there's a good network connection and good connection speed for all users to the backend folder. Nothing worse than a user constantly locking up the backend because their connection via the frontend is slow.
4. MAKE BACKUPS of your backend/frontend (this should be a given - copy/paste.)
5. Don't put code in the frontend to automatically compact/repair the backend on a network drive - this is just silly to me. Check also in Tools - Options - General that Compact on Close is NOT checked (a dumb option to even have if you ask me.).
6. Check to make sure in the Tools - Options - Advanced tab (I believe it's the same for all Access versions) - the default open mode is set to shared verses exclusive (and No Record locks is selected.)
7. Refresh the linked tables in all the frontend interfaces (Tools - Database Utilities - Linked Table Manager.) If you made table changes and didn't refresh the tables in one of the frontends, this could cause problems.
__________________
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Last edited by pkstormy; 01-04-08 at 18:24.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2008
Posts: 7
To clarify, my set up I think is a normal one for most multi-user settings. Store the back end tables on the server (in this case the lead PC) and then local front ends for each PC, correct? This has been how I have set up countless applications for users and never encountered a problem yet, but please tell me if you are seeing something incorrect. In the case of a peer-to-peer network, I have to do it this way as the drive references would not be the same for each user.. user #2 has a drive mapped to user #1's hard drive where the tables are stored.

As for the compacting, the user is not doing it when others are in. And the user is not getting a locked message. To my knowledge, the .ldb is not still hanging out there after the compact.

Tons of disk space. Backups are made routinely. No code to auto compact. All table links are refreshed. Default open mode is shared.

Everyone has read/write permissions to the back end folder and can get in fine. It is only after compacting the tables from the machine they are stored on that the other user cannot get in and gets the error: The MS Jet Database Engine cannot open the file ' ' It is already opened exclusively by another user, or you need permission to view its data. I then have to make a copy of the tables and then people can get in.

My current work around is the have user #2 do the compact from their PC. This is a workable solution, but I'm wondering if anyone has seen this error before (when not related to ASP). My client is wondering why this is happening and I have no real answer for them.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Location: Chicago, IL
Posts: 1,312
How does User #1 open the database? With a shortcut? I think you can specifiy a command line argument to open the database exclusively. Pretty wild guess, but it seems you have covered most of the bases.

After that I would start checking service packs and versions to make sure the two systems are identical. You might also consider repairing User #1's Office install. Again, a stretch, but my experience has been, if two systems using the same database act differently, then it must be a difference in the systems.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jan 2008
Posts: 7
Thanks for your suggestions dckunkle. Both PCs are the same SP, etc. I did have my client go through this.

I've tried opening with a shortcut or going through Access and actually opening the file directly. I'm not sure the exclusive setting would help as it is the back end tables causing the problem, which I have to leave for others to open, know what I mean?

I agree something with the systems must be different than mine. I'm thinking it might have something to do with permissions. I have a peer-to-peer at my office but cannot recreate ever.

Any google I do on this error message leads to ASP websites, so I'm hoping someone has heard of it happening on a plain little 'ol Access application such as mine. There's really nothing complicated in this app, very straightforward with 2 users. very frustrating, but at least I have a work-around. any help would be appreciated Thanks!
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Location: Chicago, IL
Posts: 1,312
With regards to the Exclusive argument. How does User #1 open the backend? Does he/she navigate to the file and double click it? Or do they use a shortcut? In the shortcut properties you can specify that the database open exclusively. What I am suggesting is that they have a shortcut that opens the database exclusively and they do't realize it.

But the more I think about it the more I think the compacting is not finishing properly. What happens if you compact a copy of the database using #1's system? Can #2 get in? Is it all databases that #1 compacts or just your back end? If #1 compacts then shuts down, can #2 get in? What happens if #1 compacts a database that is stored on #2's system?

Just a couple of ideas to try.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Jan 2008
Posts: 7
User #1 uses a front end as well, with tables linked to the backend. Noone really opens the backend unless they are going to perform a compact.

Both User #1 & #2 use a shortcut to a local front end, each stored on their individual hard drives. User #1 has the tables. If User #1 performs the compact and closes out, User #2 can no longer open her front end. She gets the error. User #1 is not affected at all.

If User #2 compacts (navigates to User #1's tables and performs the compact from her workstation) then User #1 & User #2 can use their respective front ends with no problem.

I believe it is all databases, but they only use this one. In the early stages of troubleshooting this problem, I recreated the tables many times and always the same results after awhile of use. I finally narrowed it down (I think) to when the compact happens, because they wouldn't be having the problem after I created a new tables file. Then, after awhile they would.
Reply With Quote
  #11 (permalink)  
Old
Moderator
 
Join Date: Dec 2004
Location: Madison, WI
Posts: 3,925
Check the MDAC version on both PC's. Reinstall the MDAC on both PC's then try compacting/repairing.
__________________
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Jan 2008
Posts: 7
Thanks for the suggestion. Can you tell me how I uninstall the MDAC before reinstalling?
Reply With Quote
  #13 (permalink)  
Old
Moderator
 
Join Date: Dec 2004
Location: Madison, WI
Posts: 3,925
Uninstall it? Try looking at Control Panel - Administrative Tools - > Data Sources and see if there's anything there but I'm not sure if you need to uninstall it though.

I'd just download the latest version and install it.

http://www.microsoft.com/downloads/d...displaylang=en

Note on the above link: "This release does not include Microsoft Jet, the Microsoft Jet OLE DB Provider or ODBC driver, the Desktop Database ODBC Drivers, or the Visual FoxPro ODBC Driver."

So you may need to re-install from a different MDAC source (try the Office/MSAccess CD also.)

Also....
http://www.microsoft.com/downloads/d...displaylang=en

(plus other MDAC sources). My guess for your problem though is that it has something to do with the MDAC since it seems to be specific to compacting/repairing or refreshing linked tables (I'm curious though if you do Tools -> Database Utilities -> Linked Table Manager and refresh the linked tables what happens and if you get the lock error or if it's only when you compact/repair.).
__________________
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Last edited by pkstormy; 01-09-08 at 16:01.
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Oct 2004
Posts: 3
I have exactly the same problem, having just re-installed Dreamweaver (from Studio MX 2004), along with Access 2003 within a Windows XP pro environment. Cannot get any database linked page to work (same error as yours). So I tried creating the very simplest of test websites using a single table database (no front end) containing just one field and with a single ASP page to display the data.

Sadly, the same problem when I tested it.

My next approach is to try it again after installing MDAC (despite MDAC reportedly being unnecessary with my configuration)
Reply With Quote
  #15 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,218
Quote:
Originally Posted by idsllc

Also, no db security is in place.
ms Access workgroups security is ALWAYS in place.. its just that is set at a level that allows anyone to use the db, usaully with full administrator privilege.. soemhtign that isn't a smart call in my books (unless you have the one site that has reseponsible, knowledgable and security aware users [thought not!])

just a dumb question.....
when you compacted and reapired the db. did you close the current isntance of Access? that may cause lockouts as you need exclusive rights to the db to do the compact and repair... it may be that access is holding on to the excliusive lock.. this is waht I think is being inferred by deleting the lockfile

Id be stronlgy tempted to deploy the mdb as a shortcut using an appropriate command line switch to make sure its opening as you expect
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On