Effects on RST in Calling Proc

[Tim Marshall]

Here's the scenario, A2003, Jet back end, illustrated with some cut down
code at the end of the post:

A proc dims a snapshot recordset (dim rst as Dao.recordset) and opens
it. There are several nested do loops, going through the records in rst
using .movenext. At one point in one of the loops, we'll say the rst is
at record "a". Now, another subprocedure is called, passing rst to it.

In the subprocedure, the recordset goes through a do loop and the sub
evaluates each record for various criteria, again, using .movenext. By
the time the subproc is done with the recordset, it is at the last
record. We then go back to the calling proc.

Will the recordset in the calling procedure still be at record "a"? Or
will it be at the end?

I've assumed the last is true and for now, in the main procedure, I
figure dimming a second recordset and setting it to the same as the
first. I thought using the second set would bypass any problems running
through the recordset in the second procedure.

Thanks for any comments on this.

Sub sMainProc()

dim rst as dao.recordset
dim dbs as dao.database

set dbs = access.currentdb
set rst = dbs.openrecordset(<sql statement>, dbopensnapshot)

with rst

if .eof = false then

.movefirst

do while .eof = false

<evaluate stuff, other loops, etc>

'At this point, rst is at record "a"

sMainProcOtherEval(rst)

'Will rst still be at "a"?

.movenext

loop

end if

.close

end with

end sub

sub sMainProcOtherEval(r as dao.recordset)

with r 'start at beginning of passed rst

.movefirst

do while .eof = false

<evaluate stuff, other loops, etc>

.movenext

loop

.close

end with

end sub
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[David Lloyd]

Tim:

I believe your assumption about the recordset being at the last record is
correct. You are passing the recordset by reference, the default, therefore
changes made to it in the sub procedure will affect the recordset in the
calling procedure.

The ByVal keyword would normally be used to avoid this, however, when using
objects as parameters, the ByVal keyword does not have the same effect.
Below is a KB article that talks about the use of the ByVal keyword when
passing objects.

http://support.microsoft.com/default...b;en-us;138517

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:df**********@coranto.ucs.mun.ca...
Here's the scenario, A2003, Jet back end, illustrated with some cut down
code at the end of the post:

A proc dims a snapshot recordset (dim rst as Dao.recordset) and opens
it. There are several nested do loops, going through the records in rst
using .movenext. At one point in one of the loops, we'll say the rst is
at record "a". Now, another subprocedure is called, passing rst to it.

In the subprocedure, the recordset goes through a do loop and the sub
evaluates each record for various criteria, again, using .movenext. By
the time the subproc is done with the recordset, it is at the last
record. We then go back to the calling proc.

Will the recordset in the calling procedure still be at record "a"? Or
will it be at the end?

I've assumed the last is true and for now, in the main procedure, I
figure dimming a second recordset and setting it to the same as the
first. I thought using the second set would bypass any problems running
through the recordset in the second procedure.

Thanks for any comments on this.

Sub sMainProc()

dim rst as dao.recordset
dim dbs as dao.database

set dbs = access.currentdb
set rst = dbs.openrecordset(<sql statement>, dbopensnapshot)

with rst

if .eof = false then

.movefirst

do while .eof = false

<evaluate stuff, other loops, etc>

'At this point, rst is at record "a"

sMainProcOtherEval(rst)

'Will rst still be at "a"?

.movenext

loop

end if

.close

end with

end sub

sub sMainProcOtherEval(r as dao.recordset)

with r 'start at beginning of passed rst

.movefirst

do while .eof = false

<evaluate stuff, other loops, etc>

.movenext

loop

.close

end with

end sub
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[rkc]

Tim Marshall wrote:

Here's the scenario, A2003, Jet back end, illustrated with some cut down
code at the end of the post:

A proc dims a snapshot recordset (dim rst as Dao.recordset) and opens
it. There are several nested do loops, going through the records in rst
using .movenext. At one point in one of the loops, we'll say the rst is
at record "a". Now, another subprocedure is called, passing rst to it.

In the subprocedure, the recordset goes through a do loop and the sub
evaluates each record for various criteria, again, using .movenext. By
the time the subproc is done with the recordset, it is at the last
record. We then go back to the calling proc.

Will the recordset in the calling procedure still be at record "a"? Or
will it be at the end?

I've assumed the last is true and for now, in the main procedure, I
figure dimming a second recordset and setting it to the same as the
first. I thought using the second set would bypass any problems running
through the recordset in the second procedure.

Thanks for any comments on this.

