We help IT Professionals succeed at work.

Prevent Users from Opening the Database window

Pohara
Pohara asked
on
Medium Priority
1,453 Views
Last Modified: 2008-02-01
To bypass the Startup option settings, hold down the Bypass (SHIFT) key while opening the database. This action will bypass any command entered in the startup window of Access.  One of the commands in this menu is the Display Database Window Check box.  If this box unchecked then the database will open with the form defined in the "Display Form" entry.  In addition if you Uncheck the "special Keys" check box the user cannot use the "F11" key to call up the window.  HOWEVER.....

If when you open the database from a the ACCESS command in the start bar if you press "SHIFT" at the same time then the "Start Up" box functions are completely ignored and the database window can be viewed.

How do prevent this?  Please note I already know the suggestion that can replace the "File" menu with a custom menu HOWEVER this does not work when a user who has his own version of access opens a copy of your file.

Any Ideas?

Thanks  Phil

Comment
Watch Question

I don't think it can be prevented.  Reason being that there would be no way to make changes to the database.

dill


Commented:
Phil,

100% won't be possible...

You can add the AllowBypassKey (check in the helpfile) setting to prevent the SHIFT from interrupting your startup, but for tables and queries no real "protection" is possible. All forms, Reports and Modules can be "protected" by the creation of an .mde, making them impossible to edit.

(BTW make sure to have an editable backup before setting AllowBypassKey or creating a .mde, as you can't open it either....)

Nic;o)

Author

Commented:
Gentlemen:

Thanks very much for your comments so far.  The one thing I am not very clear on Nico is exactly how I set that property.  It appears the only way I can do this is to create a VB program to call up the Access file and change the property.  Therefore when another copy of ACCESS opens the file it will already be set. Am I understanding that correctly?

  So to clarify....  If I set it with a VB program and attach the property to the file when a computer that has it's own copy of Access on it calls it will it be set?

Thanks
phil

Commented:
You have differents options (as always...)
You could issue the command in the Immediate Window (Also known as the "debug window").
I myself added the code to switch this on AND off behind the OnClick event of two textboxes of the mainform....
(It's hard to impossible for a user to find this ;-)

Just check the code of the "AllowBypassKey Property Example" in the helpfile.

Nic;o)
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
We do something that intercepts the shift key and only allows you in if you are in the admin group.  We of course have our own MDW file which only we can modify.  I'll supply the code when I get to work next week.

mlmcc
CERTIFIED EXPERT

Commented:
i'll second what nico has written above. you will NEVER be guaranteed that your tables are inaccessible. as nico also mentions, queries will also be available and i'm pretty sure so will macros. that's not a lot of protection, but it does keep out all but the most advanced users (and even some developers! lol)

as for queries, a long time ago i stopped saving the queries as objects. i exclusively run the queries from code now. this prevents issues with queries. i try not to use macros, but you NEED to for autokeys. as for tables, you could actually do away with localized tables entirely and rely on the IN statement (see help) however this can be OVERLY complex and it's not as efficient as linked tables.

basically all you can hope for is that the users who DO have the knowlege to circumvent access security, don't exploit that knowlege...

good luck

dovholuk

Commented:
Hi dovholuk,

Missed my "Points for dovholuk" question ?

Nic;o)
Commented:
Hi Pohara

Here are 2 peices of code I use.

One turns on and off the startup options the other turns on and off the hiding of tables

Code One


'This sets security, so users can't fool around with stuff--very useful....

Sub SetStartupProperties()

ChangeProperty "StartupShowDBWindow", dbBoolean, False
'ChangeProperty "StartupShowStatusBar", dbBoolean, False
ChangeProperty "AllowBuiltinToolbars", dbBoolean, False
ChangeProperty "AllowFullMenus", dbBoolean, False
ChangeProperty "AllowShortcutMenus", dbBoolean, False

ChangeProperty "AllowBreakIntoCode", dbBoolean, False
ChangeProperty "AllowSpecialKeys", dbBoolean, False
ChangeProperty "AllowBypassKey", dbBoolean, False


End Sub

'This turns all the protection off

Sub SetAdminProperties()

ChangeProperty "StartupShowDBWindow", dbBoolean, True
'ChangeProperty "StartupShowStatusBar", dbBoolean, True
ChangeProperty "AllowBuiltinToolbars", dbBoolean, True
ChangeProperty "AllowFullMenus", dbBoolean, True
ChangeProperty "AllowShortcutMenus", dbBoolean, True

ChangeProperty "AllowBreakIntoCode", dbBoolean, True
ChangeProperty "AllowSpecialKeys", dbBoolean, True
ChangeProperty "AllowBypassKey", dbBoolean, True


End Sub

'This part is the function used to make the changes

Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
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 ' Property not found.
Set prp = dbs.CreateProperty(strPropName, varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else
' Unknown error.
ChangeProperty = False
Resume Change_Bye
End If
End Function

Code Two


Public Sub HideTables()
   
   Dim tdf As TableDef
   
   For Each tdf In CurrentDb.TableDefs
       If tdf.Name Like "msys*" Then
       Else
           tdf.Attributes = dbSystemObject
       End If
   Next tdf
   
   Set tdf = Nothing
   
End Sub

Public Sub ShowTables()
   
   Dim tdf As TableDef
   
   For Each tdf In CurrentDb.TableDefs
       If tdf.Name Like "msys*" Then
       Else
           tdf.Attributes = 0
       End If
   Next tdf
   
   Set tdf = Nothing
   
End Sub

HTH
Neil

Author

Commented:
Thank You EVERYONE who answered this question. From the comments above it is easy to see that this so called "simple" question was not so simple. I really appreaciate the excellent response.  I only wish I could award points to each of you.

I will try Niels code and see what happens. Since I am using Neils solution I will award him the points.  I must say I like this service.  

Thanks again

Phil

Author

Commented:
I would love to see the code on the intercept of the shift key.  If mlmcc would like to post it I will certainly try to use.  

Thanks again to everyone who posted answers!

Phil
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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


Explore More ContentExplore courses, solutions, and other research materials related to this topic.