A theoretical question: how best to manage perpetual recordset needs?

[Access Developer]

Access obtains and uses system resources, internally, to perform its
functionality -- just running queries, opening and closing recordsets,
declaring objects, etc. That includes just-plain-memory, but other
resources that use memory, so sooner or later, the memory tied up from using
those will have to be returned to the system, too.

Would you be faced with regenerating the whole array (including the same
values you created earlier) each time you restarted? If the array is only
for current working data, it's a more "likely" useful approach.

I'm not a "fan" of ADO... it seems to be "effectively deprecated" and just
kept around for compatibility; the classic ADO as used in Access has been
replaced in what Microsoft considers "real development" (the DotNet world)
by ADO.NET which shared little more with classic ADO than the letters "ADO"
in the name; so I would be reluctant to pursue "disconnected recordsets" as
a solution. As hard as they hyped it, and despite some very vocal adherents,
the ADP was just not a "sell" to the Access developer community, and that
was the "real element" in which ADO was a "star".

 

[Neil]

Thanks, Larry. Yeah, I agree about ADO.

And regarding this whole thing, it could be that I'm overworrying. I just
had an issue with repeatedly opening and closing recordsets on a previous
system, so thought I'd throw this out. But we're talking about a lookup
table of a few hundred records at most, accessed maybe a 100 times an hour.
So it could be that the memory impact will be minimal.

OTOH, if repeatedly opening and closing the snapshot recordset would be more
resource-intensive than just keeping it open (or writing to an array), then
that would certainly be a consideration.

But it could be, at the end of the day, there may not be much to worry about
anyway.

Thanks,

Neil

 

[Neil]

Not sure what you mean by that it "isn't sticking."

But, in any case, I think the feedback that's been provided has been
helpful.

Thanks.

 

[Access Developer]

I suspect only testing will reveal "what is best", but, in many cases with
Access the difference turns out to not have been worth all the testing
necessary to determine it.

I've long since stopped worrying about any VBA that is not in a huge loop...
which would be very rare for anyone to have done in databases I've worked on
(usually indicates a novice designed the approach being used).

If you've worked with Access long enough to have a feel for "The Access
Way", just follow your instincts and you're not likely to "wander far from
the true path of enlightenment". <GRIN>:

 

[ralph]

I'm not a "fan" of ADO... it seems to be "effectively deprecated" and just
kept around for compatibility; the classic ADO as used in Access has been
replaced in what Microsoft considers "real development" (the DotNet world)
by ADO.NET which shared little more with classic ADO than the letters "ADO"
in the name; so I would be reluctant to pursue "disconnected recordsets" as
a solution. As hard as they hyped it, and despite some very vocal adherents,
the ADP was just not a "sell" to the Access developer community, and that
was the "real element" in which ADO was a "star".

All database technologies exist as part of a 'data stack'.
Data Client ->
Data Access Library ->
Data Drivers/Providers ->
Data Source (file/server, local/remote)

When it comes to Pro's and Con's, no discussion of any particular
technology is useful or perhaps even meaningful outside of or isolated
from the data stack in which it is to be employed.

One can point out that ADO is "effectively deprecated" (although it is
more that COM has been deprecated by the .Net Framework platform).
However, if using the MS Access client with an external data source
ADO is often a more viable alternative than DAO. Also as noted ADO is
the only data access library available for ADP.

To dismiss ADO out-right without knowing the full particulars of the
OP's problem domain, or because ADP is not popular, is not
particularly helpful.

[DAO was once "effectively deprecated". Microsoft has since reversed
itself and now recommends DAO for some solutions.]

-ralph