JWColby
jwcolby at colbyconsulting.com
Sun Jan 21 21:19:55 CST 2007
Well hello Jurgen. Long time no see, welcome back. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz Sent: Sunday, January 21, 2007 7:50 PM To: accessd at databaseadvisors.com Subject: Re: [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 _________________________________________________________________ Dont waste time standing in linetry shopping online. Visit Sympatico / MSN Shopping today! http://shopping.sympatico.msn.ca