[AccessD] Array as source for query

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