Why is this so slow

[Mark]

Hi Guru's,
Firstly, I'll apologise for the long post.
The scenario is that along with Access built in security, I have built my
own so to make it easier to enable/disable permissions on certain command
buttons, forms and enable accurate logging of useage. The problem is that
since I have added my own security, the database has gone really slow. For
example, when the database is opened, a login form appears (that bit is
normal speed). The user types in a userid and presses enter. The after
update property of the textbox uses a Dlookup function to make sure the
username is valid. It is normal at the moment to wait anything up to 20
seconds before the userid is validated.

The database is split on a XP platform using offive XP.

I am quite new to programming so a possibility is that my coding is the
cause of the sluggish behaviour. Below is the code I have on the userid
textbox after update property but it is not isolated to just this form. Can
anyone offer any advice as to what is wrong????

Private Sub userinput_AfterUpdate()
' Validate user id (name)
If IsNull(Me.userinput.Value) Or Me.userinput.Value = "" Then
MsgBox "Enter your User ID (Name) in the User ID field.", vbOKOnly,
"Missing User ID"
Me.passwordinput.Value = Null
Me.passwordinput.Visible = False
Me.userinput.SetFocus
Exit Sub
End If

' count user id entry attempts
If IsNull(DLookup("[userid]", "accees_ids", "[UserID]='" &
Me.userinput.Value & "'")) Then
Me.uidchk.Value = Me.uidchk.Value + 1
If Me.uidchk.Value > 3 Then
MsgBox "You may not make more than three User ID entry
attempts.", vbCritical + vbOKOnly, "Oops!"
DoCmd.Quit
End If
MsgBox Me.userinput.Value & " is not a valid account" & vbCrLf & _
"You have obviously not been given an account so please hit
the cancel button!!! ", vbOKOnly, "User ID " & Me.userinput.Value & "
Invalid"
Me.userinput.Value = Null
Exit Sub
End If

