Closing OpenRecordset connections

[PeregrinTook]

Hey all,

Recently my access 2k3 db started giving me an error when using the compact & repair option, telling me it may be corrupt.

I've done a bit of research and found that part of the problem at least stemmed from saving & exiting the app without closing off all opened connections.

I think I've remedied that now, but wonder if part of what I'm doing is not quite right, or not good practice at least? I'd like to hear your opinions please...

Ok so for any routines where I'm looking to build & work with a recordset, I do something like the following:

Set rstSource = gdbsCurr.OpenRecordset(pstrSQL)

Then I make sure I close off the connection when done to avoid any problems:

rstSource.Close
Set rstSource = Nothing

But sometimes I only want a count or a single value back from a table/query, so currently just do something like this:

If gdbsCurr.OpenRecordset(pstrSQL)!Count = 0 Then GoTo PROC_EXIT

My question is this though. In the second example, is this bad practice because I'm not closing off the OpenRecordset connection? Should I always set the connection explicitly like in the first example so I ensure it gets closed off, or is there another 'acceptable' method of achieving the second scenario?

I'd like to hear your thoughts please, as I can't quite seem to clarify this through searching the net...

Thanks in advance
J

 

[wazz]

always close recordsets and set them to nothing. goes for all objects: anything you "set" you should set back to nothing. also i've never seen a count of records like that, if it works, great. i usually see '...if rs.RecordCount...' (separate line). even if you stick with what you're doing, i think a '.' is more correct than a '!' before 'count'.

 

[PeregrinTook]

Thanks Wazz.

Yeah, the count method works because I'm just passing in a sql 'Select Count(CustID) as Count From tblCustomers' statement, then referencing the Count field.

And I do close every object I explicitly 'Set', my question here is more about using OpenRecordset when I haven't 'Set' it as an object, like in my earlier If statement...

Just wondering if doing that is bad practice I guess, and should I always explicitly 'Set' an OpenRecordset to an object so I can then make sure I explicitly set it back to nothing - or is there nothing wrong with the method I've used earlier in terms of leaving open connections etc?

I suspect there's prob nothing wrong with the method, I just wanted confirmation I suppose.

Thanks again
J

 

[datAdrenaline]

I utilize the With..End with construct. Also, my typical philosophy is, if I open it I close it, but .... I don't always set to Nothing, but do so frequently. In my opinion it is an "Urban Myth" as to NEEDING to do so ... I personally have not been shown an example of an unexpected behavior resulting from NOT seting to nothing ... BUT ... at the same time, there is not harm in setting to Nothing, and many argue forcefully on both sides. But with all that argument, I have not been exposed to a situation that required setting to Nothing to prevent bad stuff.

With gdbsCurr.OpenRecordset(pstrSQL)
    MsgBox "Your count is: " & !Count
    .Close
End With

As a side note ... "Count" is not the best choice for an alias name.

 

[Banana]

One more thing-

I ended up modifying my error handler to make sure that everything I write would have one exit point, and thus only one place for all closing/setting to nothing for any code:

Example:

Private Sub foo

Dim bar As Object
Dim iter As Object

On Error GoTo ProcErr

...Do something here

'We're done now, so go to exit part
Goto ProcExit
ProcErr: 'We get here if an error is raised

'Error handling goes here

ProcExit: '*REGARDLESS of whether we had an error or not, execution must come here!

bar.Close

Set bar = Nothing
Set iter = Nothing

End Sub

Other people usually write like this:

Private Sub foo()

Dim bar As Object

...Do something

ProcExit:

Set bar = Nothing 

ProcErr:

'Error Handling 

Goto ProcExit

End Sub

Either way, there's only one exit point and thus guarantees that all objects are cleaned up.

 

[wazz]

...my question here is more about using OpenRecordset when I haven't 'Set' it as an object...

ic. good question. i found this in access 2003 vba programmer's reference (wrox) (all bolds mine):

"CurrentDb...is not an object; it is a built-in function that provides a reference to the current user's default database...

CurrentDb and DBEngine(0)(0) are not the same objects internally. Access maintains a single permanent reference to the current database, but CurrentDb temporarily creates a new internal object - one in which the collections are guaranteed to be up-to-date.

...immediately after CurrentDb executes and returns a pointer, the internal object is destroyed.

...the following code will generate an error, because the reference to the current database is lost immediately after the line containing CurrentDb executes:

Dim fld As DAO.Field
Set fld = CurrentDb.TableDefs(0).Fields(0)
Debug.Print fld.Name

[edit: fails because CurrentDb was not 'Set']

This is the case for most DAO objects. One notable exception to this is the Recordset object, for which Access tries to maintain the database reference. To use CurrentDb effectively, it is always wiser to assign the reference to an object variable.

Dim dbs As DAO.Database
Dim fld As DAO.Field
Set dbs = CurrentDb
Set fld = CurrentDb.TableDefs(0).Fields(0)
Debug.Print fld.Name
dbs.Close
Set dbs = Nothing

...The reason you would use CurrentDb in preference to DBEngine(0)(0) is that you can rely on its collections being up-to-date."

also from the book:
"...calls to dbs.Close... now do absolutely nothing...dbs.Close is redundant."

and:
"Although you can work with [the default database using DBEngine(0)(0) or CurrentDb] it is often more convenient to assign it to an object variable.

Dim dbs As Database
Set dbs = DBEngine(0)(0)

...the most common method is to use the CurrentDb function."

everything i've read so far is mostly about CurrentDb and closing things. it seems that using CurrentDb is pretty important so everything is up-to-date. the last bit above implies that using (Setting) the variable is optional. in the code in your original post you never set the database object, but maybe we have to assume you did.

i haven't found any info yet that refers to using the .openrecordset method without setting the recordset object. in fact i can't find a single example so far in which the rset is not set explicitly. so, after all that, your question is still open ... use it without setting it? close it if you don't set it? set it to nothing if you haven't set it?

i suppose the answer is to set it or you might not get what you expect.

 

[PeregrinTook]

Thanks for all the replies guys.

datAdrenaline: Looks like you do suggest using a 'Close' on the OpenRecordset, I guess that's probably the safest bet, even when I don't 'Set' it to an object. I don't know for definite that leaving objects 'Set' causes problems, but I read on http://allenbrowne.com/ser-25.html that it doesn't seem to be a very clever thing to do, and once I made sure my app couldn't be exited without resetting all objects etc the initial problem I had went away - so there must be something in it at least... By the way, why do you reckon Count is not a good alias? Is that just because it could be confused with a built in function or something?

Banana: Yep I use a structure like that in your second example, only one exit point where I need to reset all objects etc.

Wazz: Thanks for the info from access 2003 vba programmer's reference. Sounds like setting the recordset to an object is optional, but when you do you should definitely always close it & set it to nothing. The gdbsCurr object I use does get set earlier in the routine, and is set to CurrentDB - plus obviously I make sure I close it later. I reckon from what you & datAdrenaline have posted, the cleanest way would be to use datAdrenaline's With...End With structure. Even if there's no definite suggestion that the method in my original post could cause any problems, it does no harm to be sure for the sake of a couple extra lines of code!

Thanks all
J