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