Re: Effects on RST in Calling Proc

Tim Marshall <TIMMY!@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
news:dfg6j7$9m6$1@xxxxxxxxxxxxxxxxxx:

> rkc wrote:
>
>>> I don't have one single policy on this dilemma, but decide
>>> case-by-case.
>>
>> The op's question involved a recordset opened as a snapshot and
>> no mention was made of a requery. I can't think of a safer
>> senario in which to use a bookmark.
>
> Thanks to all who replied and I appreciate the discussion on this
> particular sub thread.
>
> Yes, the current record does indeed change. So what I ended up
> doing was something similar to book mark, but I used the
> absoluteposition property.

If the called subroutine requiried the recordset, the number of
records could change, so I wouldn't use absolute position, since
it's almost as volatile as a bookmark (though not quite -- you're
guaranteed that bookmarks are invalidated by a requery,but absolute
position is only invalidated it there's a recorded added/deleted by
another user; that could be avoided by using a snapshot, which I
think you said you were using, so perhaps it's not a problem after
all).

> In the calling procedure, I dimmed a long, lngAbsPos, and did:
>
> lngAbsPos = .AbsolutePosition
>
> And then the called procedure has a long as an argument so
> lngAbsPos can be passed to it. After various machinations and any
> movement in the called procedure, the final step in the called
> procedure is:
>
> rst.AbsolutePosition = lngAbsPos 'as passed from the calling
> function.
>
> This worked fine and since the snapshot recordset is not changed
> in any way in the called procedure, I think this is a safe way to
> do it. The results I'm getting in testing seem to show this as
> so, anyway.

Well, I would say that as long as your code does not change, this
will work. But should you change the recordset type and should the
called subroutine get revised to requery the recordset (that's a
pair of operations that's unlikely to occur separately, but highly
likely to occur together if one of them is called for later on),
then your use of absolute position could break.

Storing the PK will *never* break, except if the record is deleted,
in which case, you'll get back information from your .FindFirst to
tell you that. I assume you'd simply have .NoMatch returning true,
whereas setting absolute position could result in an error by
repositioning you on a deleted record.

Of course, testing the PK value of the current record before a
FindFirst to your stored PK record could produce the same error, so
maybe it's not so much of advantage as all that, since in both cases
you have to deal with the possibility of the current record being
deleted.

Obviously, the snapshot avoids that, but I think it's better to code
the original procedure in a way that is likely to survive the
largest number of revisions without needing to be altered, and to
me, storing the PK value has that advantage over either alternative
that is dependent on the state of the recordset, rather than derived
from the underlying data.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
.

Relevant Pages

  • Re: Kinda OT Re: Combo Box
    ... absolute position value of a recordset or the index of a treeview or ... Can you ping me offline? ...
    (microsoft.public.vb.general.discussion)
  • Move to Absolute Position in DAO
    ... If you know the absolute position of a row in a recordset, say 2, is it ... posibble to move to that position directly? ... I can only find MoveNext, MoveLast, MoveFirst etc. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Effects on RST in Calling Proc
    ... >> Obviously, the snapshot avoids that, but I think it's better to ... the recordset is snapshot and is passed to ... > the called proc so that another field can be searched for a value ... you avoid the issue entirely. ...
    (comp.databases.ms-access)
  • Re: Zugriff auf Access-Datenbank über ODBC
    ... Requery ist genau die Funktion, ... Auszug aus der Doku: ... If the recordset is a snapshot, you must call Requery to reflect edits by ...
    (microsoft.public.de.vc)
  • Re: MFC ODBC Consumer
    ... > I have two inherited MFC Recordset classes that connect to an access db file ... When I perform edit on the second one, the updates are ... A snapshot is as the name implies: it shows the data at the time it was ... A dynaset willl reflect changes made elsewhere (e.g. by others ...
    (microsoft.public.vc.database)