How do I get Exclusive Access to Access 2010 database to make code changes?

Bob Collison
Bob Collison used Ask the Experts™
on
Hi Experts,
Recently I started encountering a message advising me I don't have 'exclusive access at this time' to the database to make changes in VBA code.

I don't know why this has started happening recently.

What can I do to fix it?

I'm the only person running doing development in the application.

Thanks.
Bob C.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
try decompiling your db
Create a shortcut with something like
"C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE"  "C:\Users\Username\Desktop\Projects\myAccdb.accdb" /decompile

run it

then open the db and from VBA window do a debug > compile
do a compact and repair
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<What can I do to fix it?>>

With the DB closed, delete any .LDB file that exists and make sure that you have full rights to the directory where the DB resides.

If you exited abnormally at some point, ACE (the database engine) may still think your in the file.

Also, outside of Access, if you have explorer open with preview enabled, make sure your not sitting on the file.

Jim.
Distinguished Expert 2017

Commented:
Hopefully, you are not really trying to make changes to a shared database.  The developer must always have his own copy of the FE and also his own test copy of the BE.  Changes are made to the developer's copy of the FE and tested against the test BE.  Once they work, the FE is relinked to the production BE and the new FE is distributed.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Bob CollisonSystem Architect

Author

Commented:
Hi Experts,
Hi Jim,
I searched my whole system for files with an .ldb.  It found two on a backup drive (physically separate from where I do my development) that were over two years old.

I re-booted the system and there are no .ldb files remaining.

The problem still exists.

Rey,
After trying Jim's suggestion I completed the following and  still have the same problem.
- Made a copy of the .accdb,
- Created a shortcut with the following entry: M:\Pgm\01-MDB-Design\SAS\Temp\SAS.accdb /decompile
- Used the Shortcut which opened the application,
- Selected the first Form and opened it in Design Mode,
- Opened the Load Event in VBA Code Window,
- Selected Debug > Compile.  Not errors detected.
- Closed VBA Code Window,
- Closed the Form,
- Completed Compact Repair,
- Closed the .accdb.

I opened the .accdb and the problem still exists.

Thanks
Bob C
Bob CollisonSystem Architect

Author

Commented:
Hi Pat,
I am well aware of the need to only have development occurring on one instance of both the Front & Back Ends.

I am working on my own PC at home and therefore there cannot be anyone else accessing either end.

All Experts,
Is this possibly an issue with the MS Access Options Settings?
In particular the 'Client Settings'?  I have them set as follows.
- Advanced - Default Open Mode: Shared.
- Advanced - Default Record Locking: No locks.
- Advanced - Open database by using record-level locking" Checked with rwest of the setting as per default.

I have tried other settings without them solving the problem.
What should they be?

Thanks,
Bob C.
Top Expert 2016
Commented:
try copying the  db to your pc, desktop and run it from there.
Bob CollisonSystem Architect

Author

Commented:
Hi Rey,
I only do development on my PC (other than testing when I am ready).

I copied the whole development folder / subfolders to my Desktop, relinked to the Back End Databases and closed the accdb.

I then started it up again and I still have the problem.

Thanks,
Bob C.
Top Expert 2016
Commented:
create a blank  db
import All objects from the Back end
import all objects from the Front End

do a compact and repair

check if the problem still exists.

if everything is fine, split the DB, New FE, New BE
run the New FE and reconnect to the NEW Back end

test again..
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Bob,

Dumb question; your not opening two instances of Access are you?  Say one for the FE and one for the BE?

Also, make sure you note the point I made about Windows Explorer and having the preview pane on.
Doing so will not allow you to have exclusive access to a DB. I've been bitten by this more than once.

If none of the above does it for you and Rey's last comment doesn't work either, then I think we'd all be doing some head scratching with a dive into process explorer required to look at what processes have an open file handle.

 and no, as a single user with a single instance of Access open, none of the settings in options apply.

Jim.
Bob CollisonSystem Architect

Author

Commented:
Hi Jim,

No such thing as a dumb question.  As a matter of fact the application has a module selected from a Form that does open / run another instance of Access.  This second instance contains all of the code for converting the data from an old version of a BEDB into a new version BEDB.  However this isn't being run from the original accdb when I have the access problem.

I have checked via Task Manager and it doesn't show more than one instance of MSACCESS.EXE 32 running.

I obviously was hoping to avoid Rey's latest solution since this is a large application with about 500 Objects (Forms, Reports, Tables, etc.).

Thanks for the comment about the Option Settings.  Also I don't use the Preview Pane at all so that isn't an issue.

I guess the next step is Rey's suggestion.  I'll start it shortly but probably won't finish it until tomorrow.

Thanks,
Bob C.
Distinguished Expert 2017

Commented:
Do you have any code in the application that modifies objects?  

