Link to home
Start Free TrialLog in
Avatar of upsfa
upsfa

asked on

Access database "The database has been placed in a state..." caused by code that creates context menu

Background
We have created an Access database with Access 2013 that is to be shared by many users.  No reports, import, or exports are being run when the db is being used.  We are simply asking users to update some fields in a form.  We have secured the database by:
•      Limiting access to records based on the ADID of the person logged into the PC.
•      Database window, Navigation Pane and Ribbon have been disabled/hidden.
•      The shift key (Bypass Key) is disabled, so there is no access to the database window.
•      VBA code is password protected.

We don’t want to create front ends for each user since this is a one-time use effort designed to prevent us from sending Excel files out to collect some information.

Problem
We wanted to give the users a limited right-click menu so they could sort and filter but not much else.
We have the code to do this and it works well, however, when a second user opens the database they get the message “The database has been placed in a state by user 'Admin' on machine 'machine name' that prevents it from being opened or locked".
When I remove the call of the code that creates the right-click menu the error goes away.  Does anyone know why this code would cause that error?  The code is below, and I have attached a very simple database, created just to test this problem, that only has one simple form, no tables or queries, and only contains that code.  The code is invoked on Form Load.
Option Compare Database

 
' This code creates the Shortcut menu (right-click menu, or context menu)

Public Function CreateSimpleShortcutMenu()
  On Error Resume Next 'If menu with same name exists delete
  CommandBars("GeneralClipboardMenu").Delete
  Dim cmb As CommandBar
  Set cmb = CommandBars.Add("GeneralClipboardMenu", msoBarPopup, False, False)
      With cmb
          .Controls.Add msoControlButton, 21, , , True   ' Cut
          .Controls.Add msoControlButton, 19, , , True   ' Copy
          .Controls.Add msoControlButton, 22, , , True   ' Paste
          .Controls.Add msoControlButton, 210, , , True 'Sort Ascending
          .Controls.Add msoControlButton, 211, , , True 'Sort Decending
          
          ' Add the Find command.
        .Controls.Add msoControlButton, 141, , , True
               
        ' Start a new grouping and Add the Filter by Selection command.
        .Controls.Add(msoControlButton, 640, , , True).BeginGroup = True
                
        ' Add the Filter Excluding Selection command.
        .Controls.Add msoControlButton, 3017, , , True
        
        ' Add the filter Contains command.
        .Controls.Add msoControlButton, 10080, , , True 'Contains Selection
        
        ' Add the filter Does Not Contain command
        .Controls.Add msoControlButton, 10081, , , True 'Filter does not contains
          
      End With
  Set cmb = Nothing
End Function

Open in new window

TestMultiUserRightClick.accdb
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Here's a thought:

How to avoid error 3734
http://www.source-code.biz/snippets/vbasic/10.htm

Cause: This error can occur when the database is automatically temporarily "promoted" from share mode to exclusive mode. This may happen when the VBA program changes something that Access wants to store within the MDE file, e.g. toolbar/menubar items (through Application.CommandBars).
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of upsfa
upsfa

ASKER

Went with a FE BE set up, Thanks.
Avatar of upsfa

ASKER

Went with a FE BE set up, Thanks.