Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 02-01-2011, 09:38 AM   #1
PhoenixofMT
Part-time Access Guru
 
Join Date: Jul 2008
Location: Montana
Posts: 35
Thanks: 2
Thanked 6 Times in 3 Posts
PhoenixofMT is on a distinguished road
How to check for an open recordset?

I have some code that looks something like this:
Code:
  If Nz(some_field, "") then err.Raise 94, etc.

  strSQL = "SQL query"
  
  Set db = CurrentDb()
  Set rst = db.OpenRecordset(strSQL)

  bunch more code...

Exit_MyFunction:
  rst.Close              
  Set rst = Nothing
  Set db = Nothing
  Exit Function

Error_MyFunction
  MsgBox err.DESCRIPTION, etc.
  Resume Exit_MyFunction
The problem I have is that if that null check errors out, I get an "Object variable or With block variable not set" error when I hit the rst.Close line in the exit section. This can lead to endless loops.

Is there a way to check that rst is open before I try to close it? I've run into this a couple of times, usually when I comment something out temporarily and it works again when I un-comment it.
In this instance my solution has been to move the null check below the "Set rst =", but I'd like to be able to check it explicitly, rather than be sure my errors will always happen in the right places.

Thanks,

-- Phoenix


Last edited by PhoenixofMT; 02-01-2011 at 10:19 AM.
PhoenixofMT is offline   Reply With Quote
Old 02-01-2011, 01:04 PM   #2
LPurvis
AWF VIP
 
LPurvis's Avatar
 
Join Date: Jun 2008
Location: North of England
Posts: 1,269
Thanks: 0
Thanked 33 Times in 33 Posts
LPurvis has a spectacular aura about LPurvis has a spectacular aura about LPurvis has a spectacular aura about
Re: How to check for an open recordset?

Hi

This does come up from time to time - and it's not a hard and fast answer.
Have a look at some existing threads.
http://www.utteraccess.com/forum/Tes...-t1946173.html
Which links to the more directly applicable
http://www.utteraccess.com/forum/Det...-t1925457.html

However - and here's where I, uncaringly, put the cat among the pigeons...
I would simply plough on regardless.
Exit_MyFunction:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

This is the closing (tidyup) label of the procedure. You have to ask if you ever want to enter an error handler in such cases.

Cheers
__________________
Leigh Purvis | Microsoft Access MVP |
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
LPurvis is offline   Reply With Quote
Old 02-01-2011, 01:46 PM   #3
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Access help for DAO.Recordset.Close and DAO.Recordset2.Close says ...
Quote:
An alternative to the Close method is to set the value of an object variable to Nothing (Set dbsTemp = Nothing).
So it's probably sufficient to do this ...
Code:
Exit_MyFunction:
  Set rst = Nothing
  Set db = Nothing
  Exit Function
...which won't raise an error.

__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky

Last edited by MarkK; 02-01-2011 at 03:28 PM. Reason: clarify the recordsets as DAO
MarkK is offline   Reply With Quote
Old 02-01-2011, 02:20 PM   #4
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: How to check for an open recordset?

That's right lagbolt, it wouldn't raise an error. Some would argue that it's necessary to close the recordset (to avoid possible memory leaks), but for me it depends on the situation.

Here are two other ways if you really want to close the recordset:
Code:
Exit_MyFunction:
    if err.number <> 94 then
        rst.Close              
        Set rst = Nothing
        Set db = Nothing
    end if
    Exit Function
Code:
Exit_MyFunction:
    if not (rst is nothing) then
        rst.Close              
        Set rst = Nothing
        Set db = Nothing
    end if
    Exit Function
vbaInet is offline   Reply With Quote
Old 02-01-2011, 02:22 PM   #5
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,828 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: How to check for an open recordset?

Quote:
Originally Posted by vbaInet View Post
Some would argue that it's necessary to close the recordset (to avoid possible memory leaks)
Interesting - since setting it to nothing removes it from memory
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 02-01-2011, 02:27 PM   #6
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: How to check for an open recordset?

Quote:
Originally Posted by boblarson View Post
Interesting - since setting it to nothing removes it from memory
Some would argue that even though the reference (i.e. the pointer to memory) is broken, it still is in memory. Notice I said some, I'm indifferent

I bet there's been some arguments about this on here.
vbaInet is offline   Reply With Quote
Old 02-01-2011, 03:28 PM   #7
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
The quote from the help file is not ambiguous. Obviously people can still argue.

__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 02-01-2011, 03:37 PM   #8
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: How to check for an open recordset?