Whether or not the effects of move operations on the recordset in the
procedure it's passed to are reflected in the calling procedure on
return is easily tested so I assume your question is does it make sense
to open two recordsets. I'd say no. You can always save your position
before passing the recordset and return to it after the call. I believe
that is a legitimate/safe use of a Bookmark.

[David W. Fenton]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:f5*******************@twister.nyroc.rr.com:

Whether or not the effects of move operations on the recordset in
the procedure it's passed to are reflected in the calling
procedure on return is easily tested so I assume your question is
does it make sense to open two recordsets. I'd say no. You can
always save your position before passing the recordset and return
to it after the call. I believe that is a legitimate/safe use of a
Bookmark.

I wouldn't.

I'd save the PK of the record that's current when you pass the
recordset to called procedure, then when control returns do a
FindFirst on the PK.

My rule: Never depend on bookmarks for anything where you can use
actual data to do so. Use bookmarks only for circumstances where you
*can't* use data.

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

[rkc]

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:f5*******************@twister.nyroc.rr.com:

Whether or not the effects of move operations on the recordset in
the procedure it's passed to are reflected in the calling
procedure on return is easily tested so I assume your question is
does it make sense to open two recordsets. I'd say no. You can
always save your position before passing the recordset and return
to it after the call. I believe that is a legitimate/safe use of a
Bookmark.

I wouldn't.

I'd save the PK of the record that's current when you pass the
recordset to called procedure, then when control returns do a
FindFirst on the PK.

My rule: Never depend on bookmarks for anything where you can use
actual data to do so. Use bookmarks only for circumstances where you
*can't* use data.

What could possibly cause just the bookmark property to fail in a
recordset opened as a snapshot?

[David W. Fenton]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:Kc******************@twister.nyroc.rr.com:

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:f5*******************@twister.nyroc.rr.com:

Whether or not the effects of move operations on the recordset in
the procedure it's passed to are reflected in the calling
procedure on return is easily tested so I assume your question is
does it make sense to open two recordsets. I'd say no. You can
always save your position before passing the recordset and return
to it after the call. I believe that is a legitimate/safe use of
a Bookmark.

I wouldn't.

I'd save the PK of the record that's current when you pass the
recordset to called procedure, then when control returns do a
FindFirst on the PK.

My rule: Never depend on bookmarks for anything where you can use
actual data to do so. Use bookmarks only for circumstances where
you *can't* use data.

What could possibly cause just the bookmark property to fail in a
recordset opened as a snapshot?

You're asking the wrong question. I don't know that there are any
such conditions that lead to failure, but I don't know that there
are not. What I do know is that navigating by PK will *never* fail
if the recordset itself remains valid.

Why use something that is a black box about which I know very little
when there's a guaranteed method that doesn't have any blackbox
aspects to it?

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

[rkc]

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:Kc******************@twister.nyroc.rr.com:

David W. Fenton wrote:

My rule: Never depend on bookmarks for anything where you can use
actual data to do so. Use bookmarks only for circumstances where
you *can't* use data.

What could possibly cause just the bookmark property to fail in a
recordset opened as a snapshot?

You're asking the wrong question. I don't know that there are any
such conditions that lead to failure, but I don't know that there
are not. What I do know is that navigating by PK will *never* fail
if the recordset itself remains valid.

Why use something that is a black box about which I know very little
when there's a guaranteed method that doesn't have any blackbox
aspects to it?

I don't see why you view the FindFirst method as any less of a
blackbox. The bookmark property is available for the exact purpose
of flagging and returning to a specific record in a recordset.
Saying that you can do the same in a more round about way doesn't
make it mysterious or unreliable.

[David W. Fenton]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:zC*****************@twister.nyroc.rr.com:

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:Kc******************@twister.nyroc.rr.com:

David W. Fenton wrote:My rule: Never depend on bookmarks for anything where you can
use actual data to do so. Use bookmarks only for circumstances
where you *can't* use data.

What could possibly cause just the bookmark property to fail in a
recordset opened as a snapshot?

You're asking the wrong question. I don't know that there are any
such conditions that lead to failure, but I don't know that there
are not. What I do know is that navigating by PK will *never*
fail if the recordset itself remains valid.

Why use something that is a black box about which I know very
little when there's a guaranteed method that doesn't have any
blackbox aspects to it?

I don't see why you view the FindFirst method as any less of a
blackbox. The bookmark property is available for the exact purpose
of flagging and returning to a specific record in a recordset.
Saying that you can do the same in a more round about way doesn't
make it mysterious or unreliable.

But we know that a bookmark is volatile data, while the primary key
is *not* volatile.

