Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I test if a recordset is open?

Status
Not open for further replies.

tedsmith

Programmer
Nov 23, 2000
1,762
AU
How can I determine if a database or recordset is open or has been closed?
Such test as If IsNull(Mydb), If Mydb.Name>"" or any attempt to read a property of the database or recordet once it has been closed, produces an error. Closing seems to destroy it.
IsEmpty gives the same result whether open or closed.

I resorted to having another flag variable that I make true if it is open but it would be neater if there was direct way.

 
What technology are you using to connect to your database? ADO, DAO, etc....

If you are using ADO, then you can check to see if the Connection object has been initialized with this...

[tt]
If myDb Is Not Nothing Then
if myDb.State <> adStateClosed
[/tt]


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
I am using DAO in this case so there is no myDB.state.

This is the sort of thing that I want for DAO!

If myDb Is Not Nothing Then . . . . gives an error on compiling.

 
in vba i believe it is
if not myDB is nothing then
 
sorry wrong forum but believe it is the same in vb as well, and in .net both is nothing and isnothing()
 
I discovered that "If (mydb is not nothing)" works in vb6 but not without the brackets.
However it tells you if mydb has been set or not but not if it is closed or open.

I had hoped there would be an API .State for DAO! strongm where are you?
 
I did this in vba assume works in vb

Public Function isOpenDAO(rs As DAO.Recordset) As Boolean
On Error GoTo err_close
Dim tempRs As DAO.Recordset
If Not rs Is Nothing Then
Set tempRs = rs.Clone
tempRs.Close
isOpenDAO = True
End If
Exit Function
err_close:
If Not Err.Number = 3420 Then
MsgBox Err.Number & " " & Err.Description
End If
End Function
 
>recordset is open or has been closed?

In DAO the database object's .Recordsets collection contains all the open recordsets. So if your recordset is not in that collection then it must be closed.
 
Using that approach

Public Function isOpenDAO(db As DAO.Database, rs As DAO.Recordset) As Boolean
On Error GoTo err_close
Dim tempRs As DAO.Recordset
For Each tempRs In db.Recordsets
If tempRs Is rs Then isOpen2DAO = True
Next tempRs
Exit Function
err_close:
MsgBox Err.Number & " " & Err.Description
End Function
 
Sorry please disregard "If (mydb is not nothing)"
It seemed to work when I tried it but I must have been drunk!

" If Not Mydb Is Nothing " returns True whether a set database is open or just been closed on my computer. It only tells if it has been initially set or set to nothing after being closed.

The recordset.collection method is fine for indication if the recordset is open but how do I tell if the database is open or set or not without resorting to an error detection routine?
I cant read the recordset collection without an error, if the database has been closed.

Any other attempt to use the word mydb when it is not open, results in an error.
 
Title of the thread is "How do I test if a recordset is open?", so I thought it more important to address that first.

We can do a similar trick with the database although, assuming you only ever have one database open at a time, we can streamline it a bit.

Here's an example:
Code:
[blue]Private Function isDBOpen() As Boolean
    Dim dbe As DBEngine
    Set dbe = New DBEngine
    isDBOpen = dbe.Workspaces(0).Databases.Count
End Function[/blue]

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top