[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