'Check to see if the password needs changing
If DLookup("[pwd]", "accees_ids", "[userid] = forms!access_login!userinput")
= Forms!access_login!userinput Then
MsgBox "As this is the first time you have used the database," _
& vbCrLf & "You are required to set a password", vbCritical = vbOKOnly,
"Change password"
Me.txtNewPW.Visible = True
Me.passwordinput.Visible = False
Me.openmain.Visible = False
Exit Sub
End If
'check to see if the password has expired
If DateDiff("m", DLookup("[chng_pwd]", "accees_ids", "[userid] =
forms!access_login!userinput"), Now()) >= 3 Then
MsgBox "3 months have passed!! You need to change your password",
vbCritical = vbOKOnly, "Expired Password"
Me.txtNewPW.Visible = True
Me.passwordinput.Visible = False
Me.openmain.Visible = False
Exit Sub
End If
' user id exists, continue
Me.passwordinput.Visible = True
Me.passwordinput.SetFocus

End Sub

Many thanks in advance,

Mark

[Allen Browne]

Hi Mark.

Without going into your code in detail, I doubt that it is causing a delay
of several seconds. This is much more likely to be related to other factors
such as NameAutoCorrect, the loading of the form, the Subdatasheet names,
the length of the network path, the non-caching of paths in Windows XP, etc.

For details, see Tony Toews' "Performance FAQ" at:
http://www.granite.ab.ca/access/performancefaq.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark" <ma*********@ntlworld.com> wrote in message
news:7l*********@newsfe3-win.ntli.net...

Hi Guru's,
Firstly, I'll apologise for the long post.
The scenario is that along with Access built in security, I have built my
own so to make it easier to enable/disable permissions on certain command
buttons, forms and enable accurate logging of useage. The problem is that
since I have added my own security, the database has gone really slow. For
example, when the database is opened, a login form appears (that bit is
normal speed). The user types in a userid and presses enter. The after
update property of the textbox uses a Dlookup function to make sure the
username is valid. It is normal at the moment to wait anything up to 20
seconds before the userid is validated.

The database is split on a XP platform using offive XP.

I am quite new to programming so a possibility is that my coding is the
cause of the sluggish behaviour. Below is the code I have on the userid
textbox after update property but it is not isolated to just this form.
Can
anyone offer any advice as to what is wrong????

Private Sub userinput_AfterUpdate()
' Validate user id (name)
If IsNull(Me.userinput.Value) Or Me.userinput.Value = "" Then
MsgBox "Enter your User ID (Name) in the User ID field.", vbOKOnly,
"Missing User ID"
Me.passwordinput.Value = Null
Me.passwordinput.Visible = False
Me.userinput.SetFocus
Exit Sub
End If

' count user id entry attempts
If IsNull(DLookup("[userid]", "accees_ids", "[UserID]='" &
Me.userinput.Value & "'")) Then
Me.uidchk.Value = Me.uidchk.Value + 1
If Me.uidchk.Value > 3 Then
MsgBox "You may not make more than three User ID entry
attempts.", vbCritical + vbOKOnly, "Oops!"
DoCmd.Quit
End If
MsgBox Me.userinput.Value & " is not a valid account" & vbCrLf & _
"You have obviously not been given an account so please hit
the cancel button!!! ", vbOKOnly, "User ID " & Me.userinput.Value & "
Invalid"
Me.userinput.Value = Null
Exit Sub
End If

'Check to see if the password needs changing
If DLookup("[pwd]", "accees_ids", "[userid] =
forms!access_login!userinput")
= Forms!access_login!userinput Then
MsgBox "As this is the first time you have used the database," _
& vbCrLf & "You are required to set a password", vbCritical = vbOKOnly,
"Change password"
Me.txtNewPW.Visible = True
Me.passwordinput.Visible = False
Me.openmain.Visible = False
Exit Sub
End If
'check to see if the password has expired
If DateDiff("m", DLookup("[chng_pwd]", "accees_ids", "[userid] =
forms!access_login!userinput"), Now()) >= 3 Then
MsgBox "3 months have passed!! You need to change your password",
vbCritical = vbOKOnly, "Expired Password"
Me.txtNewPW.Visible = True
Me.passwordinput.Visible = False
Me.openmain.Visible = False
Exit Sub
End If
' user id exists, continue
Me.passwordinput.Visible = True
Me.passwordinput.SetFocus

End Sub

Many thanks in advance,

Mark

[Pieter Linden]

"Mark" <ma*********@ntlworld.com> wrote in message news:<7l*********@newsfe3-win.ntli.net>...

Hi Guru's,
Firstly, I'll apologise for the long post.
The scenario is that along with Access built in security, I have built my
own so to make it easier to enable/disable permissions on certain command
buttons, forms and enable accurate logging of useage. The problem is that
since I have added my own security, the database has gone really slow. For
example, when the database is opened, a login form appears (that bit is
normal speed). The user types in a userid and presses enter. The after
update property of the textbox uses a Dlookup function to make sure the
username is valid. It is normal at the moment to wait anything up to 20
seconds before the userid is validated.

The database is split on a XP platform using offive XP.

I am quite new to programming so a possibility is that my coding is the
cause of the sluggish behaviour. Below is the code I have on the userid
textbox after update property but it is not isolated to just this form. Can
anyone offer any advice as to what is wrong????

Private Sub userinput_AfterUpdate()
' Validate user id (name)
If IsNull(Me.userinput.Value) Or Me.userinput.Value = "" Then
MsgBox "Enter your User ID (Name) in the User ID field.", vbOKOnly,
"Missing User ID"
Me.passwordinput.Value = Null
Me.passwordinput.Visible = False
Me.userinput.SetFocus
Exit Sub
End If

' count user id entry attempts
If IsNull(DLookup("[userid]", "accees_ids", "[UserID]='" &
Me.userinput.Value & "'")) Then
Me.uidchk.Value = Me.uidchk.Value + 1
If Me.uidchk.Value > 3 Then
MsgBox "You may not make more than three User ID entry
attempts.", vbCritical + vbOKOnly, "Oops!"
DoCmd.Quit
End If
MsgBox Me.userinput.Value & " is not a valid account" & vbCrLf & _
"You have obviously not been given an account so please hit
the cancel button!!! ", vbOKOnly, "User ID " & Me.userinput.Value & "
Invalid"
Me.userinput.Value = Null
Exit Sub
End If

'Check to see if the password needs changing
If DLookup("[pwd]", "accees_ids", "[userid] = forms!access_login!userinput")
= Forms!access_login!userinput Then
MsgBox "As this is the first time you have used the database," _
& vbCrLf & "You are required to set a password", vbCritical = vbOKOnly,
"Change password"
Me.txtNewPW.Visible = True
Me.passwordinput.Visible = False
Me.openmain.Visible = False
Exit Sub
End If
'check to see if the password has expired
If DateDiff("m", DLookup("[chng_pwd]", "accees_ids", "[userid] =
forms!access_login!userinput"), Now()) >= 3 Then
MsgBox "3 months have passed!! You need to change your password",
vbCritical = vbOKOnly, "Expired Password"
Me.txtNewPW.Visible = True
Me.passwordinput.Visible = False
Me.openmain.Visible = False
Exit Sub
End If
' user id exists, continue
Me.passwordinput.Visible = True
Me.passwordinput.SetFocus

End Sub

Many thanks in advance,

Mark

It might be faster if you opened an updateable recordset based off the
single value and then you could manipulate all the info in memory
*already* instead of rereading from the tables using DLookup. Then if
you change anything, write it back to the record and update.

[Mark]

Hi all,
I went to Toney Toews' site and followed some of the performance tips
given. Eventually, I found that by opening a form bound to a table in the
backend first (it was set to invisible so no-one can see it) and then
opening the login form, the form responded almost instantaneously and has
cured all performance issues.

Thanks all for your advice

Mark

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

Hi Mark.

Without going into your code in detail, I doubt that it is causing a delay
of several seconds. This is much more likely to be related to other factors such as NameAutoCorrect, the loading of the form, the Subdatasheet names,
the length of the network path, the non-caching of paths in Windows XP, etc.
For details, see Tony Toews' "Performance FAQ" at:
http://www.granite.ab.ca/access/performancefaq.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark" <ma*********@ntlworld.com> wrote in message
news:7l*********@newsfe3-win.ntli.net...

Hi Guru's,
Firstly, I'll apologise for the long post.
The scenario is that along with Access built in security, I have built my own so to make it easier to enable/disable permissions on certain command buttons, forms and enable accurate logging of useage. The problem is that since I have added my own security, the database has gone really slow. For example, when the database is opened, a login form appears (that bit is
normal speed). The user types in a userid and presses enter. The after
update property of the textbox uses a Dlookup function to make sure the
username is valid. It is normal at the moment to wait anything up to 20
seconds before the userid is validated.

The database is split on a XP platform using offive XP.

I am quite new to programming so a possibility is that my coding is the
cause of the sluggish behaviour. Below is the code I have on the userid
textbox after update property but it is not isolated to just this form.
Can
anyone offer any advice as to what is wrong????

Private Sub userinput_AfterUpdate()
' Validate user id (name)
If IsNull(Me.userinput.Value) Or Me.userinput.Value = "" Then
MsgBox "Enter your User ID (Name) in the User ID field.", vbOKOnly, "Missing User ID"
Me.passwordinput.Value = Null
Me.passwordinput.Visible = False
Me.userinput.SetFocus
Exit Sub
End If

' count user id entry attempts
If IsNull(DLookup("[userid]", "accees_ids", "[UserID]='" &
Me.userinput.Value & "'")) Then
Me.uidchk.Value = Me.uidchk.Value + 1
If Me.uidchk.Value > 3 Then
MsgBox "You may not make more than three User ID entry
attempts.", vbCritical + vbOKOnly, "Oops!"
DoCmd.Quit
End If
MsgBox Me.userinput.Value & " is not a valid account" & vbCrLf & _ "You have obviously not been given an account so please hit the cancel button!!! ", vbOKOnly, "User ID " & Me.userinput.Value & "
Invalid"
Me.userinput.Value = Null
Exit Sub
End If

'Check to see if the password needs changing
If DLookup("[pwd]", "accees_ids", "[userid] =
forms!access_login!userinput")
= Forms!access_login!userinput Then
MsgBox "As this is the first time you have used the database," _
& vbCrLf & "You are required to set a password", vbCritical = vbOKOnly, "Change password"
Me.txtNewPW.Visible = True
Me.passwordinput.Visible = False
Me.openmain.Visible = False
Exit Sub
End If
'check to see if the password has expired
If DateDiff("m", DLookup("[chng_pwd]", "accees_ids", "[userid] =
forms!access_login!userinput"), Now()) >= 3 Then
MsgBox "3 months have passed!! You need to change your password",
vbCritical = vbOKOnly, "Expired Password"
Me.txtNewPW.Visible = True
Me.passwordinput.Visible = False
Me.openmain.Visible = False
Exit Sub
End If
' user id exists, continue
Me.passwordinput.Visible = True
Me.passwordinput.SetFocus

End Sub

Many thanks in advance,

Mark