[AccessD] SQL against recordset

Hi Mark

To initiate an SQL Execute is slow. Running DAO on a recordset is very fast, indeed when you have only 3K records.
So open a recordset and apply the 10K filters - my guess is a 10 fold speed increase or more.

/gustav

>>> markamatte at hotmail.com 04-09-2007 16:12 >>>
This is the speed thing again.  I am running 10K SQL statements against a 3K 
row table.  I need it to be as fast as possible.  Currently my 10K 
statements are stored in a table.  I pull them in as a recordset...loop 
through...and execute each one.  The 10K are all looking at the same 
table...just different sets of criteria.  A friend suggested I pull the data 
into a recordset...and have each SQL statement reference the recorset...as 
an attempt to save time.  Maybe an array?

Any thoughts?

Thanks,

Mark A. Matte

P.S...Searching for 'stuff' like this...found an articly by Susan H. about 
arrays...but not quite what I needed.


>From: "Rocky Smolin at Beach Access Software" <rockysmolin at bchacc.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "'Access Developers discussion and problem 
>solving'"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] SQL against recordset
>Date: Mon, 3 Sep 2007 20:23:05 -0700
>
>Why do you want to do this.  Or, what are you trying to accomplish?  Why 
>not
>just open a second recordset?  The criteria are the same, yes?
>
>Rocky
>
>
>
>
>
>
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com 
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
>Sent: Monday, September 03, 2007 6:26 PM
>To: accessd at databaseadvisors.com 
>Subject: [AccessD] SQL against recordset
>
>Hello All,
>
>This might sound odd...but I want to use VBA to open a recordset...
>
>"Set rst1 = dbs.OpenRecordset("SELECT symbol from tblList...."
>
>then I want to open a new recordset by running an SQL statement against the
>first recordset.  I don't know if this is possible...and if so...what 
>syntax
>would I use to reference the recordset?
>
>"Set rst2 = dbs.OpenRecordset("SELECT symbol from rst1..."???????
>
>Any thoughts?
>
>Thanks,
>
>Mark A. Matte