It certainly isn't. But it is interesting that in the same help file some code examples include both rs.Close and set rs = nothing.
vbaInet is offline   Reply With Quote
Old 02-01-2011, 05:08 PM   #9
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,657
Thanks: 98
Thanked 1,500 Times in 1,415 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: How to check for an open recordset?

I use the following style on ADO recordsets.
This is to close and remove it but it demonstrates the syntax.

Code:
If Not rs Is Nothing Then
If rs.State = adStateOpen Then: rs.Close
Set rs = Nothing
End If
(Note the colon in "Then:" is Syntactic Sugar. I include it to help draw attention to the fact I have used a single line IF, following the style of "Else:" in the single line Else.)

Regarding the failure to close and remove recordsets, I agree with lagbolt. Access help is unequivocal. I don't see the point of ignoring the advice when it is so easy to inclue the few lines, especially if encompassed as a function.

If you doubt its need when using a pointer to a connected recordset, perhaps consider what might be the case in a disconnected recordset where the whole thing exists in memory.
Galaxiom is offline   Reply With Quote
Old 02-01-2011, 10:16 PM   #10
LPurvis
AWF VIP
 
LPurvis's Avatar
 
Join Date: Jun 2008
Location: North of England
Posts: 1,269
Thanks: 0
Thanked 33 Times in 33 Posts
LPurvis has a spectacular aura about LPurvis has a spectacular aura about LPurvis has a spectacular aura about
Re: How to check for an open recordset?

Not quite the can of worms it could have been - but unsurprising to see it echo on none the less. :-)

I think a lot of what I have to say on the use of the Close method has already been said in this thread here, from a while back. So I'll try to avoid repeating it here.
There are occasions to not treat Help files as gospel - but Helpful. ;-) Everything was written by a person.
Early in its development cycle, DAO had some memory leak problems. It's widely believed they were fixed by MS, I'm certainly inclined to think so. I just don't feel the need to give myself completely to that belief.

If I can highlight some comments to make points about...

>> So it's probably sufficient to do this ...
My point here is, hopefully, self evident. ;-)

>> Interesting - since setting it to nothing removes it from memory
Absolutely (my linked thread post is relevant here). However, again in theory, so does allowing the procedure to end. VBA garbage collection would then remove all local variables from memory. So why do anything at all? To my mind, if you're going to do anything - then be thorough.

>> If you doubt its need when using a pointer to a connected recordset, perhaps consider what might be the case in a disconnected recordset
I'm not entirely sure of the nuance being raised here. Are you saying this is a case in point supporting thoroughly releasing and closing the object variables? (The separate function comment would seem to imply as much - i.e. the "you might as well" viewpoint.)

They're both (DAO connected and ADO disconnected) objects in memory, one of which happens to maintains a connection to the database still. Both still prone to any bugs or quirks the developers of the objects lovingly imbued them with.
For example, a shaped provider recordset accessing a Jet database will fail to disconnect the inherent connection - even when disconnected. Bug/necessity due to the provider - hard to say. But not what you'd expect.

Everyone can, and will continue to do either what they were taught/learned in their formative years - or what they've decided subsequently. For me, it's a question of harm/effect. Procedure cleanup is a nice, self documenting opportunity to say "OK, am I aware of the objects I've used in this procedure and are they all actually used and worthwhile - as I now dispose of them".

Cheers
__________________
Leigh Purvis | Microsoft Access MVP |
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
LPurvis is offline   Reply With Quote
Old 02-01-2011, 10:48 PM   #11
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,657
Thanks: 98
Thanked 1,500 Times in 1,415 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: How to check for an open recordset?

Quote:
Originally Posted by LPurvis View Post
>> If you doubt its need when using a pointer to a connected recordset, perhaps consider what might be the case in a disconnected recordset
I'm not entirely sure of the nuance being raised here. Are you saying this is a case in point supporting thoroughly releasing and closing the object variables? (The separate function comment would seem to imply as much - i.e. the "you might as well" viewpoint.)
It has been said on this forum that the recordset is just a pointer and even if it did live on after the sub closed then the leak would be a very slow drip. Obviously a disconnected recordset is a whole lot more than a pointer so I my comment was meant to suggest a fire hose.

The trouble is all this stuff is in the shadows leaving it open to the likes of us to speculate on what goes on there. I am grateful for the input of those with better night vision than me but in the end it is still speculation to some extent.

I have seen some developers say that even setting the object to Nothing is superfluous. The lazy part of me says that the garbage collection should be OK but the conservative says to do what the programmers say.

