Dim rs as recordset or object? How to check if an rs is open?

[David W. Fenton]

I disagree in this particular case, but different approaches for
different folks.

My principle:

On Error Resume Next should be used for only one line of code at a
time, and only for lines of code where you know there's only one
kind of error that can happen.

 

[Stefan Hoffmann]

hi,

Ok, I've heard of that, but didn't understand it.

What questions do you have?

So it sounds like your suggestion is to close it as soon as possible, then
have error code check if any recordsets are still open, and close those?

Yes.

Most of the time I don't need a recordset which has any kind of global
scope. With one exception: performance. Under some circumstances it may
be better to open it once and requery it, instead of creating a new one.


mfG
--> stefan <--

 

[Michel Walsh]

Error handling when the end user cannot do anything about it is clearly
targeting the developer attention and most of the time, that simple
observation implies there was a false assumption (that an appropriate Assert
would have picked). So a Resume Next when coupled with


Debug.Assert Err.Number = 0

even after MANY lines is quite acceptable. Indeed...

after all, what the end user could do if an error occur when the code
evaluates

If rs Is Nothing then


And if the code fails at run time, at THIS line, is it not a problem of
LOGIC which occurred MANY LINES before? So the number of lines is quite
irrelevant, in the end, in that context.


Sure, my school of though is that error handling is to be used for the end
user in mind, while assertion is with developer in mind. But even with other
schools of thought, it is NOT infrequent to see the recommendation, as
example, to NOT apply "error handling" (even with the intention of a
developer in mind) line by line. You may easily find such references about
C++/C# try-catch error handling discussions, as example (well, have to be
aware they don't have a real Resume Next error handling, because try-catch
does not keep the 'CONTEXT' of the error).



Vanderghast, Access MVP

 

[Michel Walsh]

When a recordset object is not open, it is not a recordset object.


Nope. It is a recordset! try to assign its variable name with something
which is NOT a recordset (and NOT automatically converted for you to a
recordset) and you should get a type mismatch error.


Public Sub SimpleTest()
Dim x As DAO.Recordset
Dim y As New ADODB.Recordset
Set x = y
End Sub




Vanderghast, Access MVP

 

[GeoffG]

But so far as I know, there is no way

to know *before* you issue the rs.Close
whether or not the recordset is open or
not (except for trapping for the error that
pops up if you try to close a recordset
that isn't open).

Yes, there is. Consider the code snippet I posted
originally. I always call this code to close a
recordset:

If Not RS is Nothing then
RS.Close
Set RS = Nothing
End If

The first code line establishes whether or not RS
points to something; if RS points to something, it must
point to an open recordset: For, if you had already
called this code to close an open recordset, then RS
wouldn't point to anything the next time you called
this code and the 2nd and 3rd lines wouldn't execute
(thereby preventing a run-time error).

Regards
Geoff




in message

 

[David W. Fenton]

Error handling when the end user cannot do anything about it is
clearly targeting the developer attention and most of the time,
that simple observation implies there was a false assumption (that
an appropriate Assert would have picked). So a Resume Next when
coupled with


Debug.Assert Err.Number = 0

even after MANY lines is quite acceptable. Indeed...

after all, what the end user could do if an error occur when the
code evaluates

If rs Is Nothing then

And if the code fails at run time, at THIS line, is it not a
problem of LOGIC which occurred MANY LINES before? So the number
of lines is quite irrelevant, in the end, in that context.

The number of lines is only relevant in that the more you have, the
more likely something will dump you out of the code without turning
error handling back on. I had an app a long time ago where I was
using On Error Resume Next and assuming it would go out of scope,
and it didn't. This made me very cautious about using it anywhere at
all.

That was a problem with the way VBA worked, not a problem with the
coding. In short, it seemed that in some situations, On Error Resume
Next worked much like DoCmd.Setwarnings False, which is contrary to
expectation, but experience taught me to treat it thus.

I think the circumstances where it's a good idea are very, very few.

Sure, my school of though is that error handling is to be used for
the end user in mind, while assertion is with developer in mind.
But even with other schools of thought, it is NOT infrequent to
see the recommendation, as example, to NOT apply "error handling"
(even with the intention of a developer in mind) line by line.

I'm not suggesting anything of the sort. I'm suggesting an error
handler that uses a Case Select to pass over the error you want to
ignore and informs the user and recovers the user from any error you
didn't plan for.

On Error Resume Next is sloppy, lazy programming, for when you're
too busy to write an actual error handler.

 

[David W. Fenton]

Nope. It is a recordset! try to assign its variable name with
something which is NOT a recordset (and NOT automatically
converted for you to a recordset) and you should get a type
mismatch error.

Public Sub SimpleTest()
Dim x As DAO.Recordset
Dim y As New ADODB.Recordset
Set x = y
End Sub

What in the world is the point of this code? It is just bloody
stupid (and includes no cleanup, to boot).

 

[Michel Walsh]

The code shows what an un-open recordset *is* a recordset, to the contrary
of the initial (false) claim:


" When a recordset object is not open, it is not a recordset object. "


Vanderghast, Access MVP.

 

[Tony Toews [MVP]]

Which is not Access, of course.

And VB6 has no CurrentDB, right?

Can't recall right now. And I really doubt it. That simply wouldn't
make sense in a VB .exe context. I don't think I actually have read
any data from an MDBs tables or queries in VB6.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Michel Walsh]

