[AccessD] SQL against recordset

Charlotte Foust cfoust at infostatsystems.com
Tue Sep 4 10:37:43 CDT 2007


Same as you would in any criteria string:  "Country = 'France' Or Country = 'England'"

Charlotte Foust 

-----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 8:32 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] SQL against recordset

Thanks again,

With:
>   strFilter = "Country = 'France'"

is there syntax to add multiple fields and criteria to this property?

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

_________________________________________________________________
Kick back and relax with hot games and cool activities at the Messenger Café. http://www.cafemessenger.com?ocid=TXT_TAGHM_SeptHMtagline1





More information about the AccessD mailing list