Seek method on Recordsetclone

[sebthirlway]

Hi

I'm trying to implement something I've done many times in Access - but
this time in a .ADP connected to SQL Server.

What I'm trying to do is requery a form and then put the form "on" the
record it was previously on. This should be easy

1. Get the form's current PK value(s)
2. Requery the form
2. Open a RecordsetClone
3. Do a Find or Seek on the recordset
4. If successful (i.e. not .EOF), set form's Bookmark to recordset's
Bookmark

(in fact on this form there's an extra step 0. Rerun sp to re-populate
the table the form's based on - but this makes no difference).

Problem: the table has a 2-column primary key. So I can't use .Find,
which only supports a single-column search. So use .Seek.
Problem: as soon as I set the Index property of the recordset
(pre-requisite to using Seek), I get the error message

3251: Current provider does not support the necessary interface for
Index functionality.

Asking ? objRSClone.Supports(adSeek) or (adIndex) in the Immediate
window both give False.
Looks like ADODB recordsets opened from SQL Server with the
..Recordsetclone method don't support .Index/.Seek.

More info:
- Table recordset is based on has a primary key
- Form's recordsource is [table name], with UniqueTable set to the
same; Recordset Type is Updatable Snapshot.
- Form must be based on a "temporary" (in fact permanent, but
re-populated frequently) table for UI purposes: data is really located
in a "relationship" table where each row means "person X was involved
in the project in period Y" - whereas the form must show all periods,
with (if applicable) details of the person's involvement.

Can anyone think of a way round this? This is not a question of
efficiency, as the table typically only contains 9 rows - I'd happily
use the less efficient .Find method if it supported multiple columns.
So I think my workaround will be just to loop through the cloned
Recordset until I find the right row. Seems a bit stupid when there's
a perfectly good PK on the table.

I've looked into the ResyncCommand property, but I don't think it'll
work, as my ResyncCommand would have to be

EXEC sp_RepopulateTmpTAble (parameters)
SELECT bla FROM tmpTable WHERE Year=? and Quarter=?

In the documentation, the example under ResyncCommand isn't really an
example of code. IMHO MS's documentation reached a peak of usefulness
with VB6/Office 97 and has been going downhill fast ever since. (ever
tried pressing F1 in a code window recently?)

thanks for any suggestions


Seb

 

[Brendan Reynolds]

Have you tried using the Filter property?

http://msdn.microsoft.com/library/en-us/ado270/htm/mdprofilter.asp

 

[sebt]

Good catch!

I applied to Filter to the cloned RS, then set the form's bookmark to
the cloned RS's bookmark.
works a treat.

thanks!

I'll put it at length so that anyone searching will pick up this
thread:
To Seek or Find on multiple columns on a recordset that doesn't support
Seek:
- Clone the recordset
- Do a Filter on the clone
- Set the original recordset's Bookmark to the clone's Bookmark
voila!

Seb

 

[Pat Hartman\(MVP\)]

Why not simply use criteria in a query and only select the rows you want?
That would be more efficient than using seek or find since it limits the
recordset at the server rather than locally.

 

[sebt]

Thanks for the suggestion.

I'm only trying to reposition the form on a certain record within its
recordset (which I want to remain unfiltered - the Filter/Seek/Find on
the clone is just a way to find the bookmark the put should be set to);
and efficiency isn't an issue as the recordset only has 9 rows.
got this working using Filter.

cheers


Seb