Do Not Have Exclusive Access to DB error msg (1 Viewer)

mkdrep

Registered User.
Local time
Today, 11:25
Joined
Feb 6, 2014
Messages
176
I don't expect anyone here to fix my issue, I'm just hoping for some guidance as to what direction I should look to fix the issue.
I have a database which works fine when only one person is working in it on my office network. The issue described below does not happen when only one person is in the database. The issue only arises when more than one person is working in the database. The curious part is that the issue ONLY happens when I click on (2) buttons in the entire database application

Here is one example: When I click on the LEFT button entitled "All Job Types x Mkt Area -Form" (as shown in Market_Area_Buttons.jpg), the error msg shown in "Access_Error_Msg.jpg" pops up and I have to click through it 3 times until the underlying form (Market_Area_Form.jpg) opens up. Curiously, this does NOT happen when I click the RIGHT button; the underlying form (Market_Area_Rows.jpg) pops open immediately. The data and query used in both forms is identical.

I have read a few other posts on this topic and each individual who has access to my database has their own copy of MS Office 2016 residing on their computer. The main program resides on our office network and they access it using their local MS Access 2016 copy.

Any and all suggestions as to where I might start to look to fix this error would be appreciated. Thank you in advance for any suggestion
 

Attachments

  • Do_Not_Have_Exclusive_Access_Error_msg.jpg
    Do_Not_Have_Exclusive_Access_Error_msg.jpg
    25.6 KB · Views: 212
  • Market_Area_Buttons.jpg
    Market_Area_Buttons.jpg
    10.3 KB · Views: 193
  • Market_Area_FORM.jpg
    Market_Area_FORM.jpg
    79.6 KB · Views: 203
  • Market_Area_Rows.jpg
    Market_Area_Rows.jpg
    18 KB · Views: 212
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 08:25
Joined
Oct 29, 2018
Messages
21,358
Hi. Sounds like you don't have a split configuration. Each user should have their own copy of the front end in their individual machines, and only share the data in a single back end file on the network.
 

mkdrep

Registered User.
Local time
Today, 11:25
Joined
Feb 6, 2014
Messages
176
Hi. Sounds like you don't have a split configuration. Each user should have their own copy of the front end in their individual machines, and only share the data in a single back end file on the network.
I have read a few other posts on this topic and each individual who has access to my database has their own copy of MS Office 2016 residing on their computer. The main program resides on our office network and they access it using their local MS Access 2016 copy.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:25
Joined
Oct 29, 2018
Messages
21,358
I have read a few other posts on this topic and each individual who has access to my database has their own copy of MS Office 2016 residing on their computer. The main program resides on our office network and they access it using their local MS Access 2016 copy.
Hi. I don't mean a copy of MS Access 2016 on their machines (no one will be able to use your database without it anyway). I was referring to the name of your file. For example, if the name of your database is MyDatabase.ACCDB, then I was saying each user should have a copy of MyDatabase.ACCDB on their local machine and not share the single copy from the network. Check out this Wiki article:
 

mkdrep

Registered User.
Local time
Today, 11:25
Joined
Feb 6, 2014
Messages
176
Hi. I don't mean a copy of MS Access 2016 on their machines (no one will be able to use your database without it anyway). I was referring to the name of your file. For example, if the name of your database is MyDatabase.ACCDB, then I was saying each user should have a copy of MyDatabase.ACCDB on their local machine and not share the single copy from the network. Check out this Wiki article:
Ok. What I don't understand, though, is that this issue has only started to occur in the last few months. The "setup" I have been using, i.e., database on the network and each person has a copy of MS Access on their computer to access the database on the network, has worked literally for many years. I don't understand why the issue is popping up now AND why it only occurs in just two instances. Very curious wouldn't you say? :)
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:25
Joined
Sep 21, 2011
Messages
14,044
1. Is anyone opening the DB exclusively?
2. Go out and buy a few lottery tickets. You are one very lucky man. :D
 

Micron

AWF VIP
Local time
Today, 11:25
Joined
Oct 20, 2018
Messages
3,476
IMO the answer to your original post is that your db is likely corrupted. Anyone here will tell you that by sharing a single db, you can expect that to happen sooner or later.
Suggest you back up this file and try a compact/repair operation on it to see if it fixes the issue. If not, or if the c/r totally trashes the db, you have a file to fall back on. Regardless, the way to prevent this going forward is to split your db. You might start by opening a new one and importing all objects except tables in to it and make that your new fe. Create another new file and import the tables in to that then link them to the new fe. Given the amount of work this is going to involve, your users may have to do without the db for a day or so, but I suspect you have a bit of researching to do first on this subject, including how to manage distribution. There is plenty of answers given to those questions right here at AWF.
 

Isaac

Lifelong Learner
Local time
Today, 08:25
Joined
Mar 14, 2017
Messages
8,738
Just to help persuade you to split the database, I'll add my agreement with what others have already said. This is how not splitting a database presents itself. Under some circumstances a non split db might work OK for some time, and then it won't. The database should always be split into FE's and BE, no matter how light or segregated its usage may seem. Fortunately doing so is easy.
 

mkdrep

