Disable access controls tabs : (Database tools & Create)

[ahmed1qadoora]

In my database i would like to keep the "Create" &
" Database tools" tabs disabled for all other users,
so i make sure that no one can change the :Relationships" i been designed, plus to no one else could create table, query....

[Rabbit]

Create an mde or accde (depending on your version of Access) from the original mdb/accdb. Keep the original for future development, distribute the mde/accde for use.

[Mihail]

Not available for queries.
At least in 2007 .accde files.
You can change the design and also you can remove a query from .accde file. It is a bug, of course, but... be very carefully.

[zmbd]

Mihail, this is also true for V2010, just tried it on a development database, how discouraging!
Ok, so my thoughts are what one can do as a work around for this bug leading to the following:
1) Hide the navigation pane (nothing fancy needed here for V2010, just go into the options for the current database and hide the navigation pane). There are of course various means to do this within VBA etc; however I’m going simple here… one could also set the startup form at this point too.
2) Use autokeys macro to trap [F11]. I use this to open either the main switchboard (or the newer navigation-form) or a form that asks for user name and password which if correct then open a maintenance form that allows for opening the access object navigation pane.
3) Create a custom set of ribbons. (in the older v2003 I created custom menus) use the scratch keyword to ensure that the built in ribbon(s) isn’t (aren’t) available. One such site I’ve found useful for this is: Gunter Avenius: Example for a user-defined ribbon in Access: Be aware that they may have links to software at this site for a ribbon creator; however, this link is directly to tutorials for creating custom ribbons by hand which are well worth the effort to read and may provide a means of disabling some ribbon functions without having to rebuild everything from scratch. Personally, I just kill the ribbons and provide what I want for the users via the main forms.
z

[Mihail]

I have founded a bit of code in other site:

Put this code in a public module

Public Function SetProperties(strPropName As String, _
varPropType As Variant, varPropValue As Variant) As Integer

    On Error GoTo Err_SetProperties

    Dim db As DAO.Database, prp As DAO.Property

    Set db = CurrentDb
    db.Properties(strPropName) = varPropValue
    SetProperties = True
    Set db = Nothing

Exit_SetProperties:
    Exit Function

Err_SetProperties:
    If Err = 3270 Then    'Property not found
        Set prp = db.CreateProperty(strPropName, varPropType, varPropValue)
        db.Properties.Append prp
        Resume Next
    Else
        SetProperties = False
        MsgBox "SetProperties", Err.Number, Err.Description
        Resume Exit_SetProperties
    End If
End Function

And this code in a Form module, under the Control_Click event (use any control where you can find the _Click event)

Private Sub bDisableBypassKey_DblClick(Cancel As Integer)
On Error GoTo Err_bDisableBypassKey
    'This ensures the user is the programmer needing to disable the Bypass Key
    Dim strInput As String
    Dim strMsg As String
    Beep
    strMsg = "Do you want to enable the Bypass Key?" & vbCrLf & vbLf & _
             "Please key the programmer's password to enable the Bypass Key."
    strInput = InputBox(Prompt:=strMsg, title:="Disable Bypass Key Password")
    If strInput = "LISA270253" Then
        SetProperties "AllowBypassKey", dbBoolean, True
        Beep
        Msg = MsgBox("The Bypass Key has been enabled." & vbCrLf & vbLf & _
               "The Shift key will allow the users to bypass the startup" & _
               "options the next time the database is opened.", _
               vbInformation, "Set Startup Properties")
    Else
        BeepOnce
        SetProperties "AllowBypassKey", dbBoolean, False
        Msg = MsgBox("Incorrect ''AllowBypassKey'' Password!" & vbCrLf & vbLf & _
               "The Bypass Key was disabled." & vbCrLf & vbLf & _
               "The Shift key will NOT allow the users to bypass the" & _
               "startup options the next time the database is opened.", _
               vbCritical, "Invalid Password")
        Exit Sub
    End If
Exit_bDisableBypassKey:
    Exit Sub
Err_bDisableBypassKey:
    MsgBox "bDisableBypassKey", Err.Number, Err.Description
    Resume Exit_bDisableBypassKey
End Sub

I have tried this code and works very well.

In conjunction with .accde format is stronger than in conjunction with .accdb format.
That because if you move the .accdb file outside the "trust" folder, the code will stop work.

[zmbd]

In V2010 one can do the following as
[Autoexec] Macro - it runs even if the bypass key is held

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
<UserInterfaceMacro MinimumClientDesignVersion="14.0.0000.0000">
<Statements>
   <ConditionalBlock>
      <If>
         <Condition>[currentproject].[istrusted]</Condition>
         <Statements>
            <Action Name="Beep"/>
         </Statements>
      </If><Else>
         <Statements>
            <Action Name="MessageBox">
               <Argument Name="Message">The database is currently not in a trusted state. Many of the features of the database are currently disabled. Please Close this database and re-Open it as a trusted application. You shold consider adding the file location to the trusted list.
               </Argument>
               <Argument Name="Type">Critical
               </Argument>
               <Argument Name="Title">Database Is Partialy Disabled.
               </Argument>
            </Action>
         </Statements>
      </Else>
   </ConditionalBlock>
</Statements>
</UserInterfaceMacro>
</UserInterfaceMacros>

This is from one of my production databases so it only warns the user about the issues. In other code, I have the database close after the message is displayed; thus, preventing the user from doing much of anything unless the VBA is available (mind you, I only enable this in the production database - I keep a development copy!).