[AccessD] Array as source for query

I have used arrays to populate small queries from time to time.  I looped 
through the array generating an SQL string and used any table name in the 
database.  Any system usys... or other conventional table, whether it had 
records in it or not sufficed.  The string generated was along the lines:

"Select " & ar(0) & " as A From usysTable Union Select " & ar(1) & " as A 
>From usysTable Union Select " & ar(2) ....

The loop iterated Array as with an incrementing index counter.  Typically 
the loop would include the 'Union' in each loop and parse out the initial 
'Union' with a Mid$(strsql, 6) when it exited.  You have to watch Query 
string length limitations with this approach.  Sure you need a table, but 
any of the built in system tables works, though you can define a table with 
a short name and lose the alias to maximise the number of array 'records'.

Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com





>From: "Gustav Brock" <Gustav at cactus.dk>
>
>Hi Drew
>
>Yes, it can be done - and you have proved it - but still you need a table 
>with as many records as expected, which means that either this table must 
>have "enough" (= more than you would ever expect) records or you will have 
>to expand it dynamically which is the method you chose.
>
>I wouldn't say I would never use a method like yours but - as you have to 
>take care of at least one table anyway - I would feel tempted to fill a 
>temp table with all info needed to drive the report. As for the bloating, 
>remember that you quite easily can create also a temp database to hold the 
>temp table. Or - which I have done a couple of times - have a master 
>database with your tables. Before you initiate a run with your temp 
>table(s), copy the master database to overwrite the working copy of the 
>temp database.
>
>/gustav
>
> >>> DWUTKA at marlow.com 19-01-2007 20:38 >>>
>I'm not sure.  I've written a 'code based' query before, for something that
>was WAY too complex to actually create in a query.  I needed a report that
>built it's data from classes in a collection.  To accomplish this, I built 
>a
>table with one field, that contained numbers 1 thru....  To 'produce' the
>records, the query had the 'number' table in the FROM clause, with it's
>number field set in the where with a function that returned the number of
>objects in the collection. (It also checked to see if there were enough
>numbers in the table, if it needed more, it added them).  Then each field
>was a function that sent the collection class the field it wanted, and what
>the 'index' which was coming from the number field.
>
>Worked like a charm, but it's not something you would use in many
>situations.  That particular situation involved a pretty complex import
>process which imported the data into a collection class.  Individual 
>records
>(individual classes) that didn't pass muster (and make it into the data
>table) were dumped to a failed collection.  Instead of dumping the failed
>collection into a table, they just wanted a report of the records that
>failed, and since it was a frequent process, dumping and deleting those
>records would bloat the database, so, instead, I just kicked out a report
>using the method above to populate it.
>
>Drew
>
>-----Original Message-----
>From: Gustav Brock [mailto:Gustav at cactus.dk]
>Sent: Friday, January 19, 2007 12:40 PM
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] Array as source for query
>
>Hi Drew
>
>That might be doable, but wouldn't it be faster/simpler to write the array
>to a temp table and then use that as the source in the query?
>
>/gustav
>
> >>> DWUTKA at marlow.com 19-01-2007 16:12:22 >>>
>Not by itself, but you can feed it into a query using a custom function, 
>but
>you still need an actual table as the recordsource.
>
>Drew
>
>-----Original Message-----
>From: Kaup, Chester [mailto:Chester_Kaup at kindermorgan.com]
>Sent: Thursday, January 18, 2007 9:56 AM
>To: Access Developers discussion and problem solving
>Subject: [AccessD] Array as source for query
>
>I think I know the answer but in VBA can an array be used as the source
>data for a query instead of a table? If so how? Thanks.
>
>
>
>Chester Kaup
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Don’t waste time standing in line—try shopping online. Visit Sympatico / MSN 
Shopping today! http://shopping.sympatico.msn.ca