Does the problem exist on the original copy of the FE?
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<I guess the next step is Rey's suggestion.  I'll start it shortly but probably won't finish it until tomorrow.>>

 Either that or download process explorer and see what process(es) has the file open.

 If it's just yours, then the database header page has a problem and Rey's suggestion would take care of that.  Actually, what you might want to try doing is opening three or four instances of Access and the DB, then close each.  The .LDB file should disappear when you close the last.

 That may cleanup the DBH without having to create a new DB.

Jim.
Top Expert 2016

Commented:
is the problem happening only in a particular Object (Form, etc.,) ?
Bob CollisonSystem Architect

Author

Commented:
Hi Experts,

The problem appears to have been solved by the following steps.  I will leave this Question open until I can do some additional testing.

I have completed the creation of a new Front and Back End databases as follows.
- Created a new empty FEDB.
- Copied all Modules, Tables, Forms, Queries and Reports into the new FEDB.
- Compacted / Repaired the FEDB.
- Opened the FEDB, First Form and its Load Event in VB.
- Did a Debug > Compile.
- Fixed 6 Errors identified during the Compile.  Note that the original DB that had the problem compiled without errors.
- Compacted / Repaired the FEDB.
- Split the FEDB to create the BEDB.
- Verified the linkages.
- Opened the FEDB and completed the same process as previously produced the Error.
- I was able to change the code and save it without any error.

Rey's latest question,
The problem appears to happen on any Bound Form.  The initial Welcome Form that displays is not Bound and therefore doesn't have the problem.  It has a button that opens the Main Menu which is bound which doesn't have the problem.  Forms that are launched from the Main Menu (most of which are Bound) have the problem.

Jim,
I haven't tried opening multiple instances and then closing them followed by a check of the .LDB.

Pat,
As previously mentioned the problem began a week or so ago.  I don't have a copy of the 'Original FE' that I could try.  There is no code to modify Objects.  e.g. Tables.

So at this point it looks like everything is OK.  I'll do some additional checking and let you know.

Thanks for all your assistance.
Bob C.
Bob CollisonSystem Architect

Author

Commented:
Hi Experts,
The problem is back and occurs on accessing the first bound form.

Jim,
I have downloaded and installed process Explorer for Windows 7 64 Bit.

Could you please let me know what to do with it in regard to the problem?

Thanks,
Bob C.
Top Expert 2016

Commented:
can you share to us the db?
Top Expert 2016

Commented:
@Bob,

are you trying to change codes while the form is NOT in design view?

can you also try using a different PC or laptop?
Bob CollisonSystem Architect

Author

Commented:
Hi Rey,

I'll try to upload a copy but probably not until tomorrow.

I can try it on another PC and will give it a shot.

I'm not sure what you mean by 'trying to change codes while the form is Not in design view'.  Specifically the word 'code'.  Could you please elaborate?

Thanks,
Bob C.
Top Expert 2016

Commented:
from your post above
<- I was able to change the code and save it without any error.>

are the codes you are changing is on the form's module?
Bob CollisonSystem Architect

Author

Commented:
Hi Rey,
Yes.  I am trying to change VBA Code in a Form Event.
Thanks,
Bob C.
Top Expert 2016

Commented:
so, is the form in design view while you are changing the codes?
Bob CollisonSystem Architect

Author

Commented:
Hi Rey,
I am just walking through the scenarios using the new version that I am preparing to upload to EE.

I open the form in Form View, switch to Design View. Before the Form actually displays in Design View the message displays regarding not having exclusive access with the options of Ok or Cancel.  If I select Ok the form opens in Design View.  At this point I can make a change such as adding a bound Field which is saved.

If I do the same scenario but after adding the field I open the Load Event Code Window, the Warning Message  again displays and if I proceed I can make a change, close the Code Window  switch to Form View and the Code change is working.

However when I close the Form I am asked if I want to save the changes.  I respond 'Yes' the form closes and the changes are not saved.

Thanks,
Bob C.
Bob CollisonSystem Architect

Author

Commented:
Hi Rey,

I am attaching two Access 2010 .accdb files - Front & Back End.

Please note the following.
When setting up the Front & Back Ends for testing there is code that determines the location of the BE based on the location of the FE.  Specifically if the FE is in a Folder called NewBase, the BE must be located in a Sub-Folder of NewBase called Database.  e.g. M:\NewBase\Database.  You will need to Re-Link the FE to the BE using this structure.

Operation
To launch the FE Open the New EE Front End.accdb, select the Form Objects and open the A-00-000 - Welcome Form.  Note that the display of the Primary Database is not relevant as it is display only.  This form is not bound to a table however it opens a Database / Table, displays the data and closes the Database / Table.

Click the M-00-000 - Main Menu button.  The only other button that is relevant is the Exit System as it replaces the standard Close button.  The Main Menu displays along with a message (bottom of menu) for 3 seconds.  This is irrelevant to the problem and the instruction doesn't need to be done unless you want to select an option from this menu.  The Main Menu is bound to the 00_PERIOD_MSTR Table.