FindFirst is a method, whereas a bookmark is a form of data. I'm
advocating the use of reliable data for your navigation, and the
volatility of a bookmark makes me wary of using it except when it
can't possibly have been invalidated (i.e., immediately after a
Findfirst).

I am suspicious of any code that stores a bookmark value, as there
should always be a better method of getting back to the same record
using real data, rather than metadata about the recordset.

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

[Steve Jorgensen]

On Sat, 3 Sep 2005 17:43:54 -0400, "David Lloyd" <Da***@NoSpamPlease.com>
wrote:

Tim:

I believe your assumption about the recordset being at the last record is
correct. You are passing the recordset by reference, the default, therefore
changes made to it in the sub procedure will affect the recordset in the
calling procedure.

The ByVal keyword would normally be used to avoid this, however, when using
objects as parameters, the ByVal keyword does not have the same effect.
Below is a KB article that talks about the use of the ByVal keyword when
passing objects.
That's not quite right. When you pass an object instance to a procedure, the
called procedure is always referring to the same object, regardless of whether
it was passed by value or by reference.

ByRef or ByVal only affects what happens if you replace the contents of the
variable itself from within the called procedure. In the case of recordset,
that means if the called procedure says Set rst = <something>, does in only
place the new recordset object in the local variable, or does it replace the
calling procedure's variable that it passed to the procedure as well.

http://support.microsoft.com/default...b;en-us;138517

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:df**********@coranto.ucs.mun.ca...
Here's the scenario, A2003, Jet back end, illustrated with some cut down
code at the end of the post:

A proc dims a snapshot recordset (dim rst as Dao.recordset) and opens
it. There are several nested do loops, going through the records in rst
using .movenext. At one point in one of the loops, we'll say the rst is
at record "a". Now, another subprocedure is called, passing rst to it.

In the subprocedure, the recordset goes through a do loop and the sub
evaluates each record for various criteria, again, using .movenext. By
the time the subproc is done with the recordset, it is at the last
record. We then go back to the calling proc.

Will the recordset in the calling procedure still be at record "a"? Or
will it be at the end?

I've assumed the last is true and for now, in the main procedure, I
figure dimming a second recordset and setting it to the same as the
first. I thought using the second set would bypass any problems running
through the recordset in the second procedure.

Thanks for any comments on this.

Sub sMainProc()

dim rst as dao.recordset
dim dbs as dao.database

set dbs = access.currentdb
set rst = dbs.openrecordset(<sql statement>, dbopensnapshot)

with rst

if .eof = false then

.movefirst

do while .eof = false

<evaluate stuff, other loops, etc>

'At this point, rst is at record "a"

sMainProcOtherEval(rst)

'Will rst still be at "a"?

.movenext

loop

end if

.close

end with

end sub

sub sMainProcOtherEval(r as dao.recordset)

with r 'start at beginning of passed rst

.movefirst

do while .eof = false

<evaluate stuff, other loops, etc>

.movenext

loop

.close

end with

end sub

[Steve Jorgensen]

On Sun, 04 Sep 2005 13:49:10 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:zC*****************@twister.nyroc.rr.com:

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:Kc******************@twister.nyroc.rr.com:
David W. Fenton wrote:

>My rule: Never depend on bookmarks for anything where you can
>use actual data to do so. Use bookmarks only for circumstances
>where you *can't* use data.

What could possibly cause just the bookmark property to fail in a
recordset opened as a snapshot?
You're asking the wrong question. I don't know that there are any
such conditions that lead to failure, but I don't know that there
are not. What I do know is that navigating by PK will *never*
fail if the recordset itself remains valid.

Why use something that is a black box about which I know very
little when there's a guaranteed method that doesn't have any
blackbox aspects to it?

I don't see why you view the FindFirst method as any less of a
blackbox. The bookmark property is available for the exact purpose
of flagging and returning to a specific record in a recordset.
Saying that you can do the same in a more round about way doesn't
make it mysterious or unreliable.

But we know that a bookmark is volatile data, while the primary key
is *not* volatile.

FindFirst is a method, whereas a bookmark is a form of data. I'm
advocating the use of reliable data for your navigation, and the
volatility of a bookmark makes me wary of using it except when it
can't possibly have been invalidated (i.e., immediately after a
Findfirst).

I am suspicious of any code that stores a bookmark value, as there
should always be a better method of getting back to the same record
using real data, rather than metadata about the recordset.

A concrete reason to use FindFirst rather than a bookmark would be that a
bookmark depends on the fact that you are using the same recordset both times,
and it has not been requeried. What happens later if a need has arisen to
requery the recordset between the time the position has been recorded and when
you need to return to it?

