By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,487 Members | 1,747 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,487 IT Pros & Developers. It's quick & easy.

Closing ADO Recordset created by Execute

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
18 Replies


P: n/a
No.

Nov 13 '05 #2

P: n/a

"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

Nov 13 '05 #3

P: n/a

"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

Nov 13 '05 #4

P: n/a
Typical Access. Conflicting responses from two people whose opinions I
respect.

--
Darryl Kerkeslager
Nov 13 '05 #5

P: n/a
"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


Nov 13 '05 #6

P: n/a
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

Nov 13 '05 #7

P: n/a
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

Nov 13 '05 #8

P: n/a

"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



Nov 13 '05 #9

P: n/a
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
>
>
>


Nov 13 '05 #10

P: n/a
"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
Nov 13 '05 #11

P: n/a
LOL. You do justice to "IMHO".
--
Darryl Kerkeslager
Nov 13 '05 #12

P: n/a
"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
Nov 13 '05 #13

P: n/a
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

Nov 13 '05 #14

P: n/a
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



Nov 13 '05 #15

P: n/a
Well, I am disappointed, in that I expected an easy answer.

<sigh>

--
Darryl Kerkeslager
Nov 13 '05 #16

P: n/a
"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

Nov 13 '05 #17

P: n/a
Darryl Kerkeslager wrote:
Well, I am disappointed, in that I expected an easy answer.


We all want those.
Nov 13 '05 #18

P: n/a
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

Nov 13 '05 #19

This discussion thread is closed

Replies have been disabled for this discussion.