One thing for sure is that if anything does go wrong it is better to cover my behind than try to justify why I didn't do it as recommended by Microsoft to some boss who accepts the word of an expensive consultant saying I am an idiot because I didn't do it right, even if it isn't what actually causes the problem.

You have gathered my drift with the function comment. A function with a recordset argument makes the cleanup a single command. It makes the lazy part of me happy without having to be frightened of that consultant.
Galaxiom is offline   Reply With Quote
Old 02-02-2011, 03:53 AM   #12
LPurvis
AWF VIP
 
LPurvis's Avatar
 
Join Date: Jun 2008
Location: North of England
Posts: 1,269
Thanks: 0
Thanked 33 Times in 33 Posts
LPurvis has a spectacular aura about LPurvis has a spectacular aura about LPurvis has a spectacular aura about
Re: How to check for an open recordset?

Well all object variables are just pointers to the objects themselves in memory - created by the components of the library in question. Yes, a static ADO recordset (that's the biggest difference of data location) places a heavier client-side memory burden - but that is really the responsability of ADO to maintain. Not our VBA variable pointing at it.
Indeed it's that exact concept which makes me feel that the Close method is always important. Different programming environments handle their own variables (and pointers) differently. But the library in question (in this case ADO) should handle its own objects the same - releasing what is to be released when required (be that by an explicit call the to the Close method or just similar code called when the object is terminated... We can only hope that is the implementation... unless we are explicit ourselves, then that's all that can be done).

Until MS release the open source of all their libraries (which is half past never) then there will always be some speculation - even though we've have feedback from those on the inside in the past to help fuel the debate.

And as for these consultant types? Meh. They're just weird. And dress funny.

Cheers.
__________________
Leigh Purvis | Microsoft Access MVP |
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
LPurvis is offline   Reply With Quote
Old 02-02-2011, 02:22 PM   #13
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,657
Thanks: 98
Thanked 1,500 Times in 1,415 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: How to check for an open recordset?

Thanks Leigh. Logical and imformative as always.

The trouble with consultants is that management feels compelled to accept their advice because they are paid so much. To ignore their judgement would be to flag that the decision to engage them was wrong and a waste of money (which is often the case ).

Managers want to be seen to be responsible when out of their depth. Unfortunately they don't normally realise they are out of their depth when making the orignial decisions. Getting the consultant insulates them from the consequences of their ineptitude.

In Australia, the Board of Directors of one large bank gave themselves a huge bonus as reward for their wisdom of engaing a consultant to make a decision for them. I wish I could get a bonus for suggesting that someone else did my work. Unfortunatlely for ordinary folk we would only get retrenchment as a reward.
Galaxiom is offline   Reply With Quote
Old 02-02-2011, 03:39 PM   #14
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,566
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: How to check for an open recordset?

My best advice was already given in an earlier post by LPurvis.

My preferred style is to "bracket" anything that gives you an error of that type by using On Error Resume Next to block all traps when you are doing a close of a recordset that could already be closed, then if appropriate, reassert the error trap with a new On Error GoTo Fubar. Along those lines, I am a belt AND suspenders person, so I also include the Set rsvar = Nothing after the rsvar.Close method is executed. OK, overkill. But if you are going to beat a dead horse, beat it hard. No sense in half-measures.

I also feel that this principle can legitimately be expanded to other actions dealing with closing of forms and reports, disposal of dynamically allocated memory, etc.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 02-02-2011, 04:28 PM   #15
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,657
Thanks: 98
Thanked 1,500 Times in 1,415 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: How to check for an open recordset?

Quote:
Originally Posted by The_Doc_Man View Post
My preferred style is to "bracket" anything that gives you an error of that type by using On Error Resume Next to block all traps
Well I guess On Error Resume Next is 100 percent reliable but I think I prefer:
If Not rs Is Nothing

Quote:
I also feel that this principle can legitimately be expanded to other actions dealing with closing of forms and reports, disposal of dynamically allocated memory, etc.
Now why is there no UnDim to take those variables away?

Galaxiom is offline   Reply With Quote
Reply

Tags
check , open , open recordset , record set , recordset

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA to check if recordset is empty? paul1707 Modules & VBA 5 08-07-2011 06:46 PM
ADO RecordSet - how to go to a specific record in an already open RecordSet darbid General 2 03-14-2010 10:41 PM
Open Recordset from variable recordset error Sevn Modules & VBA 17 02-17-2010 03:47 AM
Check if recordset is populated? DanWallace Modules & VBA 19 12-09-2008 02:32 PM
Can you open a recordset from another recordset in Access 2003? TylerTand Modules & VBA 2 05-28-2008 11:21 AM




All times are GMT -8. The time now is 05:26 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World