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

openrecordset without using "set variable = "

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


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

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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.