Search too SLOW :(

[DMetcalfe92]

Hey Guys,
I built an asset register for the company I work for in MS Access.
Everything worked fine, until we recently migrated to SQL. We use ODBC links to the SQL tables and use the access DB as a front end

The searching of a SQL table is painfully slow. it takes between 5 and 15 seconds to return a record


Code is below:

Private Sub Hardware_Search_AfterUpdate()
DoCmd.OpenForm "frm_Hardware_Database", acNormal

Dim x As Long
x = DCount("*", "Assets_tbl_Asset_Database", "[AssetID] = '" & Me.[Hardware_Search] & "'")
If x > 0 Then
DoCmd.FindRecord (Me.[Hardware_Search]), acAnywhere, , , , acAll
DoCmd.Close acForm, "Search_Asset_ID"
Else
MsgBox "No Record Found"
DoCmd.Close acForm, "Search_Asset_ID"
DoCmd.Close acForm, "frm_Daisy_Hardware_Database"
DoCmd.OpenForm "Switchboard", acNormal
End If

End Sub

I've pinpointed the slowness to DoCmd.FindRecord (Me.[Hardware_Search]), acAnywhere, , , , acAll
I'm not sure why it's so slow to find a record, I just get Calculating . . . in the bottom left hand corner for a long time.


I've come up with a different method which should be instant:

Find the record ID form the table, and then execute the following code.
DoCmd.GoToRecord acDataForm, "frm_Hardware_Database", acGoTo, record_id



My only issue is finding the record ID!!! I've been searching for about an hour on the internet and can't find anything!
Does anyone have a quick fix?
Thanks

[pbaldy]

Instead of opening the form with all records, how about opening it with this technique after testing if a record exists?

BaldyWeb wherecondition