In most of the examples I have found while searching for ways to keep data with
multiple fields per record in memory a collection of classes is used. I
understand how these work and have used them in some of my macros. One thing
about collections that makes them somewhat less than satisfactory is the lack of
a way to retrieve the key that was used when the class was added to the
collection. I know I can get around this by having the key field in the class.

I have been experimenting with using ADODB record sets to manage in memory data
and found that they work well in all of the cases I have tried. Having all of
the functions supported by records sets available makes a number of things much
simpler than using collections. Functions like find, seek, bookmark and clone
are really useful.

Is there something about using "in memory" record sets that makes them a
bad choice when a collection of classes could be used instead? Are there large
performance differences between collections and records sets?

I haven't done any performance tests but plan to do some with the Perfmon tools
from the Professional Excel development book.

Thanks for your thoughts.

Mike Clemens
Clemens Consulting

it sounds liek you're using basic collections. Have you tried using the
Dictionary (in the MS Scripting Runtime DLL)?
These too are collections, but add much more functionality

eg mtDic.Exists("somekey") returns True/False
you can iterate through data AND keys.
recordsets offer some advantages in that you can filter them ... but you
definitely sacrifice speed.



"Mike Clemens" wrote:

In most of the examples I have found while searching for ways to keep data with
multiple fields per record in memory a collection of classes is used. I
understand how these work and have used them in some of my macros. One thing
about collections that makes them somewhat less than satisfactory is the lack of
a way to retrieve the key that was used when the class was added to the
collection. I know I can get around this by having the key field in the class.

I have been experimenting with using ADODB record sets to manage in memory data
and found that they work well in all of the cases I have tried. Having all of
the functions supported by records sets available makes a number of things much
simpler than using collections. Functions like find, seek, bookmark and clone
are really useful.

Is there something about using "in memory" record sets that makes them a
bad choice when a collection of classes could be used instead? Are there large
performance differences between collections and records sets?

I haven't done any performance tests but plan to do some with the Perfmon tools
from the Professional Excel development book.

Thanks for your thoughts.

Mike Clemens
Clemens Consulting