Prevent Users from Opening the Database window

This code is in a module called from the autoexec macro.

'Set Startup Properties to allow total access to database.
If CurrentUser() = "Admin" Then
    MsgBox "Admin is not a valid Login."
    DoCmd.Quit acExit
ElseIf CurrentUser() = "YOURLOGIN" Or CurrentUser() = "DATABASEADMINLOGIN" Then
    SetStartupProperties
End If


Sub SetStartupProperties()

Rem ******************************************************************
Rem * This procedure passes the name of the startup property to be   *
Rem * set, its datatype, and its desired setting.  It works with the *
Rem * ChangeProperty Function.                                       *
Rem ******************************************************************

ChangeProperty "StartupShowDBWindow", dbBoolean, True
ChangeProperty "AllowBuiltinToolbars", dbBoolean, True
ChangeProperty "AllowFullMenus", dbBoolean, True
ChangeProperty "AllowBreakIntoCode", dbBoolean, True
ChangeProperty "AllowSpecialKeys", dbBoolean, True
ChangeProperty "AllowBypassKey", dbBoolean, True

End Sub


Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
   
Rem *******************************************************************
Rem * This procedure attempts to set the startup property stated in   *
Rem * SetStartupProperties Sub Function. If the property is not found,*
Rem * it uses the CreateProperty method to append it to the database  *
Rem * properties collection.  This is necessary because these         *
Rem * properties do not appear in the properties collection until     *
Rem * they have been set or changed at least once.                    *
Rem *******************************************************************
   
Dim dbs As DATABASE, prp As Property
   
Const conPropNotFoundError = 3270
Set dbs = CurrentDb
   
On Error GoTo Change_Err

dbs.Properties(strPropName) = varPropValue
    ChangeProperty = True

Change_Bye:
    Exit Function

Change_Err:
    If Err = conPropNotFoundError Then  'prop not found
        Set prp = dbs.CreateProperty(strPropName, _
        varPropType, varPropValue)
        dbs.Properties.Append prp
        Resume Next
    Else
        'unkown error
        ChangeProperty = False
        Resume Change_Bye
    End If
End Function

Function DisableShiftKeyBypass() As Boolean

Rem ****************************************************************
Rem * Run this procedure from Debug.  After this procedure is run, *
Rem * the next time the datbase is opened the Autoexec marcro will *
Rem * NOT be bypassed, even if the shift key is pressed.           *
Rem ****************************************************************

Dim db As DATABASE
Dim prop As Property
Dim strmsg As String
Const conPropNotFound = 3270

On Error GoTo errDisableShift

Set db = CurrentDb()

db.Properties("AllowByPassKey") = False
DisableShiftKeyBypass = True

exitDisableShift:
    Exit Function

errDisableShift:
    'The AllowByPassKey property is a user-defined
    'property of the database that must be created
    'before it can bee set.  This error code will execute
    'the first time ths function is run in a database.
   
    If Err = conPropNotFound Then
        Set prop = db.CreateProperty("AllowByPassKey", _
            dbBoolean, False)
        db.Properties.Append prop
        Resume Next
    Else
        strmsg = "Function DisableShiftKeyBypass did not "
        strmsg = strmsg & "complete successfully."
       
        MsgBox strmsg, vbExclamation
        DisableShiftKeyBypass = False
        GoTo exitDisableShift
    End If
End Function

Sub SetStartupProperties_Close()

Rem ******************************************************************
Rem * This procedure passes the name of the startup property to be   *
Rem * set, its datatype, and its desired setting.  It works with the *
Rem * ChangeProperty Function.                                       *
Rem ******************************************************************

ChangeProperty "StartupShowDBWindow", dbBoolean, False
ChangeProperty "AllowBuiltinToolbars", dbBoolean, False
ChangeProperty "AllowFullMenus", dbBoolean, False
ChangeProperty "AllowBreakIntoCode", dbBoolean, False
ChangeProperty "AllowSpecialKeys", dbBoolean, False
ChangeProperty "AllowBypassKey", dbBoolean, False

End Sub

This code is run to lock the database.  Only the developer or database administrator login can execute it.

Public Function CloseDBbyDeveloper()

Rem ************************************************************
Rem * Allows the Developer or DB Administrator to select the   *
Rem * type of Startup Properties to close the Database with.   *
Rem * This procedure must be RUN each time the Developer or    *
Rem * Administrator Logs out of CTS.  If not, the Database     *
Rem * will left unsecured.                                     *
Rem *                                                          *
Rem * If logged out with User Properties, the Database will be *
Rem * reverted back to User properties the next time a user    *
Rem * logs into CTS.                                           *
Rem ************************************************************

Dim strLogoffMsg As String
Dim Style As String
Dim Title As String
Dim Response As String

If CurrentUser() = "YOURLOGIN" Or CurrentUser() = "DATABASEADMINLOGIN" Then
    strLogoffMsg = "Do you want to change Startup Properties back to "
    strLogoffMsg = strLogoffMsg & "User Properties and Close the Database?"
    strLogoffMsg = strLogoffMsg & Chr$(13) & Chr$(13)
    strLogoffMsg = strLogoffMsg & "YES *** Change Properties & Exit CTS"
    strLogoffMsg = strLogoffMsg & Chr$(13) & " NO *** Exit CTS"
   
    Style = vbYesNo + vbQuestion + vbDefaultButton1
    Title = "Administrator/Developer Closure Options"
    Response = MsgBox(strLogoffMsg, Style, Title)
       
    If Response = vbYes Then
        SetStartupProperties_Close
        ExitCTS
    Else
        ExitCTS
    End If
Else
    Exit Function
End If

End Function

delete the admin login or give it no priveleges.

We control access to the tables, queries, modules, forms, and reports through groups.  Only members of the development staff have modify permission.  All queries are run with owner's permissions and the owner is a member of the development staff.

Access through the program is controlled by user priveleges stored in a table and set when the user logs in.

Hope this helps
mlmcc