[AccessD] Array as source for query

Yes, I also use a single combo based on a single QUERY, for things like
state, city and other entities where virtually the entire list table is
pulled.  I could move all of those to callbacks of course.

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: Tuesday, January 23, 2007 2:50 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Array as source for query

Arthur:  Where it fails for me is mostly when I have continuous forms with a
few combos on each.  My switchboard/navigation form has 3 subforms, each of
which shows 9 records, each of which has 4 combos, for a total of 108 combos
on a form that is always open.  Each of those subforms may have more than 9
records.  At this moment, the subforms contain 57 records for a total of 228
combos.  How many of these count as data connections is hidden from mere
developers but I bet there are a few...

Even worse are some of the reports.  I have chosen to take it easy on the
complexity of the report recordset and use combos to display many of the
related key records in a combo.  For example, an employee report query could
join to a city table and show fields from both tables in the query.  With
particularly complex queries the report is faster if you drop the join to
the city table and use a combo to display the city while the report query
only has the ID of the city.  The example here is trivial, but in a more
complex situation, it changes the opening time of some reports from several
minutes to 2 seconds.

I've been caching list and combo data in getrows() arrays for many years but
shifted away from that approach when we switched to a terminal server
environment where memory constraints became an increasing consideration.  
What to do, push the server to go to a swap file or hit the BE???  At one
time we had over 20 users on a gigabyte of RAM and these users are required
to keep piggy applications like Outlook open and concurrently use numerous
other applications.  It turns out, though, that an array appears to use no
more RAM than an open recordset so my solution is to cache more and more and
maybe force some RAM upgrades.

A typical bound form will display records before the entire recordset is
retrived and a getrows() requires a MoveLast to get a count so it is
potentially slower.  In practice, though, my parent forms are all single
record recordset forms and it is the rare subform that has more than 30
related records so the difference added by a getrows call is not discernable
by users.

John:  I assume your objection is in making callbacks fit your framework as
reusable components.  You state two different problems, a method of caching
data and using a single callback for more than one combo.  Getting back to
my trivial example, you can copy and paste a single 'City' combo into every
form and report you use.  A single public function can be referenced in the
property sheet and thereafter, you never need to include the City table in
any query that has a CityID foreign key.  It's not as elegant as a framework
solution but it sure gets the job done.  On my switchboard/navigation form,
it gets rid of 1/4 of the connections to data.  Two more callbacks for the
people involved as Quantity Surveyor and as Estimator (reused in at least 30
places), one for the bid status (reused in over a dozen reports and several
forms) and I get to dump a couple hundred combo recordsets and their
connections to the database, and each of the four combos is driven by a
single array populated by a single one time connect/release to the data.

If the data is in a globally accessible array, and you periodically check
for a single record table containing an add/edit/delete timestamp record for
the particular source table to ensure your data is unchanged (completely
unnecessary for bid status as there are 7 bid statuses and they haven't
changed in years) and people involved records as the data is static for
months on end, you need only ever connect to the data once and reconnect to
refresh the source data when it is flagged as having been changed.  In the
form where the records are entered, just insert a procedure in the before
update event to reset the time stamp pertaining to the particular table.  In
any event, the connection is only long enough to read the data into an array
and it is promply closed and it can be reused many thousands of times where,
using conventional means, it would have meant many thousands of times
pulling data, accessing a disk, locking, releasing memory.  Even my city
table only gets a couple new records a week these days, but I like to know
the data is current at the time the form opens.  I bet that in a single user
session, it may be accessed tens of thousands of times between updates in
the various forms and reports where it only had needed to be accessed and
stored once.

I don't know how well this can be incorporated into a framework, but I don't
mind writing a few additional callbacks once a month.

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





>From: artful at rogers.com
>
>That is very strange, JC. I have used your JIT code in numerous 
>situations (10 tabs, some of which contain sub-tabs) and have never 
>experienced a hiccup let alone a crash or other untoward activity. Your 
>JIT code has made me look elegant 100 times over. I don't have the 
>slightest clue why it would fail you now. It has worked for me in MDB and
ADP flawlessly.
>
>A.
>
>----- Original Message ----
>From: JWColby <jwcolby at colbyconsulting.com>
>To: Access Developers discussion and problem solving 
><accessd at databaseadvisors.com>
>Sent: Tuesday, January 23, 2007 11:54:29 AM
>Subject: Re: [AccessD] Array as source for query
>
>Jurgen,
>
>I too have bound forms so complex that I get this "no more database"
>message.  The surprising thing is that IIRC there are more than 2400 
>"available" "recordsets".  The issue is (as you have discovered) at 
>LEAST one is used for each combo, list, form etc.  It turns out that 
>each object can use two or more "recordsets" as well so it isn't like 
>you can have up to 2400 combos / forms at once.
>
>I too make heavy use of Just In Time subforms, on tabs, connecting and 
>disconnecting the subforms as the user clicks on and then off of tabs.
>That
>definitely helps but doesn't solve the problem.  If I can build into my 
>framework a method of caching the data for combos and then using a 
>callback that would definitely help.  Combos are about 90% of the 
>problem and this is an issue that really can cause problems as your 
>forms get complex.
>
>I tried to solve this a few years ago but the cure was as bad as the 
>problem.  Callback functions are odd ducks, and I was never able to 
>satisfactorily use a single callback for more than one combo.  It is an 
>issue I would LOVE to figure out though.
>
>John W. Colby
>Colby Consulting
>www.ColbyConsulting.com

_________________________________________________________________
Get in the mood for Valentine's Day. View photos, recipes and more on your
Live.com page. 
http://www.live.com/?addTemplate=ValentinesDay&ocid=T001MSN30A0701