is Nothing vs = Empty

[Laurel]

I want to put code in my routines to be sure that a recordset is never left
unclosed, to prevent memory leaks, as I've been advised. The following
script was suggested, and it seems to work fine. Sometime the recordset
shows a value of "Nothing" when I hover over it in the debugger, and the
code is not executed. Sometimes it is populated, and the code is executed.
Great. But sometimes the recordset shows a value of "Empty" when I hover
over it in the debugger, and in those cases the code is executed and I get
an error: "Object Required" I haven't been able to figure out what causes a
recordset to = "Empty" in some situations and "Nothing" in others. Can
someone suggest code that will work in all situations? I don't know how to
test for "Empty."

If Not (irst_ClassPeriods Is Nothing) Then
irst_ClassPeriods.Close
Set irst_ClassPeriods = Nothing
End If

 

[Dirk Goldgar]

In

I want to put code in my routines to be sure that a recordset is
never left unclosed, to prevent memory leaks, as I've been advised.
The following script was suggested, and it seems to work fine.
Sometime the recordset shows a value of "Nothing" when I hover over
it in the debugger, and the code is not executed. Sometimes it is
populated, and the code is executed. Great. But sometimes the
recordset shows a value of "Empty" when I hover over it in the
debugger, and in those cases the code is executed and I get an error:
"Object Required" I haven't been able to figure out what causes a
recordset to = "Empty" in some situations and "Nothing" in others. Can
someone suggest code that will work in all situations? I don't
know how to test for "Empty."
If Not (irst_ClassPeriods Is Nothing) Then
irst_ClassPeriods.Close
Set irst_ClassPeriods = Nothing
End If

"Empty" is the value of an uninitialized Variant. If you declare your
recordset objects as Recordset:

Dim irst_ClassPeriods As Recordset


.... they will never be Empty. If you declare them as Variant:

Dim irst_ClassPeriods As Variant

or

Dim irst_ClassPeriods

.... or if you never declare them at all (replying on VBA to declare them
automatically), then they will be Empty until you assign a value to
them.

I suggest you never use Variant variables to hold recordsets. If you
don't want to identify the type of recordset -- for example, you want to
allow for the possibility that the recordset is either a DAO.Recordset
or and ADODB.Recordset -- then declare the recordset variable as Object.

I don't know how to test for "Empty."

You can use the IsEmpty() function to test whether a Variant is empty.
But if you declare your recordset objects properly, you won't need to do
that.

 

[Laurel]

Thanks. I was a little late in figuring out that the following dim
statement declared irst_Students as a variant, not a recordset, so these are
lying around.

Dim irst_Students, irst_ClassPeriods As Recordset

Your fix works fine (Dim irst_Students as recordset, irst_ClassPeriods as
recordset).

 

[Dirk Goldgar]

In

Thanks. I was a little late in figuring out that the following dim
statement declared irst_Students as a variant, not a recordset, so
these are lying around.

Dim irst_Students, irst_ClassPeriods As Recordset

That's a very popular error. <g>

 

[Jamie Collins]

I want to put code in my routines to be sure that a recordset is never left
unclosed, to prevent memory leaks, as I've been advised.

If Not (irst_ClassPeriods Is Nothing) Then
irst_ClassPeriods.Close
Set irst_ClassPeriods = Nothing
End If

What about if the recordset is not Nothing but isn't open? The
paranoid coder pattern I see is more like this:

On Error Resume Next
irst_ClassPeriods.Close
DoEvents
Set irst_ClassPeriods = Nothing
DoEvents
On Error Goto Err_Handler
blnSuccess = irst_ClassPeriods Is Nothing

IMO you should know when a recordset is open/Nothing so a paranoid
catch-all routine should be unnecessary.

Jamie.

--

 

[Jamie Collins]

<<snipped>> you want to
allow for the possibility that the recordset is either a DAO.Recordset
or and ADODB.Recordset

The OP mentioned 'memory leaks' therefore it's probably a DAO
recordset ;-)

Jamie.

--