[AccessD] using ADO to populate combos - was RE: Error3048Cannotopen any more databases

Drew Wutka DWUTKA at Marlow.com
Thu May 15 10:18:48 CDT 2008


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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.





More information about the AccessD mailing list