That said, bookmarks have the advantage of not depending on a particular
definition of a row identifier for a given recordset, and doesn't require
searching on a combination of values when the row identifier for a recordset
cannot be a single value.

I don't have one single policy on this dilemma, but decide case-by-case.

[Steve Jorgensen]

On Sat, 03 Sep 2005 22:03:55 GMT, rkc <rk*@rochester.yabba.dabba.do.rr.bomb>
wrote:

Tim Marshall wrote:

Here's the scenario, A2003, Jet back end, illustrated with some cut down
code at the end of the post:

A proc dims a snapshot recordset (dim rst as Dao.recordset) and opens
it. There are several nested do loops, going through the records in rst
using .movenext. At one point in one of the loops, we'll say the rst is
at record "a". Now, another subprocedure is called, passing rst to it.

In the subprocedure, the recordset goes through a do loop and the sub
evaluates each record for various criteria, again, using .movenext. By
the time the subproc is done with the recordset, it is at the last
record. We then go back to the calling proc.

Will the recordset in the calling procedure still be at record "a"? Or
will it be at the end?

I've assumed the last is true and for now, in the main procedure, I
figure dimming a second recordset and setting it to the same as the
first. I thought using the second set would bypass any problems running
through the recordset in the second procedure.

Thanks for any comments on this.

Whether or not the effects of move operations on the recordset in the
procedure it's passed to are reflected in the calling procedure on
return is easily tested so I assume your question is does it make sense
to open two recordsets. I'd say no. You can always save your position
before passing the recordset and return to it after the call. I believe
that is a legitimate/safe use of a Bookmark.

I find it cleaner to create and pass a recordset clone than to store a
position and return to it. Instead of trying to return to a known state after
letting something mess with the state, pass it something that won't be used to
mess with the known state.

[rkc]

Steve Jorgensen wrote:

On Sun, 04 Sep 2005 13:49:10 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:zC*****************@twister.nyroc.rr.com:

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:Kc******************@twister.nyroc.rr.com :

>David W. Fenton wrote:

>>My rule: Never depend on bookmarks for anything where you can
>>use actual data to do so. Use bookmarks only for circumstances
>>where you *can't* use data.
>
>What could possibly cause just the bookmark property to fail in a
>recordset opened as a snapshot?
You're asking the wrong question. I don't know that there are any
such conditions that lead to failure, but I don't know that there
are not. What I do know is that navigating by PK will *never*
fail if the recordset itself remains valid.

Why use something that is a black box about which I know very
little when there's a guaranteed method that doesn't have any
blackbox aspects to it?

I don't see why you view the FindFirst method as any less of a
blackbox. The bookmark property is available for the exact purpose
of flagging and returning to a specific record in a recordset.
Saying that you can do the same in a more round about way doesn't
make it mysterious or unreliable.

But we know that a bookmark is volatile data, while the primary key
is *not* volatile.

FindFirst is a method, whereas a bookmark is a form of data. I'm
advocating the use of reliable data for your navigation, and the
volatility of a bookmark makes me wary of using it except when it
can't possibly have been invalidated (i.e., immediately after a
Findfirst).

I am suspicious of any code that stores a bookmark value, as there
should always be a better method of getting back to the same record
using real data, rather than metadata about the recordset.

A concrete reason to use FindFirst rather than a bookmark would be that a
bookmark depends on the fact that you are using the same recordset both times,
and it has not been requeried. What happens later if a need has arisen to
requery the recordset between the time the position has been recorded and when
you need to return to it?

That said, bookmarks have the advantage of not depending on a particular
definition of a row identifier for a given recordset, and doesn't require
searching on a combination of values when the row identifier for a recordset
cannot be a single value.

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.

[rkc]

Steve Jorgensen wrote:

On Sat, 03 Sep 2005 22:03:55 GMT, rkc <rk*@rochester.yabba.dabba.do.rr.bomb>
wrote: <snip>
I find it cleaner to create and pass a recordset clone than to store a
position and return to it. Instead of trying to return to a known state after
letting something mess with the state, pass it something that won't be used to
mess with the known state.

Can't argue with that.

[Tim Marshall]

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.

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.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Tim Marshall]

Steve Jorgensen wrote:

I find it cleaner to create and pass a recordset clone than to store a
position and return to it. Instead of trying to return to a known state after
letting something mess with the state, pass it something that won't be used to
mess with the known state.

Thanks, Steve, I think you're right. I will see how this is in terms of
speed compared to the method I described elesewhere using the
absoluteposition property.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Steve Jorgensen]

On Mon, 05 Sep 2005 00:09:12 -0230, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote:

Steve Jorgensen wrote:

