How to determine if an object is still open

[DontKnow]

Hi Guys,

Does anyone know how to check if a dao database or recordset has been left
open?? I would like to run some code and determine if I have inadvertenly
left open an object that requires closing ie. dbs.close or rs.close (and Set
rs = Nothing).

I would like to use this code to seacrch the entire database looking for
objects that have not been closed...

Many thnaks for your input guys,

Cheers,

 

[Tom van Stiphout]

On Sun, 21 Mar 2010 16:22:01 -0700, DontKnow

You can really only do this for global database and recordset objects.
If you declare a recordset object in a procedure and then not close
it, there is no way (unless you go to extremes) to test if it is still
open outside of that procedure.
I would spend a few minutes with the Find dialog and find all
occurrences of "As Recordset" and its variants, and fix the code if
needed.

-Tom.
Microsoft Access MVP

 

[DontKnow]

Many thanks,

The reason I ask is :

I have a network that utilises the shorten version (ie. does not require the
full version of access) and if an object is left open then when a user tries
to open the database and there were objects left open previously, the
database provides an error and does not allow the user into the database
without rebooting the computer.

I have found most of the open objects abnd was ensuring that I have done
this completely ie. no objects inadvertenily left open!!

Cheers,

 

[CJ Ardash]

While you may not be able to search the entire database, there is a way to
check to see if any given recordset is open or closed. Recordsets have a
state property which you can check. Its possible values are adStateOpen,
adStateClosed, adStateConnecting, adStateExecuting, and adStateFetching. if
you add this code at the end of every procedure which has a recordset as one
of its variables and use it on any global recorset variables when the
database closes, you shouldn't have a problem with recordsets remaining open.

If rs.State <> adStateClosed Then
rs.Close
Set rs = Nothing
End If

CJ

 

[Douglas J. Steele]

Note that only ADO recordsets have a State property. DAO recordsets have
nothing equivalent.

 

[Jack Leach]

there is no way (unless you go to extremes) to test if it

Care to give a brief explanation of those extremes? I've always wished
there were some way to programmatically evaluate any variables in scope...
return a list of them or something.


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Tom van Stiphout]

There is a company - I can't think of their name right now - that
provides a way to get to the call stack and stack variables. They
built a global error handler based on this understanding.

Also, these objects are DAO or ADO recordsets which are managed to
some extent by their respective libraries, and with a lot of effort
one would be able to understand how this is done and use it to one's
advantage.

Yes, access to the call stack was one of the items the Access MVPs
mentioned when we discussed possible features for Access 15 with the
Access team. I'm not holding my breath.
..NET programming makes this a lot easier.

-Tom.
Microsoft Access MVP

 

[Douglas J. Steele]

There is a company - I can't think of their name right now - that
provides a way to get to the call stack and stack variables. They
built a global error handler based on this understanding.

Tom: Are you talking about the SimplyVBA Global Error Handler Wayne Phillips
has at http://www.everythingaccess.com/simplyvba-global-error-handler.htm?

 

[Jack Leach]

Extremes... no kidding. I'll leave this one alone

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Douglas J. Steele]

Maybe, but the Name property simply returns the SQL that was used to open
the report, so you may have an issue determining which recordset is which.

 

[ChrisO]

I have a network that utilises the shorten version (ie. does not require the
full version of access) and if an object is left open then when a user tries
to open the database and there were objects left open previously, the
database provides an error and does not allow the user into the database
without rebooting the computer.<<

Has that been verified?

 

[vanderghast]

In the full version of Access, you can open the Locals Window (in VBE, under
View) and not only get all the variables under the scope, but you can also
browse their properties too, (if they are objects). Sure, the code has to be
in a "stop" mode... after you started some execution (else there is no much
to see)


Vanderghast, Access MVP