[AccessD] Array as source for query

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