I find it cleaner to create and pass a recordset clone than to store a
position and return to it. Instead of trying to return to a known state after
letting something mess with the state, pass it something that won't be used to
mess with the known state.

Thanks, Steve, I think you're right. I will see how this is in terms of
speed compared to the method I described elesewhere using the
absoluteposition property.

Hypothetically, the speed difference should be negligible to immeasurable. I
presume that's what you'll find.

[rkc]

Steve Jorgensen wrote:

On Sat, 3 Sep 2005 17:43:54 -0400, "David Lloyd" <Da***@NoSpamPlease.com>
wrote:

Tim:

I believe your assumption about the recordset being at the last record is
correct. You are passing the recordset by reference, the default, therefore
changes made to it in the sub procedure will affect the recordset in the
calling procedure.

The ByVal keyword would normally be used to avoid this, however, when using
objects as parameters, the ByVal keyword does not have the same effect.
Below is a KB article that talks about the use of the ByVal keyword when
passing objects.

That's not quite right. When you pass an object instance to a procedure, the
called procedure is always referring to the same object, regardless of whether
it was passed by value or by reference.

ByRef or ByVal only affects what happens if you replace the contents of the
variable itself from within the called procedure. In the case of recordset,
that means if the called procedure says Set rst = <something>, does in only
place the new recordset object in the local variable, or does it replace the
calling procedure's variable that it passed to the procedure as well.

If that's really a question, passing ByRef would replace the object
variable passed in. If the second object was set to nothing at the
end of the called procedure it would leave the passed in variable
pointing to nothing.

[Steve Jorgensen]

On Mon, 05 Sep 2005 04:17:26 GMT, rkc <rk*@rochester.yabba.dabba.do.rr.bomb>
wrote:

Steve Jorgensen wrote:

On Sat, 3 Sep 2005 17:43:54 -0400, "David Lloyd" <Da***@NoSpamPlease.com>
wrote:

Tim:

I believe your assumption about the recordset being at the last record is
correct. You are passing the recordset by reference, the default, therefore
changes made to it in the sub procedure will affect the recordset in the
calling procedure.

The ByVal keyword would normally be used to avoid this, however, when using
objects as parameters, the ByVal keyword does not have the same effect.
Below is a KB article that talks about the use of the ByVal keyword when
passing objects.

That's not quite right. When you pass an object instance to a procedure, the
called procedure is always referring to the same object, regardless of whether
it was passed by value or by reference.

ByRef or ByVal only affects what happens if you replace the contents of the
variable itself from within the called procedure. In the case of recordset,
that means if the called procedure says Set rst = <something>, does in only
place the new recordset object in the local variable, or does it replace the
calling procedure's variable that it passed to the procedure as well.

If that's really a question, passing ByRef would replace the object
variable passed in. If the second object was set to nothing at the
end of the called procedure it would leave the passed in variable
pointing to nothing.

My wording might have been unclear, but that's exactly what I was trying to
say.

[David Lloyd]

Steve:

You must not have understood what I was saying. When I said "when using
objects as parameters, the ByVal keyword does not have the same effect" I
was stating in effect that the object is not passed as a value, but rather
as a reference. The KB article cited clearly demonstrates this, so there
was no reason to give an exhaustive explanation.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:vl********************************@4ax.com...
On Sat, 3 Sep 2005 17:43:54 -0400, "David Lloyd" <Da***@NoSpamPlease.com>
wrote:

Tim:

I believe your assumption about the recordset being at the last record is
correct. You are passing the recordset by reference, the default,
therefore
changes made to it in the sub procedure will affect the recordset in the
calling procedure.

The ByVal keyword would normally be used to avoid this, however, when using
objects as parameters, the ByVal keyword does not have the same effect.
Below is a KB article that talks about the use of the ByVal keyword when
passing objects.
That's not quite right. When you pass an object instance to a procedure,
the
called procedure is always referring to the same object, regardless of
whether
it was passed by value or by reference.

ByRef or ByVal only affects what happens if you replace the contents of the
variable itself from within the called procedure. In the case of recordset,
that means if the called procedure says Set rst = <something>, does in only
place the new recordset object in the local variable, or does it replace the
calling procedure's variable that it passed to the procedure as well.

http://support.microsoft.com/default...b;en-us;138517

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or
warranties.
"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:df**********@coranto.ucs.mun.ca...
Here's the scenario, A2003, Jet back end, illustrated with some cut down
code at the end of the post:

A proc dims a snapshot recordset (dim rst as Dao.recordset) and opens
it. There are several nested do loops, going through the records in rst
using .movenext. At one point in one of the loops, we'll say the rst is
at record "a". Now, another subprocedure is called, passing rst to it.

