Speeding up Access Code

[Cooks43]

Has anyone got any ideas how I could speed up my application.
It is shared over a network and is in Access 97.
The logon has a lot of code which I have tried to make as efficient as possible.

Is there a way to detect if the app is still loading because that is what I think is causing the delay.

On my computer it is sometimes quite fast but for other users it can be really slow.
Sometimes well over a minute wait after the logon ok click.
I have tried compiling all modules but it is still slow.

Even if I could display to the user that the app is still loading while detecting it loading and then when it is finished start the main database so it increases speed of my db.
This would show the user what is going on, currently the wait has no explaination.

[darre1]

well you could have a dialog display just before it starts connecting to the database, and then unload the dialog form when it's connected?

[Cooks43]

What do you mean by connecting to the database.
Its already connect.
The code is behind the logon form.
The form is in the database no connection needed.

[plenderj]

Often one can improve performance when searching through RecordSets.
Do you do anything like that ?

[Cooks43]

I dont think it has much to do with my code because the first thing i do one the ok button is to set the cursor to busy but after clicking the ok button this can take ages to change.

I think its somethin to do with access but i'm not sure.

[plenderj]

Well it really shouldnt take long to make the connection to the database when loading the application.
Takes about a second or something like that for me

[darre1]

what operations are you doing when logging in??

are you sure that it only retrives and checks the records required and not a whole bunch extra?

[Cooks43]

Here is my code


Option Compare Database
Option Explicit
Public Attempt As Integer


Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
DoCmd.Hourglass True 'This should set the cursor to busy immediately

Dim TempPassword
Dim dbs As Database
Dim strSQL As String
Dim rst As Recordset
Dim qdf As QueryDef

Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("Logon")
strSQL = "Select * FROM [tbl - Access Control List] WHERE [File Number]='" & Me.txtUser & "';"
qdf.SQL = strSQL
Set rst = dbs.OpenRecordset("Logon")

If rst.RecordCount < 1 Then
DoCmd.Hourglass False
MsgBox "You do not have a Access Account, Please contact the database administrator", vbInformation
Me.txtUser.SetFocus
GoTo Exit_OK
End If


If rst!Locked = True Then
DoCmd.Hourglass False
MsgBox "Your Acount is Locked, please contact the database Administrator", vbCritical
GoTo Exit_OK
End If

CurrentPassword = rst!Password

If CurrentPassword = Me.txtPassword Then
DoCmd.Hourglass False
MsgBox "Access Granted"
CurrentAccessN = rst![Access TypeN]
If rst!Password = "password" Then
MsgBox "Your password is set to password, please change this soon"
End If
DoCmd.SetWarnings False
DoCmd.OpenQuery ("qry - Update Logon Date")
DoCmd.SetWarnings True
DoCmd.Close
If rst![Last Change] < (Now() - 30) Then
MsgBox "Your password has expired and must be changed"
Change = 1
DoCmd.OpenForm "frm - Change Password"
Else
Change = 0
DoCmd.OpenForm "frm - Main"
End If
Else
If Attempt = 3 Then
DoCmd.Hourglass False
MsgBox "Your Account has been locked because you made more than 2 faulty attempts, please contact the DB Administrator", vbCritical
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry - Lock Account", , acEdit
DoCmd.SetWarnings True
'Application.Quit
Else
DoCmd.Hourglass False
MsgBox "Incorrect Password, Attempt " & Attempt
Me.txtPassword.SetFocus
Attempt = Attempt + 1
End If
End If

MenuAccess 'Set Up Menus from another module

Exit_OK:
dbs.QueryDefs.Delete ("Logon")
rst.Close
dbs.Close
Exit Sub

Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
If Err.Number = 3012 Then
dbs.QueryDefs.Delete ("Logon")
DoCmd.Hourglass False
MsgBox "Operation Failed, Please Retry"
Exit Sub
End If
MsgBox Err.DESCRIPTION
MsgBox Err.Number
Resume Exit_cmdOK_Click

End Sub


Private Sub Command11_Click()
Topic "Logon.htm" 'Opens .CHM File from module
End Sub

Private Sub Form_Load()
Attempt = 1
End Sub

Private Sub cmdclose_Click()
On Error GoTo Err_cmdclose_Click

If MsgBox("You will be able to use the database but as readonly, Continue?", vbYesNo) = vbNo Then
Exit Sub
Else
DoCmd.Close
CurrentAccess = "Read"
CurrentAccessN = 1
DoCmd.OpenForm "frm - Main"
End If

Exit_cmdclose_Click:
Exit Sub

Err_cmdclose_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdclose_Click

End Sub