Closing ADO Recordset created by Execute

[Darryl Kerkeslager]

When I open an ADO Recordset, I close it. However, it seems that there may
be some difference in this manner of opening a Recordset:

Dim rL As ADODB.Recordset
Set rL = New ADODB.Recordset

src = "SELECT Count(*) FROM reviewer INNER JOIN pp_officer " & _
"ON reviewer.reviewer_id = pp_officer.ppo_rev_id " & _
"WHERE rev_login = 'EllisonL'"
Set rL = CurrentProject.Connection.Execute(src, , adCmdText)
c = rL.Fields(0)
rL.Close

In this type of Recordset, is the Close statement still necessary - there
is, after all, no explicit Open?

--
Darryl Kerkeslager

[lylefair]

No.

[Randy Harris]

"Darryl Kerkeslager" <ke*********@comcast.net> wrote in message
news:9Z******************************@comcast.com. ..

When I open an ADO Recordset, I close it. However, it seems that there may be some difference in this manner of opening a Recordset:

Dim rL As ADODB.Recordset
Set rL = New ADODB.Recordset

src = "SELECT Count(*) FROM reviewer INNER JOIN pp_officer " & _
"ON reviewer.reviewer_id = pp_officer.ppo_rev_id " & _
"WHERE rev_login = 'EllisonL'"
Set rL = CurrentProject.Connection.Execute(src, , adCmdText)
c = rL.Fields(0)
rL.Close

In this type of Recordset, is the Close statement still necessary - there
is, after all, no explicit Open?

--
Darryl Kerkeslager

If the query returns rows a recordset object is created. It very definitely
should be closed.

Randy

[Randy Harris]

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:Rz****************@newssvr30.news.prodigy.com ...

"Darryl Kerkeslager" <ke*********@comcast.net> wrote in message
news:9Z******************************@comcast.com. ..

When I open an ADO Recordset, I close it. However, it seems that there may

be some difference in this manner of opening a Recordset:

Dim rL As ADODB.Recordset
Set rL = New ADODB.Recordset

src = "SELECT Count(*) FROM reviewer INNER JOIN pp_officer " & _
"ON reviewer.reviewer_id = pp_officer.ppo_rev_id " & _
"WHERE rev_login = 'EllisonL'"
Set rL = CurrentProject.Connection.Execute(src, , adCmdText)
c = rL.Fields(0)
rL.Close

In this type of Recordset, is the Close statement still necessary - there is, after all, no explicit Open?

--
Darryl Kerkeslager

If the query returns rows a recordset object is created. It very

definitely should be closed.

Randy

BTW- If you want to retrieve data (such as a count) without creating a
recordset object, you can use the GetString method.

recCount = CurrentProject.Connection.Execute(src).GetString

[Darryl Kerkeslager]

Typical Access. Conflicting responses from two people whose opinions I
respect.

--
Darryl Kerkeslager

[Darryl Kerkeslager]

"Randy Harris" <ra***@SpamFree.com> wrote

BTW- If you want to retrieve data (such as a count) without creating a
recordset object, you can use the GetString method.

recCount = CurrentProject.Connection.Execute(src).GetString

Interesting. I was just thinking today that there ought to be a way to do
just that ...
--
Darryl Kerkeslager

[Terry Kreft]

Randy,
That still creates a recordset object.

--
Terry Kreft

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:iH****************@newssvr30.news.prodigy.com ...

BTW- If you want to retrieve data (such as a count) without creating a
recordset object, you can use the GetString method.

recCount = CurrentProject.Connection.Execute(src).GetString

[Terry Kreft]

Yes.

--
Terry Kreft

"Darryl Kerkeslager" <ke*********@comcast.net> wrote in message
news:9Z******************************@comcast.com. ..

When I open an ADO Recordset, I close it. However, it seems that there
may be some difference in this manner of opening a Recordset:

Dim rL As ADODB.Recordset
Set rL = New ADODB.Recordset

