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

Chris,
I don't understand why you are requerying the whole form.  That is what is
probably slowing things down.
What I would do is have a combo box with the minimum number of fields for
the user (ie, ID + Text Desc of item to be chosed) 
On the change/exit event (up to you) then this sort of code
Dim rst as dao.recordset, strFind as string
Set rst = me.recordsetclose
Strfind = "ID=" & me!cboName
Rst.findfirst strfind
If not rst.nomatch then
	me.bookmark = rst.bookmark
Else
	msgbox ?"afakjhfh"
Endif
Set rst = nothing

HTH
Max



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Christopher
Hawkins
Sent: Thursday, September 13, 2007 8:26 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Access & SQL 2005 Performance: .Recordset clone search

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-
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com