[AccessD] Array as source for query

Charlotte Foust cfoust at infostatsystems.com
Tue Jan 23 12:02:02 CST 2007


Jurgen,

Does the restriction still cause problems if you use ADO disconnected recordsets?  They could be written to and populated from an XML file as well.

I haven't worked much in Access with them since version 2000 because my shop insisted on DAO, but ADO.Net is so flexible, that I would suspect the answer lies in ADO with Access.  I know I have an old demo that uses ADO to bounce between an array and a recordset in memory and then writes it to an XML file.

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz
Sent: Tuesday, January 23, 2007 8: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





>From: "Lembit Soobik" <lembit.dbamail at t-online.de>
>
>LOL,
>while I did understand Jürgen's essays, half way through I always 
>thought gosh, glad that I do not NEED to do that ;-)
>
>and: Welcome back, Jürgen, glad to see you here again
>
>Lembit
>
>----- Original Message -----
>From: "William Hindman" <wdhindman at dejpolsystems.com>
>To: "Access Developers discussion and problem solving"
><accessd at databaseadvisors.com>
>Sent: Tuesday, January 23, 2007 1:09 PM
>Subject: Re: [AccessD] Array as source for query
>
>
> > ...I'll second that emotion ...in dire need of a Jurgen translator 
> > ...I still have code of his from several years ago that in moments 
> > of quiet desperation, I stare at in hopes that I've finally learned 
> > enough to understand what he was going on about ...alas, tis not 
> > meant to be :)
> >
> > William Hindman
> >
> > ----- Original Message -----
> > From: "Martin Reid" <mwp.reid at qub.ac.uk>
> > To: "Access Developers discussion and problem solving"
> > <accessd at databaseadvisors.com>
> > Sent: Monday, January 22, 2007 2:08 PM
> > Subject: Re: [AccessD] Array as source for query
> >
> >
> > Still dont understand your posts Jurgen but nice to see you back.
> >
> > Martin
> >
> > Martin WP Reid
> > Training and Assessment Unit
> > Riddle Hall
> > Belfast
> >
> > tel: 02890 974477

_________________________________________________________________
Free Alerts : Be smart - let your information find you ! 
http://alerts.live.com/Alerts/Default.aspx




More information about the AccessD mailing list