How to handle empty dataset as form recordsource

[MostlyFrustrated]

In Access 2010, I'm writing VBA code that assigns an ADO dataset as the recordsource for a form. The query itself varies and is constructed from a search term typed in a textbox by the user. The code below works perfectly until the dataset is empty, when no records satisfied the WHERE criteria. In that case, there is a very long pause (presumably a timeout?) and I eventually get the error message "ODBC call failed." I tried to get around this by testing the number of records in the dataset (see toward the end) before assigning it, but it makes no difference in the behavior, so I'm thinking the actual assignment isn't the issue ... something else is.

Does anyone have any suggestions how I can fix this? If it matters, the "FROM qry_beneficial_owners" in this case is predefined query local to my Access db. That query is based on a linked view from a SQL server.

Public Sub RunSearch(SearchTerm As String)

  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim sql As String
  Dim cols As String

  cols = "[acct], [acctname], [planid]"
  sql = "SELECT " & cols & " FROM qry_beneficial_owners " & _
          "WHERE [Acct] like '" & SearchTerm & "%' " 

  Set cn = CurrentProject.AccessConnection
  Set rs = New ADODB.Recordset
  With rs
        Set .ActiveConnection = cn
        .Source = sql
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .Open
  End With

  If rs.RecordCount > 0 Then
        Set Forms!frmSearch.Recordset = rs
  End If

  Set rs = Nothing
  Set cn = Nothing

End Sub

 

[pbaldy]

Testing the record count can be problematic without a MoveLast. I'd test for EOF instead.