473,903 Members | 3,244 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Recordset multiple open and close

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?

Apr 19 '06 #1
6 6839
bl*****@carolin a.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.
Apr 19 '06 #2
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 RunMultipleQuer ies()
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 = '" & txtSomethingEls e & "'")

Set DB = CurrentDB
For i = 0 to Ubound(arrQ)
Set RS = DB.OpenRecordse t(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 RunMultipleQuer ies(strSomethin g As String, strSomethingEls e As
String)
...
End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Apr 19 '06 #3
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......?"

Apr 20 '06 #4
bl*****@carolin a.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
Apr 20 '06 #5
bl*****@carolin a.rr.com wrote in message
<11************ **********@i40g 2000cwc.googleg roups.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 + adexecutenoreco rds
.execute "query2", , adcmdstoredproc + adexecutenoreco rds
End With

--
Roy-Vidar
Apr 20 '06 #6
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. :)

May 2 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

23
3266
by: Rob Meade | last post by:
Lo all, Ok - this is what I was aiming to do, and then I thought - naahhh, that cant be right! query database results to recordset results to array using GetRows update values in one column in array <BOING>
8
2310
by: dmiller23462 | last post by:
My brain is nuked....Can anybody tell me right off the bat what is wrong with this code? Along with any glaring errors, please let me know the syntax to display a message (Response.Write would be fine I think) that will say "I'm sorry but the data you requested cannot be found" or something along those lines.... This code is on an archive page I have on my company's intranet....The end result is to show 3 records at a time pulled from an...
1
2351
by: berlinbrown | last post by:
What is the best way to open run a query and then open a recordset, for example. Set rst = exDatabase.OpenRecordset(sql) ' Run Query .... .... ....
3
35507
by: alex_peri | last post by:
Hello All, I am having problems with sorting a recordset by fields in Access. I have a table with three columns called ID, SNo and Time and would like to sort the records by Time. I would like to sort them in the actual database and not just Order them with SQL. I have included the code I wrote below. Please bear in mind that I am very new to Access and SQL. I used the Helpfile of Access for this code but i can't make it work for me....
2
2071
by: Corey | last post by:
I am missing something here. I have a pop up form (loads from the "main form")that displays multiple command buttons. When a user selects a particular button, the recordset from the main form should be updated. Here is the code that I have written but it doesnt work. I get too few parameters on rs1. Can anyone shed some light on what I am missing? I appreciate help in advance-Corey Private Sub APPRVD_Click()
13
3493
by: Jan | last post by:
Hi I have a database that I use to keep track of the sales promotions that we send to companies. I normally send a mailing based on a subset of the companies in the database (found using the query: QryICTMassDistribution3) , I then use a form and the code below to create a new record in the corrispondence table to show what corrispondence has been sent to various companies.
18
6157
by: Darryl Kerkeslager | last post by:
When I open an ADO Recordset, I close it. However, it seems that there may be some difference in this manner of opening a Recordset: Dim rL As ADODB.Recordset Set rL = New ADODB.Recordset src = "SELECT Count(*) FROM reviewer INNER JOIN pp_officer " & _ "ON reviewer.reviewer_id = pp_officer.ppo_rev_id " & _ "WHERE rev_login = 'EllisonL'" Set rL = CurrentProject.Connection.Execute(src, , adCmdText)
36
4501
by: kjvt | last post by:
Based on a prior posting, I've written a function to convert a recordset to a dataview. The first call to the function for a given recordset works perfectly, but the second call always returns a dataview with a count = 0. Can someone explain why and how I might work around this problem? Here is the code for my function: Public Shared Function GetViewFromRS(ByVal pRS As ADODB.Recordset) _ As DataView
23
52774
by: PW | last post by:
Hi, I'd like to close a recordset and set the database to nothing if a recordset is open if an error has occured. Leaving a recordset open and a database open isn't a good idea, right? Thanks, -paul w
0
11282
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10873
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10983
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10500
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9684
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7206
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6088
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4726
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3324
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.