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

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.

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

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.

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.

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.

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.

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

Does the problem exist on the original copy of the FE?

is the problem happening only in a particular Object (Form, etc.,) ?

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.

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.

can you share to us the db?

@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?

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.

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?

Hi Rey,
Yes.  I am trying to change VBA Code in a Form Event.
Thanks,
Bob C.

so, is the form in design view while you are changing the codes?

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.

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

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.

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.

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.

<<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.

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.

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.