I have an access data project (Access 2003) connected to SQL Server 2005 Database. Pulling data into an object like a list box using a SQL Query is fast but applying the filter to go to a record is slow!!
Basically I have a main form with a table as its source. I allow for a name search which opens a subform with a list of possible results. Double clicking on the specified row will then apply the filter to the table. The table is somewhat large at 350000 records but I'm still not certain it should take that long to apply a filter.
Here is the code:
Private Sub Results_DblClick(Cancel As Integer)
DoCmd.OpenForm "Main", acNormal, WhereCondition:="MRN = " & Results.Column(3)
Form_Main.Visits.RowSource = "SELECT reg_type, visit, disch, visit_seq# FROM visit WHERE mrn = " & Form_Main.MRN & " ORDER BY visit"
DoCmd.Close acForm, "SearchResults"
If anyone has any suggestions on how to increase the speed on this filter, that would really be great.