openrecordset without using "set variable = "

[K Williams]

I recently discovered that you can do the following:

if currentdb.openrecordset ("select statement.....").eof = true then

for cases when you just want to see if any records exist.

This obviously does not assign the recordset to any variable. My
question is, does this still use memory that is not freed because
there is no "recset.close" and "set variable = nothing"?

[Steve Jorgensen]

On 6 Feb 2004 07:59:27 -0800, ka***@regardingbooks.com (K Williams) wrote:

I recently discovered that you can do the following:

if currentdb.openrecordset ("select statement.....").eof = true then

for cases when you just want to see if any records exist.

This obviously does not assign the recordset to any variable. My
question is, does this still use memory that is not freed because
there is no "recset.close" and "set variable = nothing"?

To be safe, you should not assume this is OK. You could certainly write a
function to do that for you the right way, though, so the code everywhere else
is just as simple.

Oh, and I have found .EOF to be unreliable for checking if records were
returned in a DAO Recordset. RecordCount, though unreliable as a count prior
to .MoveLast is accurate for determining whether zero or >=1 records were
returned. This kind of thing is another good reason for centralizing this
sort of code. If you find a bug in your approach, or you come up with a
performance improvement, etc. you can fix it in just one place to handle
everywhere it's used.

Here's some air code (could use some error handling)...

Public Function DoesRecordExist( _
strSQL As String, _
Optional dbs As DAO.Database _
) As Boolean
Dim blnLocalDbs As Boolean
Dim rst As DAO.Recordset

If dbs Is Nothing Then
Set dbs = CurrentDB()
blnLocalDbs = True
End If

Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardonly)
DoesRecordExist = (rst.RecordCount > 0)

rst.Close: Set rst = Nothing

If blnLocalDbs Then set dbs = Nothing

End Function

[K Williams]

Thanks, Steve. That's good advice.

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<de********************************@4ax.com>. ..

On 6 Feb 2004 07:59:27 -0800, ka***@regardingbooks.com (K Williams) wrote:

I recently discovered that you can do the following:

if currentdb.openrecordset ("select statement.....").eof = true then

for cases when you just want to see if any records exist.

This obviously does not assign the recordset to any variable. My
question is, does this still use memory that is not freed because
there is no "recset.close" and "set variable = nothing"?

To be safe, you should not assume this is OK. You could certainly write a
function to do that for you the right way, though, so the code everywhere else
is just as simple.

Oh, and I have found .EOF to be unreliable for checking if records were
returned in a DAO Recordset. RecordCount, though unreliable as a count prior
to .MoveLast is accurate for determining whether zero or >=1 records were
returned. This kind of thing is another good reason for centralizing this
sort of code. If you find a bug in your approach, or you come up with a
performance improvement, etc. you can fix it in just one place to handle
everywhere it's used.

Here's some air code (could use some error handling)...

Public Function DoesRecordExist( _
strSQL As String, _
Optional dbs As DAO.Database _
) As Boolean
Dim blnLocalDbs As Boolean
Dim rst As DAO.Recordset

If dbs Is Nothing Then
Set dbs = CurrentDB()
blnLocalDbs = True
End If

Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardonly)
DoesRecordExist = (rst.RecordCount > 0)

rst.Close: Set rst = Nothing

If blnLocalDbs Then set dbs = Nothing

End Function