There is no currentDb but you need a database object since you cannot
directly create a DAO recordset.

Dim x As New DAO.Recordset


will fail because DAO recordset are of COM-instancing: "public not
creatable". On the other hand, ADODB recordsets are "public creatable" and
you CAN write:

Dim y As New ADODB.Recordset


For a DAO recordset, since your code cannot directly create them, you need a
"factory" which will create the recordset for you, such as CurrentDb does
with Access:


Dim x As DAO.Recordset ' no NEW
Set x = CurrentDb.OpenRecordset( ... )


So, in short, while there is no CurrentDb, in VB6, you HAVE TO explicitly
create a database object which will become the factory for your DAO
recordsets.



Vanderghast, Access MVP

 

[david]

" When a recordset object is not open, it is not a recordset object. "

It is a VBA variable. It has a type library. It has a type.

Having a type library, being a pointer, does not
mean that it is a DAO Recordset object.


An XLS file is associated with Excel. If you delete
the XLS file, it is no longer a spreadsheet. It is not
anything.

But the file system still understands the type'file', even
if you have no user files, and XLS association still
stands, even if you have no spreadsheets.

If you declare a variable as a DAO recordset object, you have
a pointer declaration, but no Recordset Object methods, no
Recordset object properties, and no Recordset object.

(david)

The code shows what an un-open recordset *is* a recordset, to the
contrary of the initial (false) claim:


" When a recordset object is not open, it is not a recordset object. "


Vanderghast, Access MVP.

 

[David W. Fenton]

The code shows what an un-open recordset *is* a recordset, to the
contrary of the initial (false) claim:

" When a recordset object is not open, it is not a recordset
object. "

Are you showing that it's an object, or that it's a strongly typed
variable? That is, is VBA erroring out based on trying to assign a
value stored in two variables that are of mismatched type, or is it
actually erroring out based on the contents of the variable?

I'd believe the former to be the case.

 

[david]

But oddly enough, dao db objects were createable.

So you could either go

'implicit engine
'implicit workspace
Dim x as New DAO.database

or
Dim dbe as New DAO.dbEngine
set w = dbe.openworkspace
set d = w.opendatabase

No idea if implicit creation still works. It caused problems for
unwary users of the MFC database classes. In Access, you
can get implicit Workspace creation at start up if you try to do
DAO actions before Access has finished initialising:

Application.dbEngine
Application.dbEngine.Workspaces(0)

(david)

 

[Michel Walsh]

That is as a strong type object-variable, not most of its content since it
is pointed to nothing (null).

Vanderghast, Access MVP

 

[Michel Walsh]

It is still a VARIABLE-object. While most of its 'data' is not initialized,
in theory, we can still reach global property to the type ("static type
property") such as its ... Type. The VARIABLE still exists (while in your
example of an XLS file, the file does not exist any more). In case of some
objects (mostly those which are public creatable), you can even reach
individual property (such as "Status") since the object is still
'initialized' (and 'constructed') even if it 'data part' is still nothing.
So the VARIABLE-object is still a recordset, even if the recordset is
"close" which speaks of its DATA-part which is not "open", since a recordset
is more than just its data, even if it usefulness is mostly due to how it
can reach it.