Select the Design View and the message appears that you don't have exclusive access.

Use the Close Menu - M-00-000 button to close the form.  The Standard Form Close button doesn't close the form properly.

To exit the A-00-00 - Welcome Form, click the Exit System button.

Thanks,
Bob C.
New-EE-Front-End.accdb
New-EE-Back-End.accdb
Top Expert 2016
Commented:
try
do not open the form "A-00-000 - Welcome"

open the form  "M-00-000 - Scout Admin System - Main Menu"  in DESIGN view

change some codes in the form's module or add whatever codes, then save
Bob CollisonSystem Architect

Author

Commented:
Hi Rey,

I think that you have found the problem.

I opened M-00-000 directly (without opening A-00-000) and was able to successfully make changes to it and one of its sub-forms (F-00-020) without encountering the Warning Message.

As part of this testing I had to eliminate the references to A-00-000 in M-00-000.  I commented them out. Changing the code to get the data from A-00-000 a different way (directly from the applicable table instead of a field on A-00-000).  I can also keep the data on A-00-000 up to date another way.

I also noted that A-00-000 does not set the linkages properly.  For instance if the linkages were setup directly via Link Table Manager and then A-00-000 is opened, the linkages are changed to be incorrect.

In summary, I think you have identified where the problem occurs which answers my original question.

I'm going to do some more testing and will let you know whether it is truly solved.

Thanks again for all of your assistance.
Bob C.
Bob CollisonSystem Architect

Author

Commented:
Hi Rey, Jim, Pat,
Just a quick update.  I have extensively reviewed / modified the code in Forms A-00-000 and M-00-000 and I still have the problem though it appears to be sporadic.

I'm still trying to diagnose where exactly the problem occurs.

First, I have tried to find a .LDB File anywhere on the system when I have the FE open and accessing a linked BE Database but I have never been able to have one show up.  Where should it appear?

Secondly, when the error is displayed none of the BE Linked DBs have an associated .laccdb file.

Thirdly, does this issue only present a problem during development (Design View) or is it als going to affect Production?

I'll keep you all updated.

Thanks,
Bob C.
Top Expert 2016
Commented:
here is the thing, i will not release the app if i am having problem with the development.

-there will be an .laccdb in the folder where your front end is located when you open the Front End.
-if the FrontEnd is link to the BE, an .laccdb for the BE must also appear in the folder where the BE is located.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<First, I have tried to find a .LDB File anywhere on the system when I have the FE open and accessing a linked BE Database but I have never been able to have one show up.  Where should it appear?>>

 It will show up in the same directory as the DB.   If it does not and you have the DB open, then you do not have create rights for the directory.

 In this case, it does open, but with no .LDB file, it automatically gets opened exclusive regardless of settings.


<<Secondly, when the error is displayed none of the BE Linked DBs have an associated .laccdb file.>>

 Because it's the front end that is the issue.

<<Thirdly, does this issue only present a problem during development (Design View) or is it als going to affect Production?>>

 Most likely yes.  Unless your flipping a a form into design view.

Jim.
Bob CollisonSystem Architect

Author

Commented:
Hi Rey, Jim,

Thanks for the information.  The reason I couldn't find the file was that I was trying to find .LDB rather than .laccdb since I am using Access 2010.

A .laccdb does appear for both the FE and the BE. If I close the BE Form (M-00-000) which accesses a table in the BE, the BE associated Lock File is deleted.  Same for the FE.

With my search for .laccdb the Lock Files for the FE and BE show up correctly, however there are many entries for the following file for today (I rebooted my PC today).
- laccdb-lock-retained-after-access-is-closed[1].htm

This appears to relate to my issue.  Can you shed any light on it?

Thanks,
Bob C.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<laccdb-lock-retained-after-access-is-closed[1].htm>>

  This is a html file, is that the complete file name?   What's in it?

Jim.
Bob CollisonSystem Architect

Author

Commented:
Hi Jim,

That is the full filename.  I did some further investigation and I believe that it is the name of a file related to my Web Search for .laccdb File information as after the previous update a search showed this Question  as a file like the one I asked about above.

Sorry for the red herring.
Thanks.
Bob C.
Bob CollisonSystem Architect

Author

Commented:
Hi All,
Thanks for all the work you did on this question.  Although the issue still exists I have determined through exhaustive testing that it only happens when I:
- Open unbound form A-00-000 - Welcome (that needs to be unbound because it contains a form to re-link).
- Click a button to open form M-00-000 - Main Menu which is bound.

It is not necessary during development to use form A-00-000 so if I initially just load form M-00-000 and do my development I haven't had a problem.  i.e. It appears that starting with an unbound form and going to a bound form is the issue.

Thanks again for all your assistance with this issue.
Bob C.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial