I have a program in which I'd like to reuse the same recordset name for 

different recordsets.  (i.e. I open the recordset for one Query then close 

it and reopen it with a different query) once I'm done with the first one

   I have a loop which will query a table with different people's names 

from a name table.  I've tried a rs.close before another rs.Open with a 

different query, but I keep getting an error saying, "Object doesn't 

support this property or method: 'Open' ".  What am I doing wrong? If 

anyone can help, I'd appreciate it very much.  I'd hate to have to declare 

separate recordsets for each person in the name database.



Chris

cc:



Repeated opening and closing of the same recordset is a very inefficient use

of server resources. You should try to improve your query to get all you

need in a single access, then manipulate in your program. As for the

specific error you're getting, more code is needed to evaluate properly

what's happening.



http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=154



hth

m.

________________

----- Original Message -----

From: "Christopher Cote" <chrscote@9...>

To: "Access ASP" <access_asp@p...>

Sent: Tuesday, October 02, 2001 2:12 AM

Subject: [access_asp] Reopening a recordset





> I have a program in which I'd like to reuse the same recordset name for

> different recordsets.  (i.e. I open the recordset for one Query then close

> it and reopen it with a different query) once I'm done with the first one

>    I have a loop which will query a table with different people's names

> from a name table.  I've tried a rs.close before another rs.Open with a

> different query, but I keep getting an error saying, "Object doesn't

> support this property or method: 'Open' ".  What am I doing wrong? If

> anyone can help, I'd appreciate it very much.  I'd hate to have to declare

> separate recordsets for each person in the name database.

>

> Chris

OK, here are some more specifics on what I have.  I am writing a baseball 

stat program to output each players stats for batting/pitching.  I have a 

table for the player names and 3 separate tables, one for each type of 

stat (seems reasonable). Now what I planned on doing was to simply to a 

getrows of the player names into an array.  Now here's where I got into a 

little bit of trouble.  I wanted to query, say, the batting recordset for 

each player and then sum each of the fields using a SQL Sum statement.  I 

know this is a lot of work on the server's part. 

  Is there a way I can use a filter instead of an SQL Statement, where I 

only have to get the data once and then disconnect from the server?  If 

so, how would I do this?



Chris

I forgot to mention that I want 

to do a sum of each player's stats.  Is there a way to filter out each 

player's stats in the batting table, say, and then sum them without having 

to do a separate Sum sql statement. If there is any way to do this besides 

simply looping through each record and adding each field's value, it would 

be nice.  If there was a way to sum all fields at one time and get a 

separate table with the sums, I'd love to know how.



Chris

OK, I've got the recordset filter working alright.  I don't know what I 

was doing wrong, but all I really care is that it's now working.  Now, the 

question I have is: Is there a way to find the sums of each player's stats 

without having to do a separate sum SQL statement?  The only way I can 

think of right now to sum each separate player's stats is by performing a 

SELECT SUM stat1 as Stat(1), SUM stat2 as Stat(2), etc.  for each time I 

filter the recordset.  I don't want to waste processor time by going 

through loops summing each field, especially since I have about 10-15 

numerical fields.  If anyone can think of an easier way to total a number 

of fields for each person, please let me know.  It would be of great help.



Chris