By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,856 Members | 1,986 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,856 IT Pros & Developers. It's quick & easy.

Testing for an open recordset

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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
'-------------------------------

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.