[AccessD] Array as source for query

JWColby jwcolby at colbyconsulting.com
Tue Jan 23 12:54:10 CST 2007


It is not the JIT code failing, it does indeed work flawlessly.  

What is going on is that the DIS application is a tremendously complex
application.  In order to accomplish what the client wanted, to allow them
to see at a glance what they wanted to see, I had to literally build an
updateable query spanning about 6 parent/child/grandchild/etc tables.  Then
I built a form which contains about two DOZEN tabs to allow them to get at
other pieces of the main "claim" process.  The result is a form which had an
unknown quantity of queries being pulled at one time to display and allow
them to edit this thing.  Then... There is my framework also using
recordsets for it's own purposes, other "popup" forms that may be opened, an
entire rather complex Mail Merge application etc.

It all adds up to tons of "recordsets" used.

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 artful at rogers.com
Sent: Tuesday, January 23, 2007 12:15 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Array as source for query

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

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz
Sent: Tuesday, January 23, 2007 11:39 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Array as source for query

Lembit:

I appreciate all the welcomes.

Peculiar thing is, I still 'NEED to to that'.  That application I wrote that
the 'NEED' pertains to is still in service and is currently up to 47 users.

Complexity has been added to the extent that if users leave too many forms
open, they frequently get a 'Cannot open anymore databases.' 3048 error (an
FE error) and it seems once that happens, even bound forms don't save data
until after a restart.  That application remains, in IT parlance 'just a
file' so it remains my responsibility, and there is no possiblity of
migrating it to an industrial back end.

The only way to reduce connections to data is to disconnect lists and combos
and just in time subforms from directly bound sources.  I have a few
particularly nasty reports that open from equally nasty forms that tend to
put the application over the top.

The only solution that comes to hand is to connect to data, throw the data
into arrays, close the connection and display the information as callback
lists and combos.  Bound (but not updateable) subforms can be done using the
virtual recordset approach provided the number of records is limited and
field length is restricted to a known maximum so as not to exceed the SQL
string length limitation.  And of course, although they are bound forms,
they are not bound to actual records in a real table.  Still, the other
Access bound bells and whistles are present.

We had a list debate here about allowing text fields all to be 255
characters rather than placing restrictions on them, but if a subform can
allow display of 6 records, it is useful to impose a maximum field size to
guarantee all data in those records can be displayed.  Drew argued
vehemently for unrestricted size and he made a good case, but I'm now glad
that I kept them since there is no need to 'fix' the data to fit new
requirements.

There is much 'display only' data to be disconnected.  It appears the
application is destined to continue for another 4 or 5 years until,
hopefully, the parent company developers start meeting some of our specific
needs.  Access development has, for me, slipped from close to primary to
tertiary scope in my employment so I can dabble with all this when I feel
the desire.  For the time being, limiting the number of forms allowed to be
open was a quick patch.  Hopefully I can disconnect all the 'display only' 
data.  After that, I may have to implement a custom record locking scheme
and go unbound.  It is anticipated that the number of users will continue to
climb exponentially from the 3 users we started with in 1997.

Oh yes, I 'NEED' to pursue this kind of amusement more than ever.  
Fortunately, as long as things continue to work reasonably well, it is a
matter of dabbling when I feel like it.  This task is occasional weekend
entertainment.

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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list