Comparing LanUserID to a tblUsers UserID

[MikeJfromESVA]

I have database the needs User-level Security. We all are on a LAN which we login in to enter the common network.

I want to use the LoginID and compare it to a tblUsers UserID in order to grant entry into my database without generating another password.

How do I capture the LanUserID and then compare it to the tblUsers UserID.

Remember I'm new to Access 2000 VBA code writing

 

[ghudson]

Environ("UserName") will give you the users network name.

You could use a Dcount of the tblUsers table and your Dcount criteria would be the Environ("UserName"). If the count is > 0 then the user's network name must be in your tblUsers table. Check the Access help files for more info and examples on using the Dcount function.

Using Access security with workgroups and user permissions is the best way to "secure" your database. Anything else will not prevent somebody from linking to your db and extracting out anything they want.

 

[NimbusSoftware]

Here 'ya go:

Option Base 0
Option Compare Database
Option Explicit
Option Private Module

Private Declare Function SysGetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
   (ByVal lpBuffer As String, nSize As Long) As Long

'  Double-quote Chr$(34)
Global Const DBQ As String = """"

'***********************************************************************************************
' GetUserName (Public Function)
'
' PARAMETERS:
'  None
'
' RETURN VALUE:
'  String
'
' DEPENDENCIES:
'  None
'
' REFERENCES (in this order):
'  Visual Basic for Applications
'  Microsoft Access 9.0 Object Library
'
' DESCRIPTION:
'  Returns the user name
'
' MODIFICATION HISTORY:
'  June 2000
'  Daniel J. Pinter
'     Initial Version
'     Original code can be found at [url]www.mvps.org/access/api/api0008.htm[/url]
'***********************************************************************************************
Public Function GetUserName() As String
   On Error GoTo ErrorHandler
   Dim strTemp As String * 80

   If SysGetUserName(strTemp, Len(strTemp)) = 0 Then
      GetUserName = "<Non-Network User>"
   Else
      GetUserName = DBQ & Left$(strTemp, InStr(strTemp, Chr$(0)) - 1) & DBQ
   End If
   GoTo ExitHere

ErrorHandler:
   msgbox "basGetUserName" & vbnewline & "GetUserName"

ExitHere:
End Function

 

[MikeJfromESVA]

I see how to get GetUserFunction but...........

How do compare it to UserId in by tblUsers table inside by database?

 

[gecko_1]

If DCount("[userID]", "tblUsers", "[userID] = '" & Environ("UserName") & "'") Then
    DoCmd.OpenForm "frmMenu"
Else
    MsgBox "Access Denied"
    DoCmd.Close
end if

place this in the startup form's OnLoad event

 

[ghudson]

NimbusSoftware, why use that old code when Environ() does more for less?
gecko_1, that will not work.
MikeJfromESVA, try this in the forms OnOpen event...

Private Sub Form_Open(Cancel As Integer)
    
    If DCount("[UserID]", "tblUsers", "[UserID] = " & "'" & Environ("UserName") & "'") > 0 Then
        'MsgBox "User found" 'used for testing
        DoCmd.OpenForm "frmMenu"
    Else
        MsgBox "Access Denied"
        DoCmd.Close acForm, Me.name
    End If
    
End Sub

 

[dt01pqt]

This is a bit of a fruitless exercise like ghudson says this will not secure your database. However to log the access passwords with the network passwords is a good way to investigate possible malpractice.

 

[NimbusSoftware]

NimbusSoftware, why use that old code when Environ() does more for less?
gecko_1, that will not work.
MikeJfromESVA, try this in the forms OnOpen event...

Private Sub Form_Open(Cancel As Integer)
    
    If DCount("[UserID]", "tblUsers", "[UserID] = " & "'" & Environ("UserName") & "'") > 0 Then
        'MsgBox "User found" 'used for testing
        DoCmd.OpenForm "frmMenu"
    Else
        MsgBox "Access Denied"
        DoCmd.Close acForm, Me.name
    End If
    
End Sub

(cough cough) i don't any more (cough cough)
That was the offical Micro$suck code.