B
boborta
OS: Windows XP Pro
Access: 2002 SP3
Configuration: All databases are front/back end configurations.
All databases are linked to an Access LAN security database, which
stores LAN Id, user information - including a password.
Database startup:
Startup form requires user to login with their LAN Id and password
(both stored in mdb mentioned above). The form gives users three
chances to login correctly, otherwise, they are kicked out. The login
process uses the following code:
Private Sub RealPW_AfterUpdate()
'-------------------------------------------------------------
' Purpose : Checks user LAN Id and Password against the LAN security
table.
' If successful, form hides and main menu opens. Else, user gets 2
more trys before
' they are forced out of the database.
' Author : Bob
' Phone:
' E-Mail:
' Notes :
' Calls: dbLookup
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' 5/31/2007 RO:
'=============================================================
Dim PWValidate As Variant
Dim intCounter As Integer
Dim strLanID As String
Dim strPwd As String
On Error GoTo err_RealPW_AfterUpdate
DoCmd.Hourglass True
strLanID = Me.UserID
strPwd = Me.RealPW
PWValidate = dbLookup("password", "SELECT
d_LAN_Security_IDs.password " _
& "FROM d_LAN_Security_IDs " _
& "WHERE lan_id='" & strLanID & "' AND password='" & strPwd &
"';")
If PWValidate = Me.RealPW Then
fnRecordUserLogin (strLanID)
DoCmd.Close acForm, "frmLogIn"
' Me.Visible = False
DoCmd.OpenForm "frmMainMenu"
Else
If intCounter < 3 Then
MsgBox "Invalid login, check and reenter your USERID or
Password", vbCritical, strAppName
intCounter = intCounter + 1
Me!RealPW = ""
Me!UserID = ""
Else
DoCmd.Quit
End If
End If
exit_RealPW_AfterUpdate:
DoCmd.Hourglass False
Exit Sub
err_RealPW_AfterUpdate:
MsgBox Err.Number & Err.Description
Resume exit_RealPW_AfterUpdate
End Sub
The dblookup (in part):
Dim rst As DAO.Recordset
Dim db As DAO.Database
On Error GoTo HandleErr
Set db = CurrentDb()
Set rst = db.OpenRecordset(SQLExpression, dbOpenSnapshot,
dbReadOnly)
If rst.EOF Then
dbLookup = DefaultValue
Else
dbLookup = rst(FieldName) & ""
End If
rst.Close
If the password entered matches the password stored in the security
table, the user gets logged in.
Our LAN (UNIX) security setup:
All users are granted permissions to read the Access security database
and are granted read and write in the remaining databases.
The problem we are having occurs if either Access developer has the
Access security database open. This prevents users from opening other
production databases. The query that is run using dbLoopup (above)
cannot access the d_LAN_Security_IDs table.
Error message: 91 Object variable or with block variable not set.
Attempting to open the link table at this point causes the following
error message: Could not use '................(the database path/name here)';
file already in use.
Thanks for looking at this.
Bob
Access: 2002 SP3
Configuration: All databases are front/back end configurations.
All databases are linked to an Access LAN security database, which
stores LAN Id, user information - including a password.
Database startup:
Startup form requires user to login with their LAN Id and password
(both stored in mdb mentioned above). The form gives users three
chances to login correctly, otherwise, they are kicked out. The login
process uses the following code:
Private Sub RealPW_AfterUpdate()
'-------------------------------------------------------------
' Purpose : Checks user LAN Id and Password against the LAN security
table.
' If successful, form hides and main menu opens. Else, user gets 2
more trys before
' they are forced out of the database.
' Author : Bob
' Phone:
' E-Mail:
' Notes :
' Calls: dbLookup
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' 5/31/2007 RO:
'=============================================================
Dim PWValidate As Variant
Dim intCounter As Integer
Dim strLanID As String
Dim strPwd As String
On Error GoTo err_RealPW_AfterUpdate
DoCmd.Hourglass True
strLanID = Me.UserID
strPwd = Me.RealPW
PWValidate = dbLookup("password", "SELECT
d_LAN_Security_IDs.password " _
& "FROM d_LAN_Security_IDs " _
& "WHERE lan_id='" & strLanID & "' AND password='" & strPwd &
"';")
If PWValidate = Me.RealPW Then
fnRecordUserLogin (strLanID)
DoCmd.Close acForm, "frmLogIn"
' Me.Visible = False
DoCmd.OpenForm "frmMainMenu"
Else
If intCounter < 3 Then
MsgBox "Invalid login, check and reenter your USERID or
Password", vbCritical, strAppName
intCounter = intCounter + 1
Me!RealPW = ""
Me!UserID = ""
Else
DoCmd.Quit
End If
End If
exit_RealPW_AfterUpdate:
DoCmd.Hourglass False
Exit Sub
err_RealPW_AfterUpdate:
MsgBox Err.Number & Err.Description
Resume exit_RealPW_AfterUpdate
End Sub
The dblookup (in part):
Dim rst As DAO.Recordset
Dim db As DAO.Database
On Error GoTo HandleErr
Set db = CurrentDb()
Set rst = db.OpenRecordset(SQLExpression, dbOpenSnapshot,
dbReadOnly)
If rst.EOF Then
dbLookup = DefaultValue
Else
dbLookup = rst(FieldName) & ""
End If
rst.Close
If the password entered matches the password stored in the security
table, the user gets logged in.
Our LAN (UNIX) security setup:
All users are granted permissions to read the Access security database
and are granted read and write in the remaining databases.
The problem we are having occurs if either Access developer has the
Access security database open. This prevents users from opening other
production databases. The query that is run using dbLoopup (above)
cannot access the d_LAN_Security_IDs table.
Error message: 91 Object variable or with block variable not set.
Attempting to open the link table at this point causes the following
error message: Could not use '................(the database path/name here)';
file already in use.
Thanks for looking at this.
Bob