src = "SELECT Count(*) FROM reviewer INNER JOIN pp_officer " & _
"ON reviewer.reviewer_id = pp_officer.ppo_rev_id " & _
"WHERE rev_login = 'EllisonL'"
Set rL = CurrentProject.Connection.Execute(src, , adCmdText)
c = rL.Fields(0)
rL.Close

In this type of Recordset, is the Close statement still necessary - there
is, after all, no explicit Open?

--
Darryl Kerkeslager

[Randy Harris]

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:Jp********************@karoo.co.uk...

Randy,
That still creates a recordset object.

--
Terry Kreft
Please clarify Terry.

Are you saying that it creates a recordset object to retrieve the output,
then destroys it on completion of the command? I'm pretty sure that no
recordset remains after the command is completed.

Randy

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:iH****************@newssvr30.news.prodigy.com ...

BTW- If you want to retrieve data (such as a count) without creating a
recordset object, you can use the GetString method.

recCount = CurrentProject.Connection.Execute(src).GetString

[Terry Kreft]

Yes it creates a transient recordset.

The Execute method of the connection object returns a Recordset object.

You are then using the GetString method on the returned Recordset.

Now whether this would cause a leak in resources I wouldn't like to say (but
I can hear Lyle saying No <g>). Personally I don't use the GetString method
but I do use the GetRows method and when I do, I prefer to create the
recordset object and then close and set to nothing as normal.
--
Terry Kreft

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:uc****************@newssvr30.news.prodigy.com ...

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:Jp********************@karoo.co.uk...

Randy,
That still creates a recordset object.

--
Terry Kreft

Please clarify Terry.

Are you saying that it creates a recordset object to retrieve the output,
then destroys it on completion of the command? I'm pretty sure that no
recordset remains after the command is completed.

Randy

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:iH****************@newssvr30.news.prodigy.com ...

>
>
> BTW- If you want to retrieve data (such as a count) without creating a
> recordset object, you can use the GetString method.
>
> recCount = CurrentProject.Connection.Execute(src).GetString
>
>
>

[Lyle Fairfield]

"Darryl Kerkeslager" <ke*********@comcast.net> wrote in news:n4-dnYeQW-
mt**********@comcast.com:

Typical Access. Conflicting responses from two people whose opinions I
respect.

Terry Kreft is, TTBOMK, the most knowledgeable person in the world with
respect to VBA code in MS-Access modules. On this issue, I disagree with
him.

Regardless, given no other evidence or opinion and having no sense of the
thing myself, if I had to choose between Terry (and Randy) and Lyle, I
would choose to accept Terry’s position, and discard Lyle’s.

--
Lyle Fairfield

[Darryl Kerkeslager]

LOL. You do justice to "IMHO".
--
Darryl Kerkeslager

[Darryl Kerkeslager]

"Terry Kreft" <te*********@mps.co.uk> wrote

Yes it creates a transient recordset.

The Execute method of the connection object returns a Recordset object.

Wait - so there isn't even a recordset object *explicitly* created - and
certainly not one that can be closed - yet it is opened (created?)?

I'll accept that if you say so - but then, the logical conclusion is that
there must either be an internal close mechanism to the Recordset, or it can
never be closed and therefore never "garbage disposaled".
--
Darryl Kerkeslager

[david epsom dot com dot au]

You 'never' have to close an object. VBA 'always'
closes objects when they go out of scope.
"close what you open" is not the same as
"close if you use the key word 'open'
========
Access/VBA users 'never' have to close an object.
VBA 'always' closes objects when they go out of scope.

However, even with Access/VBA there is no documented
information on WHEN the VBA background task will
close an object. Also, you need to explicitly close
objects if you have reference loops (not relevant in
this example), and sometimes when you certain kinds
of implicit create/open bugs (where the object reference
gets lost).

