[AccessD] SQL against recordset

Hi Mark

The on-line help has an example:

  Set dbs = CurrentDb
  Set rstOrders = dbs.OpenRecordset("Orders", dbOpenDynaset)
  ' First filter.
  strFilter = "Country = 'France'"
  rstOrders.Filter = strFilter
  Set rstFiltered = rstOrders.OpenRecordset
  ' Do something ...
  '
  ' Next filter.
  strFilter = "Country = 'Italy'"
  rstOrders.Filter = strFilter
  Set rstFiltered = rstOrders.OpenRecordset
  ' Do something ...
  '
  ' Clean up.
  rstFiltered.Close
  rstOrders.Close
  Set dbs = Nothing

/gustav


>>> markamatte at hotmail.com 04-09-2007 16:47 >>>
Thanks Gustav,

...but that is where I am confused">So open a recordset and apply the 10K 
filters "\

After I open the recordset...how do I "apply" the 10K filters?

When I pull in the 3K records...an example of the 10K SQL...all records 
where (FieldX between 1 and 25) and FieldY >.15

How do I get this criteria to reference the recordset?

Thanks,

Mark


>From: "Gustav Brock" <Gustav at cactus.dk>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: <accessd at databaseadvisors.com>
>Subject: Re: [AccessD] SQL against recordset
>Date: Tue, 04 Sep 2007 16:30:10 +0200
>
>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