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

  • Thread starter Thread starter Neil
  • Start date Start date
The number of records (rows) in the lookup table will be very small.
Probably about 20 per client. Right now there's only one client that the
system will be used with. Potentially more later. So, either way, very, very
small numbers.

So performance really isn't an issue. I'm sure I wouldn't see any difference
in looking a lookup in an open recordset vs. an array. With just a few
hundred records at most, it'll be virtually instantaneous either way.

My main concern is the drain on resources with a perpetually-open recordset
vs. a recordset that is continually opened and closed vs. (new
consideration) a perpetual array. Which one would tax resources the least?
That's my main concern.
Click to expand...

'Access Developer' is actually answering most of your questions, but
it doesn't seem to be sticking. So perhaps it would be helpful if you
provided more details of your target domain - the clients and data
stacks involved - program and control flow, ..., then he and others
can better address specific issues with your design.

As for a "drain on resources", that is always possible with any client
application that continually opens and closes an object, and with any
object. One always runs the risk of eventual fragmentation or worse
accidently leaving a reference behind. Doesn't necessarily follow it
will occur, but it might.

Creating an Array of a fixed 'maximum' length and reusing it can
mitigate that issue.

-ralph
 
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".
 
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
 
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.
 
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>:
 
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".
Click to expand...

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
 
Back
Top