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,

On Sun, 21 Mar 2010 16:22:01 -0700, DontKnow
<(E-Mail Removed)> wrote:

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


>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,
>

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,

"Tom van Stiphout" wrote:

> On Sun, 21 Mar 2010 16:22:01 -0700, DontKnow
> <(E-Mail Removed)> wrote:
>
> 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
>
>
> >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,
> >
> .
>

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

"DontKnow" wrote:

> 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,
>
>

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

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"CJ Ardash" <(E-Mail Removed)> wrote in message
news:60B09FD5-D150-4128-BE8C-(E-Mail Removed)...
> 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
>
> "DontKnow" wrote:
>
>> 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,
>>
>>

> 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" wrote:

> On Sun, 21 Mar 2010 16:22:01 -0700, DontKnow
> <(E-Mail Removed)> wrote:
>
> 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
>
>
> >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,
> >
> .
>

On Mon, 22 Mar 2010 13:23:02 -0700, Jack Leach <dymondjack at hot mail
dot com> wrote:

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


>> 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.

"Tom van Stiphout" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Mon, 22 Mar 2010 13:23:02 -0700, Jack Leach <dymondjack at hot mail
> dot com> wrote:
>
> 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/simp...r-handler.htm?

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

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)



"Tom van Stiphout" wrote:

> On Mon, 22 Mar 2010 13:23:02 -0700, Jack Leach <dymondjack at hot mail
> dot com> wrote:
>
> 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
>
>
> >> 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.
> .
>

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.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Marshall Barton" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Douglas J. Steele wrote:
>
>>Note that only ADO recordsets have a State property. DAO recordsets have
>>nothing equivalent.
>
>
> Maybe the Recordsets collection can be useful here?
>
> --
> Marsh
> MVP [MS Access]