Testing for an open recordset

[Colleyville Alan]

I ran into problems in an app in which I received a msg of "cannot lock the
table, it is in use by another user". It turns out that I had opened a
recordset with a command like set rstmyrecs = openrecordset("Tablename") to
get a record count and I was trying to run a query to modify the table even
though I had not yet closed the recorset.

I fixed most of them, but I was wondering if there is a way to test if a
recorset is open. I got hit with the same error msg the other day and I
realized that while my code released the recordset variable from memory
(rstmyrecs = nothing), it did not close it. I seem to recall getting an
error msg if I tried to close a recorset that was not open or whose variable
was already released from memory (cannot recall which).

Anyhow, I'd like to be able to test to see if the recordset is open to do
something like

If recordset rstmyrecs is open then
rstmyrecs.close
end if

Is there such a command or some other way to achieve the same thing?
Thanks

[Wayne Morgan]

You can test to see if the variable is set

If rst = Nothing Then

but to catch the other, when you try to Close the recordset, the easiest
thing to do is to just trap the error. Unless you are doing something that
specifically would require otherwise, it is usually easiest to use a two
command statement as if it is a single command.

rst.Close
Set rst = Nothing

--
Wayne Morgan
MS Access MVP
"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:_VOOc.63586$eM2.49687@attbi_s51...

I ran into problems in an app in which I received a msg of "cannot lock the table, it is in use by another user". It turns out that I had opened a
recordset with a command like set rstmyrecs = openrecordset("Tablename") to get a record count and I was trying to run a query to modify the table even though I had not yet closed the recorset.

I fixed most of them, but I was wondering if there is a way to test if a
recorset is open. I got hit with the same error msg the other day and I
realized that while my code released the recordset variable from memory
(rstmyrecs = nothing), it did not close it. I seem to recall getting an
error msg if I tried to close a recorset that was not open or whose variable was already released from memory (cannot recall which).

Anyhow, I'd like to be able to test to see if the recordset is open to do
something like

If recordset rstmyrecs is open then
rstmyrecs.close
end if

Is there such a command or some other way to achieve the same thing?
Thanks

[John Mishefske]

Colleyville Alan wrote:

I ran into problems in an app in which I received a msg of "cannot lock the
table, it is in use by another user". It turns out that I had opened a
recordset with a command like set rstmyrecs = openrecordset("Tablename") to
get a record count and I was trying to run a query to modify the table even
though I had not yet closed the recorset.

I fixed most of them, but I was wondering if there is a way to test if a
recorset is open. I got hit with the same error msg the other day and I
realized that while my code released the recordset variable from memory
(rstmyrecs = nothing), it did not close it. I seem to recall getting an
error msg if I tried to close a recorset that was not open or whose variable
was already released from memory (cannot recall which).

Anyhow, I'd like to be able to test to see if the recordset is open to do
something like

If recordset rstmyrecs is open then
rstmyrecs.close
end if

Is there such a command or some other way to achieve the same thing?
Thanks

Not in DAO (you could set a flag when you open the recordset and then test the
flag to determine whether to close it.

ADO Recordsets have a .State property that is set to adStateOpen (1) when open.

--
'-------------------------------
' John Mishefske
'-------------------------------