On change event - Access to SQL (1 Viewer)

[maacky99]

We have moved our database from Access to a SQL back-end still using an Access front-end. For the most part everything has went really well. I have some code that checks to see if a serial number is in the system when it's entered on the New Jobs screen. In our old Access database it searched and reported if it was or not almost instantaneously. Now it takes a realllly long time. Any ideas on why this is or what I could change to improve the performance?

Here is the code:
Private Sub SERIAL__Change()
Dim db As Database
Dim Rst As DAO.Recordset
Set db = CurrentDb()
SNvalue = Me.SERIAL_.Text
Set Rst = db.OpenRecordset("repairs", dbOpenDynaset)
Rst.FindFirst "[SERIAL_no] = '" & SNvalue & "'"
If Rst.NoMatch Then
Else
MsgBox ("Check warranty status !")
End If
End Sub

 

[david.brent]

It sounds like you've linked the SQL tables into your Access database. Try this to see if this gives you a better response time.

Private Sub SERIAL__Change()
Dim db As Database
Dim Rst As DAO.Recordset
Set db = CurrentDb()
SNvalue = Me.SERIAL_.Text
Set Rst = db.OpenRecordset("SELECT [Serial_No] FROM repairs WHERE [Serial_No]='" & SNvalue & "'")

If Rst.RecordCount = 0 Then
Else
MsgBox ("Check warranty status !")
End If

End Sub

Might be quicker.

Take care.

 

[maacky99]

That worked like a charm! Thanks so much for your help.