CODEWITHSUNDEEP
sql-serverms-accessvba
Tiffany V
Tiffany V

Reputation: 57

Most efficient way to find a record in Access linked to SQL Server database

I have a multi user Access UI linked to a SQL Server database that is used to perform loan reviews for a mortgage company. Users are currently using the 'Find Record' function that already exists in Access and it takes a very long time to locate the record. While I do understand why it takes so long and what it theoretically needs to do, I can't figure out the best way to write code to do directly to the record needed. It needs to be a message box similar to the existing find record function, but that performs the most efficient way. I tried the following, which doesn't include a message box, and it doesn't work:

  Private Sub txtFindLoan_AfterUpdate()

  Dim sSQL As String
  sSQL = "SELECT * FROM dbo_PreCloseAuditEnc WHERE LOAN_NUM = '" & 
  Me.txtFindLoan & "'"

  Me.RecordSource = sSQL
  Me.Requery
  End Sub

Thanks in advance!

Upvotes: 0

Views: 285

Answers (2)

Gustav
Gustav

Reputation: 55881

There is no bottleneck, except that you don't need a requery. And, of course, make sure you have an index on the LOAN_NUM field.

You could have an InputBox to pick up the loan number. The code could be called from a button:

Private Sub LoanButton_Click()

    Dim sLoanNo As String
    Dim sSQL As String

    sLoanNo = InputBox("Loan Number:", "Select Loan Number")
    If sLoanNo <> "" Then
        ' Input provided.
        ' Validate input. 
        ' IsValidLoanNumber will be a function to do this. Must return True/False.
        If IsValidLoanNumber(sLoanNo) Then
            sSQL = "SELECT * FROM dbo_PreCloseAuditEnc WHERE LOAN_NUM = '" & sLoanNo & "'"    
            Me.RecordSource = sSQL
        End If
    End If

End Sub

Upvotes: 2

Erik A
Erik A

Reputation: 32672

You're probably off best using ADODB since you're only using external data, and binding the form directly to a filtered ADODB recordset.

As Sean Lange said, you should parameterize your query, and you can do that either using DAO or ADO, but not by modifying the Me.RecordSource property

Private Sub txtFindLoan_AfterUpdate()
    Dim sSQL As String
'I'm assuming your table name in MS SQL is dbo.PreCloseAuditEnc
    sSQL = "SELECT * FROM dbo.PreCloseAuditEnc WHERE LOAN_NUM = ?"
    Dim adoConn As New ADODB.Connection
'Enter a valid OLEDB connection string here:
    adoConn.Open "Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;"
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = adoConn
    cmd.CommandText = sSQL
    cmd.Parameters.Append cmd.CreateParameter(Type:=adChar, Value:=Me.txtFindLoan)
    Me.RecordSet = cmd.Execute
End Sub

Upvotes: 1

Related Questions