[AccessD] Array as source for query

Jurgen Welz jwelz at hotmail.com
Tue Jan 23 13:49:32 CST 2007


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




More information about the AccessD mailing list