mjohnson1105
New member
- Local time
- Today, 09:16
- Joined
- Jun 6, 2013
- Messages
- 8
Hi,
I created a database for the HR department, however it contains personal information. I am trying to write VBA code that will only allow certain users certain access. Someone emailed me some coding and I really need help understanding it. The logic is that he created two tables that stores user's network names and ID numbers. The VBA code validates whether a person has access to certain tables and etc based on their network name and ID number. See below message for more info. Thanks.
Table 1 (tblAccess)
Columns:
Id
Networkname
Tablename
Read_write
Tbale 2 (tblAdmins)
Column:
networkname
Functions in VBA:
Public Function CheckAdmins() As Boolean
Dim rs As ADODB.Recordset
Dim c As String
If Not ConnectProductionDatabase() Then
MsgBox "Issue with connecting to the database"
Exit Function
End If
Set rs = New ADODB.Recordset
c = "select count(id) as ADMINS from tblAdmins where NetworkName = '" &
GetUserName & "';"
rs.ActiveConnection = conn
rs.Source = c
rs.CursorLocation = adUseServer
rs.LockType = adLockOptimistic
rs.CursorType = adOpenDynamic
rs.Open
CheckAdmins = False
If rs!ADMINS = 1 Then
CheckAdmins = True
End If
rs.Close
Set rs = Nothing
End Function
Above checks to see if user is authorized administrative privileges within the
database
Below captures network ids
Function GetUserName()
Const lpnLength As Integer = 255
Dim STATUS As Integer
Dim lpName, lpUserName As String
lpUserName = Space$(lpnLength + 1)
STATUS = WNetGetUser(lpName, lpUserName, lpnLength)
If STATUS = NoError Then
lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
Else
MsgBox "Unable to get the name."
end
End If
GetUserName = lpUserName
End Function
Unhideit allows admin into back end of Access
Public Function UnHideIt()
If IsDisableShift Then
If MsgBox("Do you want to Enable Shift", vbYesNo) = vbYes Then
ap_EnableShift
End If
End If
DoCmd.SelectObject acTable, "tblxxx", True
DoCmd.RunCommand acCmdWindowUnhide
End Function
HideIt blocks users from getting to access objects forcing them to only be in
the Forms from a driven menu
Public Function HideIt()
ap_DisableShift
DoCmd.SelectObject acTable, "tblxxx", True
DoCmd.RunCommand acCmdWindowHide
End Function
I created a database for the HR department, however it contains personal information. I am trying to write VBA code that will only allow certain users certain access. Someone emailed me some coding and I really need help understanding it. The logic is that he created two tables that stores user's network names and ID numbers. The VBA code validates whether a person has access to certain tables and etc based on their network name and ID number. See below message for more info. Thanks.
Table 1 (tblAccess)
Columns:
Id
Networkname
Tablename
Read_write
Tbale 2 (tblAdmins)
Column:
networkname
Functions in VBA:
Public Function CheckAdmins() As Boolean
Dim rs As ADODB.Recordset
Dim c As String
If Not ConnectProductionDatabase() Then
MsgBox "Issue with connecting to the database"
Exit Function
End If
Set rs = New ADODB.Recordset
c = "select count(id) as ADMINS from tblAdmins where NetworkName = '" &
GetUserName & "';"
rs.ActiveConnection = conn
rs.Source = c
rs.CursorLocation = adUseServer
rs.LockType = adLockOptimistic
rs.CursorType = adOpenDynamic
rs.Open
CheckAdmins = False
If rs!ADMINS = 1 Then
CheckAdmins = True
End If
rs.Close
Set rs = Nothing
End Function
Above checks to see if user is authorized administrative privileges within the
database
Below captures network ids
Function GetUserName()
Const lpnLength As Integer = 255
Dim STATUS As Integer
Dim lpName, lpUserName As String
lpUserName = Space$(lpnLength + 1)
STATUS = WNetGetUser(lpName, lpUserName, lpnLength)
If STATUS = NoError Then
lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
Else
MsgBox "Unable to get the name."
end
End If
GetUserName = lpUserName
End Function
Unhideit allows admin into back end of Access
Public Function UnHideIt()
If IsDisableShift Then
If MsgBox("Do you want to Enable Shift", vbYesNo) = vbYes Then
ap_EnableShift
End If
End If
DoCmd.SelectObject acTable, "tblxxx", True
DoCmd.RunCommand acCmdWindowUnhide
End Function
HideIt blocks users from getting to access objects forcing them to only be in
the Forms from a driven menu
Public Function HideIt()
ap_DisableShift
DoCmd.SelectObject acTable, "tblxxx", True
DoCmd.RunCommand acCmdWindowHide
End Function