[AccessD] Access & SQL 2005 Performance: .Recordset clone search

Hi Christoffer

Not that I've seen this issue but, first, why the requery?
Next, how about using a specific recordset:

  Dim rst As DAO.Recordset
  Dim bkm As Bookmark

        With Forms("frmBlah")
          ' Skip this?
          ' .Requery

          Set rst = .RecordsetClone
          With rst
            .FindFirst "[RecID] = " & Me![cboFindRec]
            If .NoMatch = False Then
              ' Read Bookmark.
              bkm = .Bookmark
            End If
            .Close
          End With
          Set rst = Nothing

          If Not bmk Is Nothing Then
            ' Apply Bookmark.
            .Bookmark = bmk
          End If
       End With

Finally, with 200 fields you are probably only retrieving those you need?

/gustav

>>> clh at christopherhawkins.com 13-09-2007 09:26 >>>
Hey all.

I've got a real performance issue here.  A client of mine has an Access (front and back end) application.  We have migrated to a SQL 2005 back-end.  Most things work as well as the Access version did, but there is one thing in particular that has become markedly slower - a  jump-to navigation control using the old .RecordsetClone trick.  You know the one:  you lay a combobox on the form and bindit to a list of IDs, then add this code to the OnClick:

        Forms("frmBlah").Requery
        Forms("frmBlah").RecordsetClone.FindFirst "[RecID] = " & Me![cboFindRec]
        Forms("frmBlah").Bookmark = Forms("frmBlah").RecordsetClone.Bookmark

Anyway.  The table that this works against is bad news; it has about 200 fields and 5,000 records.  It's indexed for the most commonly-searched fields, but still.  The jump-to takes about 3 seconds in Access - which, IMO, is too slow to start with - but it now takes a whopping EIGHT SECONDS against SQL 2005.  

I'm not sure why this is.  Out of all the things that have stayed the same performance-wise, why is this the thing that slows down?

Has anyone had any similar experiences with migrating to SQL 2005 (or SQL in general)?

-Christopher-