We help IT Professionals succeed at work.

CHANGING THE ALLOWBYPASS OPTION DATABASE WINDOW HELP

InvisibleMan
InvisibleMan asked
on
Medium Priority
637 Views
Last Modified: 2009-12-16
Hello all.  Ok here is what I need to do.  I need to lockdown any user from holding the shift key down then only allow the 1 admin person to do this.  How can this be done.  I have seen some articles but they dont help much it doesnt show how to call the function etc.  Now I think this is going to be tough because right off the bat how would I know that the user holding the shift key is an admin?  Is there a way after they login which I have seperate login form with code I validate then I can change something that would allow that admin to access the Database Window?  Thanks
Comment
Watch Question

CERTIFIED EXPERT
Commented:
don't remember where i acquired it from, but here's the module i use. copy / paste this code into a new module.


Public Function SetAllowBypassKey(bBypass As Boolean)
    '******************************************************
    ' Sub SetAllowBypassKey(bBypass As Boolean)  Access 97
    '
    ' Sets the AllowBypassKey property of the Properties
    ' collection of
    ' the current database to either True or False.  Takes
    ' effect the next
    ' time the database is opened.
    '********************************************************
    Dim dbs As Database, prp As Property
    Const conPropNotFoundError = 3270
    'Occurs if the property doesn't exist
    Const strKey = "AllowBypassKey"
    'Our property name
    Set dbs = CurrentDb
    'Set object to the current database
    On Error GoTo SetAllowBypassKeyErr
    'Specify error handler
    If bBypass = False Then
      dbs.Properties(strKey) = False
    'Set AllowBypassKey to False
    Else
      dbs.Properties(strKey) = True
    'Set AllowBypassKey to True (default)
    End If

If bBypass = True Then
   MsgBox "Enabled"
Else
   MsgBox "Disabled"
End If

SetAllowBypassKeyExit:
      Exit Function

SetAllowBypassKeyErr:
      If Err = conPropNotFoundError Then
    ' Property doesn't exist yet
    If bBypass = False Then
    ' Create the property
         Set prp = dbs.CreateProperty(strKey, dbBoolean, False)
      Else
         Set prp = dbs.CreateProperty(strKey, dbBoolean, True)
      End If
      dbs.Properties.Append prp
    ' Add it to the collection
      Resume SetAllowBypassKeyExit
    End If

End Function

'********************** END COPY *************************

