[AccessD] SQL against recordset

Mark - something like this might work . . .

Dim stg1 as String
Dim rst1 as DAO.Recordset
Dim rstCriteria as DAO.Recordset
Dim rstDuplicate as DAO.Recordset

'-- Set original recordset
Stg1 = "SELECT * FROM tbl3K"
Set rst1 = dbengine(0)(0).Openrecordset(stg1,dbopensnapshot)
Set rstDuplicate = rst1

Do while rst1.eof = false
	'-- Now start working your 10K criteria list
	rstDuplicate.filter = rst1("Criteria")
	set rstCriteria = rstDuplicate.OpenRecordset
	Do while rstCriteria.eof = false
		'-- take actions
		rstCriteria.movenext
	Loop
	Rst1.movenext
Loop

'-- Close recordsets


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Tuesday, September 04, 2007 9:48 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] SQL against recordset

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
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Get a FREE small business Web site and more from Microsoft. Office Live! 
http://clk.atdmt.com/MRT/go/aub0930003811mrt/direct/01/