Limit access to a database to a few users. (1 Viewer)

[in22]

IsDevelepor is a separate function with this code:

Function IsDevelepor() As Boolean

Dim UserName As String

IsDevelepor = False
UserName = VBA.Environ("UserName")

Select Case UserName
Case "nasiri1"
IsDeveloper = True
Case Else
IsDeveloper = False
End Select

End Function

 

[GinaWhipp]

Oops, forgot that part (will REALLY have to update that page! Here you go...

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

 

[in22]

I tried to hide navigation pane in case a non admin is using the database. (Which I believe the IsDevelepor function does). I modified the code as follows:

If IsDeveloper Then
ChangeProperty "AllowBypassKey", dbBoolean, True
DoCmd.SelectObject acTable, , True
Else
ChangeProperty "AllowBypassKey", dbBoolean, False
DoCmd.NavigateTo "acNavigationCategoryObjectType"
DoCmd.RunCommand acCmdWindowHide
End If

However when I test the database by closing and opening it again, navigation pane is hidden although I am logged on as the same user. Shouldn't the navigation be visible since I have used the Case "nasiri1" as True? (My last post)

 

[GinaWhipp]

Yes, it should be visible. AND, I gather by your reply it is not... just asd a test. close and reopen, did that help? I use this code in Access 2010, it won't be the first time I have to make adjustments to code because of Access 2007, it has its own special issues...

 

[in22]

Nope. Didn't help. I know Access 2007 is such a pain...

 

[GinaWhipp]

I am going to have till later when I can get to Access 2007 and figure out why, oh why...

 

[in22]

Meanwhile do you know why Me.Visible = False would not work in the following code:

If Not rst.BOF And Not rst.EOF Then
rst.Edit
rst.Fields("LogonCount") = rst.Fields("LogonCount") + 1
rst.Fields("LastLogon") = Now()
rst.Update

Me.txtSecurityID = rst.Fields("SecurityID")
If Me.txtSecurityID <> 3 Then
DoCmd.NavigateTo "acNavigationCategoryObjectType"
DoCmd.RunCommand acCmdWindowHide
Else
DoCmd.SelectObject acTable, , True
End If
Me.txtUserID = rst.Fields("UserID")
Me.txtOverride = rst.Fields("SpecialPermissions")
Me.txtPassword = rst.Fields("Password")

DoEvents
Else
Me.Dirty = False
Me.Visible = False
DoCmd.NavigateTo "acNavigationCategoryObjectType"
DoCmd.RunCommand acCmdWindowHide
DoCmd.OpenForm "frmNewUser", acNormal, , , , acWindowNormal


Do Until Me.Tag = "Continue"
DoEvents
Loop
Me.Visible = True
Me.txtSecurityID = rst.Fields("SecurityID")
If Me.txtSecurityID <> 3 Then
DoCmd.NavigateTo "acNavigationCategoryObjectType"
DoCmd.RunCommand acCmdWindowHide
Else
DoCmd.SelectObject acTable, , True
End If
Me.txtUserID = rst.Fields("UserID")
Me.txtOverride = rst.Fields("SpecialPermissions")
Me.txtPassword = rst.Fields("Password")

End If

If Me.txtSecurityID <> 3 Then
Me.AllowEdits = False
Else
Me.AllowEdits = True
End If

When I open the database from a machine whose user name (NetworkID) is still not captured by the code, it (should and) does take me to the New User form. However it does not hide the frmMain form temporarily....I can't figure why...

 

[GinaWhipp]

Okay, I just had a chance to read the code... The Navigation Pane should only show if you use the ByPass Key. It will not show if you just double-click. It is to prevent *average* Users from using the ByPass Key and getting to the Navigation Pane.

 

[in22]

The navigation seems to work fine. The Me.Visible = False is for the Main form (frmMain) to temporarily become invisible and the New User form (frmNewUser) to pop up in case a non-admin opens the database. This part works fine, except that when the New User form opens the Main form doesn't hide, and both forms are open at the same time...

 

[nanscombe]

A DoEvents after the Me.Visible = False might do the trick, it may allow the visibility change to take place before executing the next command.


Is the code snippet below being used to stop the code in frmMain from moving on until frmNewUser is closed?

Do Until Me.Tag = "Continue"
DoEvents
Loop

If so you could just open frmNewUser as a Dialog instead which stops the calling form executing any code until frmNewUser is closed.

 DoCmd.OpenForm "frmNewUser", acNormal, , , , acDialog

 

[in22]

That worked perfect!

Thanks

 

[GinaWhipp]

@in22... That must be in Access 2007 because it works as expected in Access 2010, been using it for years. However, you have it modified to look for whehter a User is an ADMIN. Perhaps you could post the modification of what you did OR I see Nigel has posted a some code which should work BUT if it doesn't doesn't... please post the modifications.

EDIT: I see I was too slow!

 

[GinaWhipp]

Just as a side note...

I found out why the *Main Menu* was not showing. You used...

Me.Dirty = False
Me.Visible = False
DoCmd.NavigateTo "acNavigationCategoryObjectType"
DoCmd.RunCommand acCmdWindowHide
DoCmd.OpenForm "frmNewUser", acNormal, , , , acWindowNormal

...and it should have been...

DoCmd.OpenForm "frmNewUser", acNormal, , , , acWindowNormal
Me.Dirty = False
Me.Visible = False
'and these two line aren't in my code though they should do fine here...
DoCmd.NavigateTo "acNavigationCategoryObjectType"
DoCmd.RunCommand acCmdWindowHide

When modifying code there is sometimes a reason they are in the order they are in. It can make all the difference in the world. Hopefully, that bit of advice will help you along the way!

 

[in22]

I'm almost done with the entire 'securing the database' goal. I'm using the following code in each form to restrict access for users:

Option Compare Database

Private Sub cmdMainMenu_Click()

DoCmd.Close acForm, Me.Name
DoCmd.SelectObject acForm, "frmMain"
DoCmd.Restore

End Sub

Private Sub Form_Load()

If Forms![frmMain]![txtSecurityID] <> 3 Then
Me.AllowAdditions = False
Me.AllowDeletions = False
Me.AllowEdits = False
'Me.RecordLocks = True
End If

End Sub

And for this to work I'm minimizing the main menu form:

Private Sub cmdSPSClow_Click()

DoCmd.SelectObject acForm, Me.Name
DoCmd.Minimize
DoCmd.OpenForm "SPSC Weekly Low Priority - Current Month", acNormal, , , , acWindowNormal

End Sub

I have two Questions
1 - Although I can lock records on the from for editing with Me.AllowEdits, what is the recordlock function for? (When I had it active VBA would error out as not valid for this type...)
2 - How can I use the same method above (using the txtSecurityID as reference) to hide the toolbar for non-admin users? (I don't wan't them to go into design view etc.)

 

[GinaWhipp]

1. No need to use for what you're trying to do. To learn about what it is...
http://msdn.microsoft.com/en-us/library/office/bb215995(v=office.12).aspx

2. To hide the Ribbon, use this line...

DoCmd.ShowToolbar "Ribbon", acToolbarNo

...in the appropiate places.

 

[DavidAtWork]

It seems your txtSecurityID is the key to you controlling future admin secure events, what happens if the admin user closes frmMain, if they have access to the database window it's entirely possible they could close frmMain.
I've always found using a hidden form that loads in a hidden state when the database opens, remains open until the database closes, this would be the place to have your txtSecurityID and it also has the advantage that you can perform certain functions when the database closes which can be executed using the hidden form's unload event, functions such as restoring the database to locked down state (disabling the bypass key), restoring the Access menus/toolbars etc

David

 

[GinaWhipp]

@David,

Easy enough to fix, just disable the built in Close button on all your Forms. Then put your own Close button, which, depending on which Form, either thows them out of the database or lands them back on the Main Menu.

 

[in22]

I finally made everything work. Worked perfect in fact!

However starting this morning there's a strange prob. The code and everything is the same but when I open the database (I'm an admin), the main form is not showing. In the taskbar in the bottom left it does say 'Form view', which suggests for someone reason the form is hidden...Is that a bug within Access? Closing and reopening didn't work. Any ideas?

 

[in22]

@David

Thanks for the heads up. I actually did think about but then as Gina said disabled the close button. There's no way for any user to close the main form (or any form for that matter). If they want to access certain form the main form has a button for it. I minimize the main form so the txtSecurityID can be captured on the other form load. And then every form has the 'Back to Main Menu' option which closes that form and restores the main form. Had to copy paste a few lines of code for each form and was time consuming but worth it...

 

[in22]

If I open any other form and hit 'back to main menu' button, the main form still isn't showing!