Jurgen Welz
jwelz at hotmail.com
Tue Jan 23 14:05:03 CST 2007
Charlotte: I expect the problem would go away with any disconnected approach. I find text files are faster than XML. VBA is lightning fast with text file access. Getting from an mdb table to an array is simply a matter of opening a recordset, calling getrows to fill an array and then closing the recordset. That's all the bouncing needed. I don't really have much of a problem as it has been going away with the approach currently being implemented. It was mentioned as a justification for the concept of an array as a source for a query. I'm running Access 2003 at work but only have 2000 at home. I have yet to convert to ADO. Ciao Jürgen Welz Edmonton, Alberta jwelz at hotmail.com >From: "Charlotte Foust" <cfoust at infostatsystems.com> > >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 _________________________________________________________________ Your Space. Your Friends. Your Stories. Share your world with Windows Live Spaces. http://discoverspaces.live.com/?loc=en-CA