In the subprocedure, the recordset goes through a do loop and the sub
evaluates each record for various criteria, again, using .movenext. By
the time the subproc is done with the recordset, it is at the last
record. We then go back to the calling proc.

Will the recordset in the calling procedure still be at record "a"? Or
will it be at the end?

I've assumed the last is true and for now, in the main procedure, I
figure dimming a second recordset and setting it to the same as the
first. I thought using the second set would bypass any problems running
through the recordset in the second procedure.

Thanks for any comments on this.

Sub sMainProc()

dim rst as dao.recordset
dim dbs as dao.database

set dbs = access.currentdb
set rst = dbs.openrecordset(<sql statement>, dbopensnapshot)

with rst

if .eof = false then

.movefirst

do while .eof = false

<evaluate stuff, other loops, etc>

'At this point, rst is at record "a"

sMainProcOtherEval(rst)

'Will rst still be at "a"?

.movenext

loop

end if

.close

end with

end sub

sub sMainProcOtherEval(r as dao.recordset)

with r 'start at beginning of passed rst

.movefirst

do while .eof = false

<evaluate stuff, other loops, etc>

.movenext

loop

.close

end with

end sub

[Steve Jorgensen]

On Mon, 5 Sep 2005 07:35:29 -0400, "David Lloyd" <Da***@NoSpamPlease.com>
wrote:

Steve:

You must not have understood what I was saying. When I said "when using
objects as parameters, the ByVal keyword does not have the same effect" I
was stating in effect that the object is not passed as a value, but rather
as a reference. The KB article cited clearly demonstrates this, so there
was no reason to give an exhaustive explanation.

I re-read your post, and I see that is what you were saying. Sorry for the
misunderstanding.

[David W. Fenton]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:9i*******************@twister.nyroc.rr.com:

Steve Jorgensen wrote:

On Sun, 04 Sep 2005 13:49:10 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:zC*****************@twister.nyroc.rr.com :
David W. Fenton wrote:

>rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
>news:Kc******************@twister.nyroc.rr.co m:
>
>
>
>>David W. Fenton wrote:

>>>My rule: Never depend on bookmarks for anything where you can
>>>use actual data to do so. Use bookmarks only for
>>>circumstances where you *can't* use data.
>>
>>What could possibly cause just the bookmark property to fail
>>in a recordset opened as a snapshot?
>
>
>You're asking the wrong question. I don't know that there are
>any such conditions that lead to failure, but I don't know that
>there are not. What I do know is that navigating by PK will
>*never* fail if the recordset itself remains valid.
>
>Why use something that is a black box about which I know very
>little when there's a guaranteed method that doesn't have any
>blackbox aspects to it?

I don't see why you view the FindFirst method as any less of a
blackbox. The bookmark property is available for the exact
purpose of flagging and returning to a specific record in a
recordset. Saying that you can do the same in a more round about
way doesn't make it mysterious or unreliable.

But we know that a bookmark is volatile data, while the primary
key is *not* volatile.

FindFirst is a method, whereas a bookmark is a form of data. I'm
advocating the use of reliable data for your navigation, and the
volatility of a bookmark makes me wary of using it except when it
can't possibly have been invalidated (i.e., immediately after a
Findfirst).

I am suspicious of any code that stores a bookmark value, as
there should always be a better method of getting back to the
same record using real data, rather than metadata about the
recordset.

A concrete reason to use FindFirst rather than a bookmark would
be that a bookmark depends on the fact that you are using the
same recordset both times, and it has not been requeried. What
happens later if a need has arisen to requery the recordset
between the time the position has been recorded and when you need
to return to it?

That said, bookmarks have the advantage of not depending on a
particular definition of a row identifier for a given recordset,
and doesn't require searching on a combination of values when the
row identifier for a recordset cannot be a single value.

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.

But there's a great *danger* of invalidating the bookmark because
the recordset was passed to a subroutine. It would be *very* easy to
revise that other subroutine down the road without realizing that
you might be invalidating a bookmark in the calling routine. Yes, it
would require sloppy coding, but my point is that if you code to
avoid using a bookmark when you have another method for navigation,
then you don't have to worry about whether or not you or another
coder is sloppy or not in revising the subroutine.

It just seems to me like a complete no-brainer to neer use a
volatile piece of data except where it's the only option. PK
navigation is never going to be volatile, and that makes it vastly
superior to bookmark navigation, in my opinion.

Personally, I don't think I've ever used any bookmark except in the
line immediately folloing a .NoMatch test after a .FindFirst. In
that case, the bookmark can't be invalid yet, and it's the only way
to get the location pointer, so there is no alternative.