It is different with ASP/VBscript. ASP/VBScript
objects don't go out of scope like they do when
running procedures in Access/VBA. ASP/VBScript
authors 'always' have to close objects. The supervisor
program 'never' closes objects for you.

It is also different from classic C. Classic C did
not have a management program that silently opened and
closed objects for you as a background process. In
Classic C you 'always' had to have an explicit close.

Regarding the explicit open: don't get the confused
by the mantra "close what you open". Two points:

(1) they didn't say "close if you use the key word 'open'"
and

(2) that's a classic C proverb: If you decide to
always use .close, do so because it is required in
ASP/VB script, or because it is your coding practice,
or because it deals with specific bugs, not because
it was a requirement in 1970.

(david)

"Darryl Kerkeslager" <ke*********@comcast.net> wrote in message
news:9Z******************************@comcast.com. ..

When I open an ADO Recordset, I close it. However, it seems that there
may be some difference in this manner of opening a Recordset:

Dim rL As ADODB.Recordset
Set rL = New ADODB.Recordset

src = "SELECT Count(*) FROM reviewer INNER JOIN pp_officer " & _
"ON reviewer.reviewer_id = pp_officer.ppo_rev_id " & _
"WHERE rev_login = 'EllisonL'"
Set rL = CurrentProject.Connection.Execute(src, , adCmdText)
c = rL.Fields(0)
rL.Close

In this type of Recordset, is the Close statement still necessary - there
is, after all, no explicit Open?

--
Darryl Kerkeslager

[Terry Kreft]

Darryl,
I'll respond inline to (hopefully) make it clearer.
"Darryl Kerkeslager" <ke*********@comcast.net> wrote in message
news:i_******************************@comcast.com. ..

"Terry Kreft" <te*********@mps.co.uk> wrote

Yes it creates a transient recordset.

The Execute method of the connection object returns a Recordset object.

Wait - so there isn't even a recordset object *explicitly* created - and
certainly not one that can be closed - yet it is opened (created?)?

That's right. There has to be a recordset created in order for you to get
access to the data, that is what the Execute method returns.

I'll accept that if you say so - but then, the logical conclusion is that
there must either be an internal close mechanism to the Recordset, or it
can
You would certainly hope so or else or else what you say next is true (which
is where memory leaks come from).
never be closed and therefore never "garbage disposaled".
--
Darryl Kerkeslager

Personally I would expect the recordset to be closed and set to nothing at
some time, almost certainly by (or at) the time it goes out of scope.
Unfortunately that's part of the problem; because you don't explicitly
create a variable and assign the resultset to it and then close and set it
to nothing, you don't control when it closes (or indeed if it ever does) and
that IMO is a bad programming practice.

Other people (some of whom I respect as programmers ) are happy that their
programming practice accounts for this, and that's fine, I'm not trying to
be evangelical about this or start an argument, I'm just saying why I do it
this way.
--
Terry Kreft

[Darryl Kerkeslager]

Well, I am disappointed, in that I expected an easy answer.

<sigh>

--
Darryl Kerkeslager

[Darryl Kerkeslager]

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote

Access/VBA users 'never' have to close an object.
VBA 'always' closes objects when they go out of scope.

However, even with Access/VBA there is no documented
information on WHEN the VBA background task will
close an object.

I guess in that regard it's really no different than C# or Java. Perhaps,
with the MB of memory in all the PCs users using, it would not be an issue?
And certainly (Ha!), all memory should be freed when users terminate Access
at the end of the day.

--
Darryl Kerkeslager

[Trevor Best]

Darryl Kerkeslager wrote:

Well, I am disappointed, in that I expected an easy answer.

We all want those.

[Terry Kreft]

Questions are easy, answers rarely so.

<BEG>

--
Terry Kreft

"Darryl Kerkeslager" <ke*********@comcast.net> wrote in message
news:Fv******************************@comcast.com. ..

Well, I am disappointed, in that I expected an easy answer.

<sigh>

--
Darryl Kerkeslager