Vanderghast, Access MVP

" When a recordset object is not open, it is not a recordset object. "

It is a VBA variable. It has a type library. It has a type.

Having a type library, being a pointer, does not
mean that it is a DAO Recordset object.


An XLS file is associated with Excel. If you delete
the XLS file, it is no longer a spreadsheet. It is not
anything.

But the file system still understands the type'file', even
if you have no user files, and XLS association still
stands, even if you have no spreadsheets.

If you declare a variable as a DAO recordset object, you have
a pointer declaration, but no Recordset Object methods, no
Recordset object properties, and no Recordset object.

(david)

 

[Michel Walsh]

Indeed, there is an additional problem brought by creatable objects (under
COM, and mostly under VBA) since you can 'create the object, but not fully
initialize it. As example


Dim x AS New ADODB.Recordset


you have an ADODB recordset, fully 'constructed', but not ideally
'initialized'. By comparison, a not-creatable object obliges you to pass
through a 'factory' which MAY acts like an initializer:


Dim y AS DAO.Recordset
Set y = CurrentDb("SELECT ... )

were here, the recordset is not only 'created' but also properly
'initialized' as in 'set to total intended functionality's . After all, I
hope to imagine that people did not invented 'not-creatable object just to
make the developer life harder, but easier.


Vanderghast, Access MVP

 

[David W. Fenton]

So the VARIABLE-object is still a recordset, even if the recordset
is "close" which speaks of its DATA-part which is not "open",
since a recordset is more than just its data, even if it
usefulness is mostly due to how it can reach it.

I disagree. A variable of type recordset that contains no pointer to
a recordset is not a recordset. It's a recordset variable.

Even when you've created a recordset and stored the pointer to it in
a recordset variable, the variable itself is *still* not a
recordset. I is always a recordset variable. Sometimes it points to
a recordset (open or closed, in fact). Sometimes it doesn't. But the
variable is never the recordset itself.

 

[Michel Walsh]

The variable will always be a pointer to a recordset, its "data connection"
being close, or open. Closing the recordset only 'close' the access to the
data, it does not necessary destroy other data, (overhead maintenance) :
close is not the same as setting the object to nothing after all. And even a
variable-object set to nothing is more than a simple 'null' (nothing).


Vanderghast, Access MVP

 

[david]

You can postulate a particular object type where:

close is not the same as setting the object to nothing after all.

But actually, for a DAO Recordset object, there is not much
difference. It is not just a case of it's "data connection being
closed, or open".

In fact, and this is just for the particular case of the DAO recordset
object, when you close a DAO recordset object, as far as it is able
it destroys itself.

(david)

The variable will always be a pointer to a recordset, its "data
connection" being close, or open. Closing the recordset only 'close' the
access to the data, it does not necessary destroy other data, (overhead
maintenance) : close is not the same as setting the object to nothing
after all. And even a variable-object set to nothing is more than a simple
'null' (nothing).


Vanderghast, Access MVP

 

[David W. Fenton]

The variable will always be a pointer to a recordset,

No, it won't.

In this code:

Dim rs As DAO.Recordset

If I then refer to the contents of rs, there's no pointer there,
because it hasn't been initialized.

If I open a recordset and assign its pointer to the recordset
variable, and then write:

rs.Close
Set rs = Nothing

there won't be any pointer stored in that recordset variable.

its "data connection"
being close, or open. Closing the recordset only 'close' the
access to the data, it does not necessary destroy other data,
(overhead maintenance) : close is not the same as setting the
object to nothing after all.

And I never said anything else. Indeed, I've been arguing for years
that closing a recordset and setting its recordset variable to
Nothing are two separate operations that are independent.

And even a
variable-object set to nothing is more than a simple 'null'
(nothing).

I didn't say it was Null -- someone else did. But the pointer is
cleared when you set it to Nothing. And it will be Nothing, which is
the term describing an object variable with no pointer stored in it.