When passing a recordset to a subroutine, if you're going to store
something, it seems logical to store the least volatile data, and
that would be the PK value, then use a .FindFirst to reposition the
record pointer. And, of course, you could test the PK value of the
current record and might not have to reposition at all.

The only circumstance where this would require more work would be if
there is no PK in the recordset, in which case I question whether
the code is properly designed in the first place. But that isn't
insurmountable, either, assuming you've got, at the very least,
unique records in your recordset -- it's easy enough to store in a
string the criteria for your .FindFirst to reposition the current
record pointer, if needed.

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

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:df**********@coranto.ucs.mun.ca:

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

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:me********************************@4ax.com:

I find it cleaner to create and pass a recordset clone than to
store a position and return to it. Instead of trying to return to
a known state after letting something mess with the state, pass it
something that won't be used to mess with the known state.

Well, that's only going to work if you're not changing the data in
the original recordset. Well, yes, you could do that in a clone,
too, but then you'd possibly have to refresh or requery the original
recordset.

Also, cloning the recordset could cause another hit on the back end
(I'm not sure about that), so it could be simpler to use the
original recordset.

In any event, if you're using a snapshot in the first place, I see
no need to clone it to pass it to another subroutine.

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

[Steve Jorgensen]

On Tue, 06 Sep 2005 17:51:13 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:me********************************@4ax.com :

I find it cleaner to create and pass a recordset clone than to
store a position and return to it. Instead of trying to return to
a known state after letting something mess with the state, pass it
something that won't be used to mess with the known state.

Well, that's only going to work if you're not changing the data in
the original recordset. Well, yes, you could do that in a clone,
too, but then you'd possibly have to refresh or requery the original
recordset.

Also, cloning the recordset could cause another hit on the back end
(I'm not sure about that), so it could be simpler to use the
original recordset.

In any event, if you're using a snapshot in the first place, I see
no need to clone it to pass it to another subroutine.

The reason is that each clone has its own current record pointer. It's
actually the same thing as returning to the record with a bookmark (with all
the same potential problems you've pointed out) except that you don't actually
have to re-set the bookmark because the pointer the called procedure can
change is not the one the original recordset has.

Cloning does not requery or refresh records, so there's no database hit. It's
basically just a new, separate context for filtering and navigation.

[Tim Marshall]

David W. Fenton wrote:

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.

Thanks for the feedback.

In the original proc, the recordset is snapshot and is passed to the
called proc so that another field can be searched for a value that is
being tested in the calling proc. So the nature of the two together is
such that the recordset will not be changed.

I think you're right, the PK find first alternative is probably better,
though. But as I said, the nature of my procedures in this case are
such that changes made to the recordset in the second procedure would
not be appropriate. I suppose the two could have been melded into one
procedure, but I thought the second could lend itself later in
development to other procs similar to the one here that calls it.

I try as much as possible to avoid data manipulation by row processing
and prefer set processing, ie, I prefer to execute an update or insert
statement as opposed to going through each row in a recordset and doing
edit/update, so I generally stick with dbopensnapshot.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:df**********@coranto.ucs.mun.ca:

David W. Fenton wrote:

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.
Thanks for the feedback.

In the original proc, the recordset is snapshot and is passed to
the called proc so that another field can be searched for a value
that is being tested in the calling proc. So the nature of the
two together is such that the recordset will not be changed.

If your code or the context in which it is used never changes, then
it's not an issue. I prefer writing code that will survive
foreseeable changes, even ones that are mistaken.
I think you're right, the PK find first alternative is probably
better, though. But as I said, the nature of my procedures in
this case are such that changes made to the recordset in the
second procedure would not be appropriate. I suppose the two
could have been melded into one procedure, but I thought the
second could lend itself later in development to other procs
similar to the one here that calls it.
My point is not about the code in its current state, but about what
might happen in the future when you or another coder has forgotten
the original imitations on what you can do to the code. By using PK
navigation, you avoid the issue entirely.
I try as much as possible to avoid data manipulation by row
processing and prefer set processing, ie, I prefer to execute an
update or insert statement as opposed to going through each row in
a recordset and doing edit/update, so I generally stick with
dbopensnapshot.

But there are operations that need to be performed that *can't* be
done with set processing, so one sometimes does need to edit an
actual recordset.

I have never used snapshots because my impression was that they were
much sloer than dynasets. I do know that at one point I got religion
and tried using them all over the place and the app slowed to a
crawl, so I switched back to the default recordset type, dynasets.

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

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:gs********************************@4ax.com:

Cloning does not requery or refresh records, so there's no
database hit. It's basically just a new, separate context for
filtering and navigation.

OK. Sounds like a good alternative.

However, it's only a *navigation* alternative, since edits to a
clone will appear in the clone's parent, as well, right? In that
case, I see no real difference, except for the independent current
record pointer, when using a snapshot.

In reality, this is not the kind of scenario I've run onto much at
all, so I'd never really done anything with recordset clones, other
than the one for forms, which I believe is a special one that has
special features not present in regular recordset clones. But that
could be voodoo suspicions on my part more than it is a
reality-based point of view!

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

[Tim Marshall]

David W. Fenton wrote:

I have never used snapshots because my impression was that they were
much sloer than dynasets. I do know that at one point I got religion
and tried using them all over the place and the app slowed to a
crawl, so I switched back to the default recordset type, dynasets.

Good heavens, I thought they were supposed to be faster!!!!?????

Here's what A97 help file has to say (god help anyone trying to find
anything in A2003 and other help files...). I thyink this is what got
me going using them:

"Snapshot-type Recordset objects are generally faster to create and
access than dynaset-type Recordset objects because their records are
either in memory or stored in TEMP disk space, and the Microsoft Jet
database engine doesn't need to lock pages or handle multiuser issues.
However, snapshot-type Recordset objects use more resources than
dynaset-type Recordset objects because the entire record is downloaded
to local memory."

Interesting. I guess this means if you have the RAM, go for it?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Steve Jorgensen]

On Wed, 07 Sep 2005 18:58:17 -0230, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote:

David W. Fenton wrote:

I have never used snapshots because my impression was that they were
much sloer than dynasets. I do know that at one point I got religion
and tried using them all over the place and the app slowed to a
crawl, so I switched back to the default recordset type, dynasets.

Good heavens, I thought they were supposed to be faster!!!!?????

Here's what A97 help file has to say (god help anyone trying to find
anything in A2003 and other help files...). I thyink this is what got
me going using them:

"Snapshot-type Recordset objects are generally faster to create and
access than dynaset-type Recordset objects because their records are
either in memory or stored in TEMP disk space, and the Microsoft Jet
database engine doesn't need to lock pages or handle multiuser issues.
However, snapshot-type Recordset objects use more resources than
dynaset-type Recordset objects because the entire record is downloaded
to local memory."

Interesting. I guess this means if you have the RAM, go for it?

Whether a snapshot or a dynaset is faster depends on many things. It would be
vastly ofersimplifying to say that one is always faster than the other. Also,
forward-only snapshots are generally faster than non-forward-only snapshots.

[Steve Jorgensen]

On Wed, 07 Sep 2005 15:05:04 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:gs********************************@4ax.com :

Cloning does not requery or refresh records, so there's no
database hit. It's basically just a new, separate context for
filtering and navigation.

OK. Sounds like a good alternative.

However, it's only a *navigation* alternative, since edits to a
clone will appear in the clone's parent, as well, right? In that
case, I see no real difference, except for the independent current
record pointer, when using a snapshot.

In reality, this is not the kind of scenario I've run onto much at
all, so I'd never really done anything with recordset clones, other
than the one for forms, which I believe is a special one that has
special features not present in regular recordset clones. But that
could be voodoo suspicions on my part more than it is a
reality-based point of view!

From what I can tell, it is true that form recordset clones are subtlely
different than other recordset clones, though the difference only matters for
certain weird operations that are only ever needed in client/server apps.

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:df**********@coranto.ucs.mun.ca:

David W. Fenton wrote:

I have never used snapshots because my impression was that they
were much sloer than dynasets. I do know that at one point I got
religion and tried using them all over the place and the app
slowed to a crawl, so I switched back to the default recordset
type, dynasets.

Good heavens, I thought they were supposed to be faster!!!!?????

Here's what A97 help file has to say (god help anyone trying to
find anything in A2003 and other help files...). I thyink this is
what got me going using them:

"Snapshot-type Recordset objects are generally faster to create
and access than dynaset-type Recordset objects because their
records are either in memory or stored in TEMP disk space, and the
Microsoft Jet database engine doesn't need to lock pages or handle
multiuser issues. However, snapshot-type Recordset objects use
more resources than dynaset-type Recordset objects because the
entire record is downloaded to local memory."

Interesting. I guess this means if you have the RAM, go for it?

My experience was that they took longer to load, as Rushmore didn't
seem to be available. I believe MichKa told us someth9ing about
that, but can't find anything about it (it's too nebulous for me to
find in Google Groups).

But I could be wrong, and the issue could have changed over the
years with new versions of Access.

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