Registered User.
Local time
Today, 11:25
Joined
Feb 6, 2014
Messages
176
Thank you to everyone who replied to my question. I appreciate the help and direction
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:25
Joined
Feb 19, 2002
Messages
42,970
each person has a copy of MS Access on their computer
You keep saying this and the answer is "of course they do" If they didn't have a copy of MS Access, they couldn't run ANY Access application. The point that we are trying to make is that each user needs a copy of the .accdb on their own computer. This presumes that the database is split into a FE (forms/reports/macros/queries/code) and BE (tables only). The FE has tables that LINK to the BE. The BE (Back end) is located on a server so all users are sharing the "data" part of the application but they are NOT sharing the coding part of the application.

You have always been in jeopardy but you've been lucky that no one has corrupted the database causing a catastrophic loss. You are probably getting the error message now because you have made some change to the app that changes an object on the fly. if userA has the app open and opens a form that has code behind it that changes the form object itself, that will prevent any other user from even opening the application because Access CANNOT support sharing when objects are being updated. Only DATA may be updated in shared databases.

I wrote this yesterday but didn't hit send. Hope it helps
 

Micron

AWF VIP
Local time
Today, 11:25
Joined
Oct 20, 2018
Messages
3,476
if userA has the app open and opens a form that has code behind it that changes the form object itself, that will prevent any other user from even opening the application because Access CANNOT support sharing when objects are being updated.
That is subject to interpretation. Design changes, yes, but that's not the normal situation. If users share a common fe, because each has a copy of Access, they are opening an instance of a form. As long as they don't cause data conflicts, they will never know that user A opened a form in edit mode (if so coded) and user B opened that same form but to a new record. There will be no conflict, and the controls of each form will be visible/coloured/etc. according to the code that ran when they opened it.
 

missinglinq

AWF VIP
Local time
Today, 11:25
Joined
Jun 20, 2003
Messages
6,423
Having multiple users sharing a single, non-split database, sitting on a network drive is, as has been said, a sure way to repeated episodes of corruption, speed and timing problems, and all manner of strange, odd and curious behavior! Multiple users simply have to work off of a split database, with each user having their own copy of the Front End, which contains everything except the data/tables, on their respective hard drives, and a Back End with only the Tables on a shared drive.

Being in forced retirement, I spend 8-10 hours a day here and on other Access forums/newsgroups, and over the past 14 years have seen literally dozens and dozens of reports of non-split databases using links to a single Front End, causing these kinds of problems as well as massive data lose! The really insidious thing is that a non-split app can work for extended periods of time, as yours has, before the troubles begin! But once it does, they become chronic, which is to say they occur over and over and over again!

The record, by the way, was a Non-Split db that had been working, without problems, for 12 years! It then started exhibiting all of the symptoms listed above and continued to do so until the app was split.

Linq ;0)>
 

Micron

AWF VIP
Local time
Today, 11:25
Joined
Oct 20, 2018
Messages
3,476
of non-split databases using links to a single Front End
I agree with all of that, but if the db is not split, how can there be a single front end that is linked to anything?
P.S. - perhaps I'm encroaching on the record, at least as far as you might know. While I was familiar with split db's, I didn't know about the importance of it circa 2008 or 2009 and created such an app (actually, more than one). I still meet with or talk to some of the users from time to time and they say it's still working. Covid has put the kibosh on that for now, but the last time was this past December. I ask them if they can break it so that my former employer will call me back to fix it, but so far, no luck. The fe and be of 3 apps are on a network share. Must be a real stable and fast network, no?
Don't get me wrong - I wouldn't do it again.
 

Isaac

Lifelong Learner
Local time
Today, 08:25
Joined
Mar 14, 2017
Messages
8,738
Having multiple users sharing a single, non-split database, sitting on a network drive is, as has been said, a sure way to repeated episodes of corruption, speed and timing problems, and all manner of strange, odd and curious behavior! Multiple users simply have to work off of a split database, with each user having their own copy of the Front End, which contains everything except the data/tables, on their respective hard drives, and a Back End with only the Tables on a shared drive.

Being in forced retirement, I spend 8-10 hours a day here and on other Access forums/newsgroups, and over the past 14 years have seen literally dozens and dozens of reports of non-split databases using links to a single Front End, causing these kinds of problems as well as massive data lose! The really insidious thing is that a non-split app can work for extended periods of time, as yours has, before the troubles begin! But once it does, they become chronic, which is to say they occur over and over and over again!

The record, by the way, was a Non-Split db that had been working, without problems, for 12 years! It then started exhibiting all of the symptoms listed above and continued to do so until the app was split
Wow that was a lot of bold
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:25
Joined
Feb 19, 2002
Messages
42,970
Sometimes, it's hard to get people to listen:eek:

Having a monolithic database - all objects AND data in one .accdb that everyone shares is much worse than having a split FE and BE with multiple people sharing the FE. Why? because with a shared FE, when you corrupt something, it is generally only the FE so no big crisis. You shut everyone down for a while which is an annoyance but the data is probably safe. However with the monolithic configuration, when someone corrupts the db, they are likely to take data also and that is infinitely harder to recover from.

The split FE/BE with distributed FE's so each user has his own, has been the expert recommendation as long as I can remember. No one who knows anything about Access would use any other method. PERIOD! I even use split FE/BE for my own personal databases.
 

Users who are viewing this thread

Top Bottom