I have inherited a very inefficient database that we are making some changes to. The first changes were to split it, and put the back-end in SQL, which we have done successfully. Now we are having some performance issues because some of our users are at a remote location, and the connection tunnel is limited, so it runs very slowly for them. Though that is the main cause of their performance issues, I am looking to see where I might be able to gain some efficencies and make things a little better for them.
One of their slowest performing objects is a particular form they use to search and edit data. The Record Source of this Form is a query that basically takes a subset of data from the main data table. The query currently returns about 23500 records (of a total of about 44500 records found in the main table).
The slow action they have is a Search button, in which they search for a record based on a value they input. The field that they are searching on is the Primary Key field, so there is really no efficiency to be gained there, since it is already indexed. I was wondering how efficient their method of searching is. Currently, they are clicking on a Command Button which open the Find/Replace dialog box, where they enter the value they are searching for. The code looks like this:
Code:
Private Sub Command50_Click()
On Error GoTo Err_Command50_Click
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_Command50_Click:
Exit Sub
Err_Command50_Click:
MsgBox Err.Description
Resume Exit_Command50_Click
End Sub
In some old databases I created many years ago, I used this type of functionality to search for records, where a user enter the value they want to look for in a Combo Box, and the AfterUpdate events triggers it to jump to that record:
Code:
Private Sub cboClient_AfterUpdate()
' Go to selected record
DoCmd.ShowAllRecords
Me!strClientCode.SetFocus
DoCmd.FindRecord Me!cboClient
' Blank out value in combo box
Me!cboClient.Value = ""
End Sub
So, my question is this. Is the Find/Replace dialog box a fast and efficient method of searching for records, or is there a better, more efficient way of going about it (like the other method I posted)? I would try it myself, but unfortunately (or fortunately) I am not in the environment that is experiencing the slowness. So I would need to coordinate the testing with one of the users out there. I may still do that, but just wanted to get some opinions on what might be some best practices for searching first.
Thanks