Recordset multiple open and close

[blue875]

Hello helper people who are smarter than me:
I have a form that needs to submit multiple queries to different tables
during one Sub's execution. Some sections are as simple as:

1| With rst
2| .Open query1
3| .Close
4| .Open query2
5| End With

However, what I run into is that .Open and .Close don't always get
triggered, or maybe the state isn't set, but I do know I frequently run
into the error "Method not allowed when the object is open" message at
places like line 4.

I take the next step: run an empty loop that waits for the Recordset to
Close before going on:

1| With rst
2| .Open query1
3| .Close
4| Do While .State <> adStateClosed
5| Loop
6| .Open query2
7| End With

Problem is, I get stuck in an infinite loop, because the recordset
never closes, even though ".Close" was the last command read before
going into the loop?

So, what would cause .Close not to close?

[salad]

bl*****@carolina.rr.com wrote:

Hello helper people who are smarter than me:
I have a form that needs to submit multiple queries to different tables
during one Sub's execution. Some sections are as simple as:

1| With rst
2| .Open query1
3| .Close
4| .Open query2
5| End With

However, what I run into is that .Open and .Close don't always get
triggered, or maybe the state isn't set, but I do know I frequently run
into the error "Method not allowed when the object is open" message at
places like line 4.

I take the next step: run an empty loop that waits for the Recordset to
Close before going on:

1| With rst
2| .Open query1
3| .Close
4| Do While .State <> adStateClosed
5| Loop
6| .Open query2
7| End With

Problem is, I get stuck in an infinite loop, because the recordset
never closes, even though ".Close" was the last command read before
going into the loop?

So, what would cause .Close not to close?

So why are you closing the recordset prior to opening query2? You might
be better op opening rst with query1, closing. Then create a new
With/Endwith set for query2.

[Rich P]

Instead of opening/closing queries you could write the queries directly
in your loop using an array of select statements from your Form's code
module:

Sub RunMultipleQueries()
Dim arrQ As Variant, i As Integer, j As Integer, k As Integer
Dim DB As DAO.Database, RS As DAO.Recordset

arrQ = Array("Select fldx From tbl1 Where fldy = '" & txtSomething &
"'", "Select fldx From tbl2 Where fldy = '" & txtSomethingElse & "'")

Set DB = CurrentDB
For i = 0 to Ubound(arrQ)
Set RS = DB.OpenRecordset(arrQ(i))
Do While Not RS.EOF
'Do your stuff
RS.MoveNext
Loop
RS.Close
Next
End Sub

As you can see, this procedure also accommodates/uses parameters very
easily. If you want to run this procedure from a standard module
instead of a Form module, you can replace txtSomething/SomethingElse
with string variables that you could either pass in to the procedure or
use global string vars.

Sub RunMultipleQueries(strSomething As String, strSomethingElse As
String)
...
End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[blue875]

I get an error when I consecutively call
..Open query1
..Open query2
citing that you can't open an open recordset. Makes sense to me.
The problem is, sometimes the ".Close" command between gets ignored,
and I get the error anyway.

It's not a matter of how I should construct the queries; these queries
are often very different, and setting up a loop to construct the
queries would be impractical, if not impossible - and again, that's not
the problem. Put in simple terms, the problem is this:

Bob: "Open a recordset based on query 1."
PC: "OKAY."
Bob: ".....ok, that's done, now close the recordset."
PC: "OKAY."
Bob: "And now open the same recordset using query 2."
PC: "I CAN'T. RECORDSET STILL OPEN."
Bob: "WT......?"

[salad]

bl*****@carolina.rr.com wrote:

I get an error when I consecutively call
.Open query1
.Open query2
citing that you can't open an open recordset. Makes sense to me.
The problem is, sometimes the ".Close" command between gets ignored,
and I get the error anyway.

It's not a matter of how I should construct the queries; these queries
are often very different, and setting up a loop to construct the
queries would be impractical, if not impossible - and again, that's not
the problem. Put in simple terms, the problem is this:

Bob: "Open a recordset based on query 1."
PC: "OKAY."
Bob: ".....ok, that's done, now close the recordset."
PC: "OKAY."
Bob: "And now open the same recordset using query 2."
PC: "I CAN'T. RECORDSET STILL OPEN."
Bob: "WT......?"

Instead of being tricky why not open/close in steps
Set rst = New ADODB.Recordset
With rst
.Open "Query1"
If .State = adStateOpen Then
.Close
End If
End With
set rst = Nothing
Set rst = New ADODB.Recordset
With rst
.Open "Query2"
If .State = adStateOpen Then
.Close
End If
End With
set rst = Nothing

[RoyVidar]

bl*****@carolina.rr.com wrote in message
<11**********************@i40g2000cwc.googlegroups .com> :

Hello helper people who are smarter than me:
I have a form that needs to submit multiple queries to different
tables during one Sub's execution. Some sections are as simple as:

1| With rst
2| .Open query1
3| .Close
4| .Open query2
5| End With

However, what I run into is that .Open and .Close don't always get
triggered, or maybe the state isn't set, but I do know I frequently
run into the error "Method not allowed when the object is open"
message at places like line 4.

I take the next step: run an empty loop that waits for the Recordset
to Close before going on:

1| With rst
2| .Open query1
3| .Close
4| Do While .State <> adStateClosed
5| Loop
6| .Open query2
7| End With

Problem is, I get stuck in an infinite loop, because the recordset
never closes, even though ".Close" was the last command read before
going into the loop?

So, what would cause .Close not to close?

Why are you opening recordsets when you don't do anything with them?

Have you hidden what you do with the recordsets, or are you opening
recordsets to execute action queries?

If the latter, consider trying something like this

With yourconnection
.execute "query1", , adcmdstoredproc + adexecutenorecords
.execute "query2", , adcmdstoredproc + adexecutenorecords
End With

--
Roy-Vidar

[blue875]

Found my issue: I was running action queries, but not running .Update
on the Recordset afterward. As soon as I entered that command,
everything ran fine. Odd that I had remembered that sometimes and
forgotten it at others. Guess that's what I get for being so darn
human. :)