Check state of a RecordSet?

[TheSpoon]

I want to know if a RecordSet object is open before I call .Close, is there a way to find this out?


Thanks

 

[KenReay]

Hi

Why not just close it, trap any resulting error and continue?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RoyVidar]

Hi!

Here's something I might use before exiting a sub when using ADO, should you use DAO, I don't know the equivalent property:

[tt]if (not rs is nothing) then
if (rs.state=adstateopen) then
rs.close
end if
set rs=nohting
end if[/tt]

Roy-Vidar

 

[TheSpoon]

RoyVidar: Yeah, I saw that property when searching google on the topic, but unfortunately (to my knowledge) there is no equivalent property in DAO.

KenReay: Guess I'll have to do that.

 

[TheAceMan1]

How are ya TheSpoon . . . . . Can do in DAO!

When a recordset is first open, its added to the [blue]Recordsets Collection[/blue]. First problem with this is, its tied to the [blue]Database Object[/blue] (unless a clone) of the running routine. This makes it harder to look for open recordsets across any number of running routines throughout the database. Second problem (in ADO as well) is [blue]detecting multiple instances[/blue] of the same recordset!

[blue]Definition : Multiple Instances
More than one recordset object having the same source.[/blue]

The following function circumvents both these problems. It returns [purple]0[/purple] if the recordset is closed/does'nt exist, and a [purple]number > 0[/purple] representing the number of open instances found.

Note: The [purple]Source[/purple] of db.OpenRecordset([purple]Source[/purple], Type) as an example, is whats passed to the routine. So if its a large SQL . . . gotta pass it, as its the source.

[blue]Public Function IsOpenRst(rstSource As String) As Integer
   [green]'Return = 0 = rstSource not open/exist
   'Return > 0 = # of open instances of rstName
   'rstSource is the same Source in db.OpenRecordset(Source, Type)[/green]

   Dim ws As Workspace, db As DAO.Database, rst As DAO.Recordset
   Set ws = DBEngine.Workspaces(0) [green]'Default WorkSpace[/green]
   
   For Each db In ws.Databases
      For Each rst In db.Recordsets
         If rst.Name = rstSource Then [green]'rst.Name returns the Source![/green]
            IsOpenRst = IsOpenRst + 1
         End If
      Next
   Next
   
   Set ws = Nothing
End Function[/blue]

With slight modification, the routine can be made to close one or all instances of the same recordset.

Give it a whirl . . . . . . .

See Ya! . . . . . .

 

[PHV]

Anyway, the DAO help file claims that setting a recordset to nothing produces the same result as closing it ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya PHV . . . . .

From the help file for [blue]Nothing[/blue]:

[blue]Several object variables can refer to the same actual object. When Nothing is assigned to an object variable, that variable no longer refers to an actual object. When several object variables refer to the same object, [purple]memory and system resources associated with the object to which the variables refer are released only after all of them have been set to Nothing[/purple], either explicitly using Set, or implicitly after the last object variable set to Nothing goes out of scope.[/blue]

See Ya! . . . . . .