On Thu, 29 Mar 2012 09:11:26 -0500, "Neil"
<neil.ginsberg+(E-Mail Removed)> wrote:

>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.
>

'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".

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"Neil" <neil.ginsberg+(E-Mail Removed)> wrote in message
news:jl1qsd$ub9$(E-Mail Removed)...
> The system resources I'm referring to would only be in memory. There would
> be writing of data, so compact and repair is not an issue. I'm only
> discussing the memory resources that are used when a recordset is opened
> and reopened (snapshot type).
>
>
> "Access Developer" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> "Neil" <neil.ginsberg+(E-Mail Removed)> wrote
>>
>> > Very nice! Tell me: it's been running for 10 years -
>> > but does the database or Access itself need to be
>> > restarted once in a while? Seems to me that there
>> > would be memory issues with a continuously-running
>> > program, without restarting it periodically.
>>
>> I think I mentioned in an earlier post, the Jet and ACE database engines
>> do not return "system resources" that they have obtained until the DB is
>> closed and a Compact and Repair operation performed -- that it was not
>> designed for, and can't be used as, an "always-on 24/7/365" database
>> store as can many server databases (including MS SQL Server, Oracle,
>> MySQL, and others).
>>
>> Larry Linson, Microsoft Office Access MVP
>>
>
>

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



"Access Developer" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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".
>
> --
> Larry Linson
> Microsoft Office Access MVP
> Co-Author, Microsoft Access Small Business Solutions, Wiley 2010
>
> "Neil" <neil.ginsberg+(E-Mail Removed)> wrote in message
> news:jl1qsd$ub9$(E-Mail Removed)...
>> The system resources I'm referring to would only be in memory. There
>> would be writing of data, so compact and repair is not an issue. I'm only
>> discussing the memory resources that are used when a recordset is opened
>> and reopened (snapshot type).
>>
>>
>> "Access Developer" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> "Neil" <neil.ginsberg+(E-Mail Removed)> wrote
>>>
>>> > Very nice! Tell me: it's been running for 10 years -
>>> > but does the database or Access itself need to be
>>> > restarted once in a while? Seems to me that there
>>> > would be memory issues with a continuously-running
>>> > program, without restarting it periodically.
>>>
>>> I think I mentioned in an earlier post, the Jet and ACE database engines
>>> do not return "system resources" that they have obtained until the DB is
>>> closed and a Compact and Repair operation performed -- that it was not
>>> designed for, and can't be used as, an "always-on 24/7/365" database
>>> store as can many server databases (including MS SQL Server, Oracle,
>>> MySQL, and others).
>>>
>>> Larry Linson, Microsoft Office Access MVP
>>>
>>
>>
>
>

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.


"ralph" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Thu, 29 Mar 2012 09:11:26 -0500, "Neil"
> <neil.ginsberg+(E-Mail Removed)> wrote:
>
>>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.
>>
>
> '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

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>:

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"Neil" <neil.ginsberg+(E-Mail Removed)> wrote in message
news:jl37sg$qla$(E-Mail Removed)...
> 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
>
>
>
> "Access Developer" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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".
>>
>> --
>> Larry Linson
>> Microsoft Office Access MVP
>> Co-Author, Microsoft Access Small Business Solutions, Wiley 2010
>>
>> "Neil" <neil.ginsberg+(E-Mail Removed)> wrote in message
>> news:jl1qsd$ub9$(E-Mail Removed)...
>>> The system resources I'm referring to would only be in memory. There
>>> would be writing of data, so compact and repair is not an issue. I'm
>>> only discussing the memory resources that are used when a recordset is
>>> opened and reopened (snapshot type).
>>>
>>>
>>> "Access Developer" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> "Neil" <neil.ginsberg+(E-Mail Removed)> wrote
>>>>
>>>> > Very nice! Tell me: it's been running for 10 years -
>>>> > but does the database or Access itself need to be
>>>> > restarted once in a while? Seems to me that there
>>>> > would be memory issues with a continuously-running
>>>> > program, without restarting it periodically.
>>>>
>>>> I think I mentioned in an earlier post, the Jet and ACE database
>>>> engines do not return "system resources" that they have obtained until
>>>> the DB is closed and a Compact and Repair operation performed -- that
>>>> it was not designed for, and can't be used as, an "always-on 24/7/365"
>>>> database store as can many server databases (including MS SQL Server,
>>>> Oracle, MySQL, and others).
>>>>
>>>> Larry Linson, Microsoft Office Access MVP
>>>>
>>>
>>>
>>
>>
>
>

On Thu, 29 Mar 2012 19:30:36 -0500, "Access Developer"
<(E-Mail Removed)> wrote:

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