jwcolby
jwcolby at colbyconsulting.com
Thu May 15 22:52:02 CDT 2008
I am loading arrays and storing them in a collection. The biggest problem is that the number of trips through the function is just extreme, 4 or more for each record plus a handful for no apparent reason. Thus if you have a table of 10,500 cities with a PKID and city name column, that is 40 thousand trips through the function plus. This is the result for my state table: Ctl=cboState; ID=-1; Row=0; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=1; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=2; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=3; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=4; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=5; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=6; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=7; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=8; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=9; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=10; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=11; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=12; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=13; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=14; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=15; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=16; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=17; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=18; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=19; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=20; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=21; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=22; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=23; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=24; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=25; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=26; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=27; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=28; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=29; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=30; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=31; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=32; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=33; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=34; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=35; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=36; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=37; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=38; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=39; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=40; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=41; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=42; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=43; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=44; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=45; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=46; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=47; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=48; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=49; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=50; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=51; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=52; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=53; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=54; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=55; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=56; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=57; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=58; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=59; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=60; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=61; column=0; Code=6; Ctl=cboState; ID=-1; Row=-1; column=0; Code=7; Ctl=cboState; ID=-1; Row=61; column=1; Code=6; Ctl=cboState; ID=-1; Row=-1; column=1; Code=7; cboState: Trips: 126 Not good! For "small" lists it is quite serviceable though, the time spent calling the function being much less than the time to hit the table, plus it doesn't tie up connections to the be. I replaced about 15 or so combos using queries with combos using the callback and shaved about 20% of my form open time the second and subsequent times I opened the form. John W. Colby www.ColbyConsulting.com Drew Wutka wrote: > The speed is going to be dependent on your code. Can you post the > function you wrote? There's two basic ways to approach a recordset > based callback function. > > Method One: Keep the recordset open, and constantly refer back to it. > > Method Two: Load all of the data of the recordset into memory (using a > collection or an array). > > Method One may be slower, depending on how you are pulling the data, and > manipulating the recordset (to return the values to the function). > > Method Two should scream, if the data resides in memory. > > Drew > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: Thursday, May 15, 2008 9:15 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] using ADO to populate combos - was RE: > Error3048Cannotopen any more databases > > Drew, > > I finally got around to tweaking a callback widget I created long ago > while trying to get a generic callback that could be used by any combo. > > The method uses a class which holds the actual callback method as well > as a collection to hold arrays loaded from queries. Basically I set up > a query names qSomeCboName, qSomeOtherCboName etc. This allows me to > use the combo name from inside of the callback function to get at the > data required for that combo. Thus my rowsource can be cboCallBack() > for any combo which has a stored query named in the proper manner. > > It works. It is not speedy. In fact it is slow. For small lists like > states it is quite acceptable, a very slight hesitation as you pull down > > the list or type in the name of the list item and have the combo > "follow" your typing. > > On large lists, for example a city table with 10555 cities, it locks up > the form for minutes as it opens. It also locks up the form for a long > period as you type in something like Racoon (an actual city name in the > table!). In fact performance gets marginally better the second time you > > do such a "search" through the combo, apparently due to caching in > memory? > > But in general it is unusable for "large" lists. I am going to have to > do some research for what "large" is. BTW, the same table loaded > directly into a combo using the query itself runs at a quite acceptable > speed, barely noticible hesitation as the combo "finds" racoon. > > I assume the difference is interpreted VBA callback vs optimized C++ or > even assembler callback "behind" the combo using the recordset. > > This still leaves plenty of opportunity to use the callback though since > > I have dozens of combos which use small lists. Getting these converted > to use a callback MIGHT make a significant difference in some of my > complex form load times. We shall see. > > I just thought you might be interested to know that I at least got it > working. > > John W. Colby > www.ColbyConsulting.com > > > Drew Wutka wrote: >> That's not necessary, to write a function for each control. One of > the >> arguments of that control is the control itself. >> >> So you could create one function, in a module, that would be your >> 'global' callback function. What I would recommend would be to create > a >> class that would handle recordset objects, and in your callback > function >> pull the appropriate recordset from that class based on the control >> argument that is passed to the callback function. >> >> Drew >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby >> Sent: Wednesday, March 05, 2008 9:17 AM >> To: 'Access Developers discussion and problem solving' >> Subject: Re: [AccessD] using ADO to populate combos - was RE: >> Error3048Cannotopen any more databases >> >> Long ago I tried using call back functions and the only way I could >> figure >> out to do it was write a callback for each object. That just doesn't >> work >> when you want to implement this easily and automatically system wide - >> five >> hundred different combos. >> >> 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 Drew Wutka >> Sent: Wednesday, March 05, 2008 10:06 AM >> To: Access Developers discussion and problem solving >> Subject: Re: [AccessD] using ADO to populate combos - was RE: Error >> 3048Cannotopen any more databases >> >> Use a callback function. >> >> Drew >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby >> Sent: Wednesday, March 05, 2008 8:23 AM >> To: 'Access Developers discussion and problem solving' >> Subject: [AccessD] using ADO to populate combos - was RE: Error 3048 >> Cannotopen any more databases >> >> I was under the perhaps naive belief that with Access 2000 and > greater, >> a >> combo could be populated by an ADO recordset, i.e. it's rowsource >> property >> could be an ado recordset. When I go out and look, I am running into >> methods that build up a string and the row source type is set to value >> list. >> That is a rather useless solution in the majority of cases since the > max >> limit of characters in the list is around 2000. >> >> Is it in fact possible to make the rowsource an ADO recordset or am I >> delusional? If possible, how is it done? Example code would be nice. >> >> John W. Colby >> Colby Consulting >> www.ColbyConsulting.com