THEN create an AutoKeys macros (if you don't have one). add two entries. first create one with a Macro Name column of "^+{F12}", Action=RunCode, Function Name (below) = "=SetAllowBypassKey(True)"

the second row should be: Macro Name "^+{F11}", Action= RunCode, Function Name (below) ="=SetAllowBypassKey(False)"

then when you want to lock the db, press Ctrl-F12
when you want to unlock the db, press ctrl-F11

the ctrl-F12/F11 is totally up to you. i use a very difficult to guess keyboard combo. some people use a "hidden" command button, etc.

make sense?

dovholuk

Commented:
There has been a lot of discussion on this point which you can see under my origional question at:question no 20264224

The solution that I came up with to block some users but not others is as follows.

Create a form called login as your startup form and have a text field on it called UserPassword

You then paste the  code below into the form. To make sure that the users can not bypass the logon form  you make it firstly the startup object and make it modal so that they must enter the password to get at the application in general. If the paswword entered is that of the user more than just the Shift key is bolcked in the sub-SetStartupPropertiesGeneral. You can select which you want to block.

I actually put a lot more security in place d code for that can be seen under the old question.

The code below works well and I have just implemented it for a client yesterday

Good Luck

Rick


'_______________ THE CODE_________
Private Sub Form_Load()
UserT = 0
cmdclose.Visible = False
UserPassword.Visible = True
UserPassword.SetFocus
End Sub

Private Sub UserPassword_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then UserPassword_LostFocus: Exit Sub
End Sub

Private Sub UserPassword_LostFocus()
Dim StrDocName As String
    Dim stLinkCriteria As String
    StrDocName = "Your normal start form"
    If UserPassword.Text = "your user password" Then
    UserT = 0
    Call SetStartupPropertiesGeneral
           cmdacsdb
           ElseIf UserPassword.Text = "your admin password" Then
           Call SetStartupPropertiesAdmin
    UserT = 1
      cmdacsdb
   Else
End
End If
End Sub

Sub SetStartupPropertiesGeneral()
   
 ChangeProperty "StartupForm", dbText, "logon"
    ChangeProperty "StartupShowDBWindow", dbBoolean, False
    ChangeProperty "StartupShowStatusBar", dbBoolean, False
    ChangeProperty "AllowBuiltinToolbars", dbBoolean, False
    ChangeProperty "AllowFullMenus", dbBoolean, False
    ChangeProperty "AllowBreakIntoCode", dbBoolean, False
    ChangeProperty "AllowSpecialKeys", dbBoolean, False
    ChangeProperty "AllowBypassKey", dbBoolean, False
End Sub

Sub SetStartupPropertiesAdmin()
        ChangeProperty "AllowBypassKey", dbBoolean, True
End Sub

Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
    Dim dbs As Object, prp As Variant
    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

Commented:
Just a further note:

You see that I have a variable UserT an Integer that is either 0 or 1 depending on if the password is  general or Admin. If you declare UserT as Global it will be passed to other forms and can be used to show or hide other buttons etc.

Also as the logon form is modal it will need to be closed after supllying the correct password has opened up your main form. The user will not be able to use the application until the modal logon form is closed. Set the properties to Popup as well and no other program can be accessed until it is closed.

Remember that the change property settings only take effect the second time the application is opened

Rick

Commented:
Hi InvisibleMan,

By default I set all my databases to not allow the bypass.  I use an entry in the autoexec macro to set allowbypass to false.

How do I then get in?

My databases have an exit form.  Their is a trasparent label on this form that when clicked sets the AllowByPass to true.

The Autoexec Macro has this entry
      Action:  RunCode
      FunctionName: SetStartup()

This Macro sets the AllowByPass to false every time the database is opened and the autoexec macro allowed to run.  The autoexec macro does not run when you hold down the shift key.

Next Create a module and copy this into it.

'********************Start of Code*************************
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

Function SetStartup()
ChangeProperty "AllowBypassKey", dbBoolean, False
End Function

Function SetStartupProperties()
ChangeProperty "AllowBypassKey", dbBoolean, True
End Function


'******************End of Code**************************

Then create a transparent label on any form in your database (I use the exit form)  and on its On Click  or On Dbl Click type: =SetStartupProperties()

This sets the AllowByPass to True.

Then the next time you open the database hold down the Shift key.

Drawbacks. (a) The user should not be aware of the existence of the tranparent label or its function.  

(b)  To allowbypass you must first start the database and then set the allowbaypass to true by clicking on the transparent label, close the database and then open it holding down the shift key.

(c)  You must remember to reset the allowbypass to false by simply opening the database (without holding down the shift key)

Hope you find this helpful
Mavreich

Author

Commented:
All good answers none seem to work.  I like Mavreich answer but its not working.  I hold shift and it still goes right to the database window.  I think you have to create property or something.  But none of these stop the user from holding down the shift key on the open of the database.  This is the issue I have and the main problem.  Mavreich I dont think yours works as you say the autoexec does not run.  What I need to do is have the bypass set to false immediatly when the database is being opened and the user holds the shift key.  Then based on some login code I have allow the shift to happen or the unlock.

Commented:
You cannot set this function via the auto exec macro It must be set from code.

If you give me the name of your normal startup form that you want all users to use I will make up the code for you and a form that you can download with all the comments to ensure success

Rick

Commented:
<<What I need to do is have the bypass set to false immediatly when the database is being opened and the user holds the shift key>>

This can't be done. Holding the shift key prevents startup forms, macros or any code from running when the database opens. There is no way for the database to know if the shift is pressed when it opens. Access checks the AllowBypassKey property of the database when opening it. Also this property does not take effect until the next time the database is opened.

If this is a shared database where the admin user is allowed to use the bypass key, then it must be a split database with the front end on each users pc. Otherwise every time the admin opens the db, the AllowBypassKey property is set to false, which will allow all users to use the bypass.

Also this property should be set when the db closes. This means having a startup form which must always be open. Closing this form sets this property according to user type or group, then closes the db or Access.

If the db is secured with a .mdw you can check if the user belongs to the Admin group using ADOX or DAO. If it is not secured then you will need a table that stores user name and type since all users are admins in an unsecured db.

Commented:
qwqaw - wrong you can stop the shift key from by passing the start up. My code posted here is in a form called logon which is the startup form.

Normally holding the shift key would bypass the code in the form. However when you set the proerties of the form to modal and popup with the focus set on the password field, the modal form code must be run before any other action can be taken not only in access but also in windows.

once the  user password is entered the change property code is run and the shift key is de-activated.

As you say the code must be run once to set the allowbypasskey to false before it is given to the user to work with.

I have three applications now running with this code in place and it works very well. It is even more secure if the application is converted into an MDE file.

I will make a sample code available soon for download

Rick

Commented:
I have made a sample mdb file -secure.mdb for download at

http://www.aerocom-int.com/anonftp/secure.mdb

The mdb file as downloaded is set for admin  so that code can be viewed.

paste the logon form into your own application, change the values of userpwd and adminpwd to your own password strings

and the value of dbform to that of your own normal  startup form.

change the startup object to the logon form. close the database and open it normally. Enter the user password  and then close the database. The bypasskey is now disabled.

If you try to open the database by holding down the shift key you will only get the logon form. To re-enable the shift key enter the admin password start and close the database. Now you will be able to open it and view the database window by holding down the shift key.

Make sure that you activate the user mode by opening with the user password  at least once before you distribute it.

Hope this clears up how it is done for everybody

Rick

Commented:
by the way the passwords are set as "user" or "admin" and the default database form as "FIRST FORM"

These are the only values you need to change to customise the form for your personal use

Rick

Commented:
InvisibleMan are you still out there is cyberspace. How about closing this open question. Did you try the code that I made available for ?.

its now 2 months since you responded!!

Are you still there????

Commented:
for InvisibleMan

It's time to clean up this TA, so I will leave a recommendation in Community Support that this question is:
 - Answered by: dovholuk  
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
Force accepted

** Mindphaser - Community Support Moderator **

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