PC Review


Reply
Thread Tools Rate Thread

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

 
 
Neil
Guest
Posts: n/a
 
      24th Mar 2012
I'm looking for some theoretical advice about the best way to manage
resources with a recordset situation I have.

The database in question is going to run on a server and receive inputs from
another device. No user input. It, theoretically, will run for days or weeks
without being closed.

In one part of the code, after a record is written to a particular table, my
code has to look up an ID value in the Devices table, based on the input
record's Client ID and Device Number. Client ID and Device Number are unique
within a Device record, so only one ID value from the Devices table will
match those.

The question is: how best to do the lookups to minimize resource usage,
since the database will need to be running continuously for extended periods
of time. Some ideas:

1) Have a global recordset set to the Devices table, and do a FindFirst each
time a lookup is needed, never closing the recordset.

2) Have a global recordset based on Devices, with a Where clause set to
Client ID and Device Number (so that the recordset only returns one record).
Leave the recordset variable open at all times, but reset it each time to
whatever the current Client ID and Device Number are.

3) Same as #2, only use a local recordset variable which goes out of scope
each time, and is recreated each time a lookup is needed (being sure to
close the recordset and set it to Nothing before exiting the procedure).

Now, #3 might seem like the logical choice, since it opens a tiny
(one-record) recordset, and then goes away at the end of the proc, freeing
up those resources.

However, in the past where I've done that, opening up multiple one-record
recordsets, the program eventually ran out of system resources. In that
case, though, it wasn't with a recordset going out of scope. It was more
with a loop, where the same recordset variable was set to a different
one-record recordset with each iteration of the loop. After a few thousand
iterations, Access gave an error.

Interestingly, clearing the error allowed the loop to continue, so it may
have been due to the fact that these iterations were immediately after each
other, and Access didn't have a chance to "reset" whatever needed to be
reset? In the current situation, there won't be a loop, and the resetting of
the recordset variables will happen at least seconds, if not minutes or even
hours, apart. So it's a different situation.

So that is what I'm wondering. Given a perpetually running database, what's
the best way to manage a recordset that needs to continually be used to look
up a single record value? (I hope someone doesn't say, "Just use DLookup!"
;-) )

Thanks for any advice.

Neil


 
Reply With Quote
 
 
 
 
Access Developer
Guest
Posts: n/a
 
      25th Mar 2012
If you are talking about an "Access" database (Jet or the newer ACE), those
are file-server databases, which recover "released disk space" and certain
other system resources only when you do a Compact and Repair operation *...
they are not designed nor intended for 24/7/365 operation as you describe.
So, any suggestions you receive, including mine will just be for "making the
best of a bad situation".

* that is why, in the previous experience you
described, you encountered running out of
system resources

Often, you'll see some of us here "defending" Jet/ACE databases against
those who contend they are useless. But, if you read carefully, you'll see
that we suggest "appropriate tools for the environment", not "use Jet/ACE
whenever and wherever you have need for a database engine".

What do you mean by "run on a server and receive inputs from another
device"? And, indeed, are you asking specifically about Jet or ACE
databases? How will the database "receive inputs from another device"?

For the database to detect "when a record is written to a specific table",
unless it is your code doing the writing, the database will have to support
a feature called "triggers" -- Jet does not support triggers, and they are
new in ACE, but, even so, I have not read of many, if any, "issues" with
them.

I'm guessing... just guessing from what you wrote... that either the
database application is still to be defined, or the database engine is not
yet selected. If so, then a server database would be a better choice than
either Jet or ACE. You do not describe what the purpose of the data will
be... surely it's not a "write-once, read never" thing -- that would not be
useful, unless to perhaps meet arbitrary requirements for saving data that
do not specify the saved data will ever be retrieved/used.

If you can clarify some of the points I mention here, you will vastly
improve the probability that someone can either offer useful suggestions or
direct you to a better source of information.

Because of the way Access, Jet, and ACE operate, to avoid the potential of
DB corruption, I avoid having recordsets "just sitting around open"... I
make use of recordsets as compact and contained as possible, often (and that
would certainly be the case where you do not know when, or how often,
activity will be needed), I open the Table/Query, read/write the Record, and
then immediately close the Table/Query to force the update to take place.
That would eliminate from my consideration an "always-open" Recordset as you
describe... having correct (uncorrupted) data is essential; performance is
worthless if the data is not correct.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access



 
Reply With Quote
 
 
 
 
Neil
Guest
Posts: n/a
 
      26th Mar 2012
Hi, Larry.

> What do you mean by "run on a server and receive inputs from another
> device"? And, indeed, are you asking specifically about Jet or ACE
> databases? How will the database "receive inputs from another device"?


It's a 2010 ACCDB file. There is Windows-based software called KEPServer
which will receive data from external devices via the Internet, and then
will write it to a database of the user's choosing. The KEPServer will be
writing the values to my ACCDB file, one record at a time.

> For the database to detect "when a record is written to a specific table",
> unless it is your code doing the writing, the database will have to
> support a feature called "triggers" -- Jet does not support triggers, and
> they are new in ACE, but, even so, I have not read of many, if any,
> "issues" with them.


Yes, will be using the ACCDB AfterInsert Data Macro.

> I'm guessing... just guessing from what you wrote... that either the
> database application is still to be defined, or the database engine is not
> yet selected. If so, then a server database would be a better choice than
> either Jet or ACE. You do not describe what the purpose of the data will
> be... surely it's not a "write-once, read never" thing -- that would not
> be useful, unless to perhaps meet arbitrary requirements for saving data
> that do not specify the saved data will ever be retrieved/used.


Once the data is written to the raw data table by KEPServer, the AfterInsert
macro will copy the record to another table in normalized format. Here is
where the lookup is needed, since it needs to look up the device ID based on
the information passed to it by KEPServer. The routine will also perform
calculations and write them to a seperate table. These two tables (the
normalized data and the separate calculation table) will be used for
reporting purposes only. No editing of the data. So it's a write-once,
read-many thing. (The only data the users will be editing are the lookup
tables, client lists, etc.)


> If you can clarify some of the points I mention here, you will vastly
> improve the probability that someone can either offer useful suggestions
> or direct you to a better source of information.


Hopefully the above notes clarify. Sorry for not specifying the database
format originally. Should have. If you need more info, let me know.


> Because of the way Access, Jet, and ACE operate, to avoid the potential of
> DB corruption, I avoid having recordsets "just sitting around open"... I
> make use of recordsets as compact and contained as possible, often (and
> that would certainly be the case where you do not know when, or how often,
> activity will be needed), I open the Table/Query, read/write the Record,
> and then immediately close the Table/Query to force the update to take
> place. That would eliminate from my consideration an "always-open"
> Recordset as you describe... having correct (uncorrupted) data is
> essential; performance is worthless if the data is not correct.


You make an excellent point. And I may just do it that way for that reason
alone. However, I do want to point out that the recordset I was talking
about leaving open continuously would be a snapshot recordset of a lookup
table, used only for looking up values. No editing or writing to the
recordset would be performed. So it's a slightly different situation than
what you describe. Still, you make a valid point about corruption.

Thanks, Larry!

Neil


 
Reply With Quote
 
ralph
Guest
Posts: n/a
 
      26th Mar 2012
On Mon, 26 Mar 2012 06:48:00 -0500, "Neil"
<(E-Mail Removed)> wrote:

>Hi, Larry.
>
>> What do you mean by "run on a server and receive inputs from another
>> device"? And, indeed, are you asking specifically about Jet or ACE
>> databases? How will the database "receive inputs from another device"?

>
>It's a 2010 ACCDB file. There is Windows-based software called KEPServer
>which will receive data from external devices via the Internet, and then
>will write it to a database of the user's choosing. The KEPServer will be
>writing the values to my ACCDB file, one record at a time.
>
>> For the database to detect "when a record is written to a specific table",
>> unless it is your code doing the writing, the database will have to
>> support a feature called "triggers" -- Jet does not support triggers, and
>> they are new in ACE, but, even so, I have not read of many, if any,
>> "issues" with them.

>
>Yes, will be using the ACCDB AfterInsert Data Macro.
>
>> I'm guessing... just guessing from what you wrote... that either the
>> database application is still to be defined, or the database engine is not
>> yet selected. If so, then a server database would be a better choice than
>> either Jet or ACE. You do not describe what the purpose of the data will
>> be... surely it's not a "write-once, read never" thing -- that would not
>> be useful, unless to perhaps meet arbitrary requirements for saving data
>> that do not specify the saved data will ever be retrieved/used.

>
>Once the data is written to the raw data table by KEPServer, the AfterInsert
>macro will copy the record to another table in normalized format. Here is
>where the lookup is needed, since it needs to look up the device ID based on
>the information passed to it by KEPServer. The routine will also perform
>calculations and write them to a seperate table. These two tables (the
>normalized data and the separate calculation table) will be used for
>reporting purposes only. No editing of the data. So it's a write-once,
>read-many thing. (The only data the users will be editing are the lookup
>tables, client lists, etc.)
>
>
>> If you can clarify some of the points I mention here, you will vastly
>> improve the probability that someone can either offer useful suggestions
>> or direct you to a better source of information.

>
>Hopefully the above notes clarify. Sorry for not specifying the database
>format originally. Should have. If you need more info, let me know.
>
>
>> Because of the way Access, Jet, and ACE operate, to avoid the potential of
>> DB corruption, I avoid having recordsets "just sitting around open"... I
>> make use of recordsets as compact and contained as possible, often (and
>> that would certainly be the case where you do not know when, or how often,
>> activity will be needed), I open the Table/Query, read/write the Record,
>> and then immediately close the Table/Query to force the update to take
>> place. That would eliminate from my consideration an "always-open"
>> Recordset as you describe... having correct (uncorrupted) data is
>> essential; performance is worthless if the data is not correct.

>
>You make an excellent point. And I may just do it that way for that reason
>alone. However, I do want to point out that the recordset I was talking
>about leaving open continuously would be a snapshot recordset of a lookup
>table, used only for looking up values. No editing or writing to the
>recordset would be performed. So it's a slightly different situation than
>what you describe. Still, you make a valid point about corruption.
>


For pure static views with long life-times and presenting a minimal
impact on an active database file or server, you might checkout using
a "disconnected Recordset". Plenty of examples on the web.

-ralph
 
Reply With Quote
 
agiamb
Guest
Posts: n/a
 
      26th Mar 2012
If the lookup set is rarely updated, another option (also depending on
number of rows), would be to use GetRows to put the data into an array. Then
do all your lookups in the array. You'll be surprised at how fast it is.

I built a similar project some years ago in Access 2000.
Data input was via serial port, continuously streaming in.
I had two local be files (be1, be2) with the same structure.
The data was inserted into be1 from the fe, then periodically the fe would
switch to inserting the data into the be2 and the data from be1 would be
copied to a network file, then deleted.
be1 would be then compacted.
Periodically, the same would happen with be2.

In the case of the network file being unavailable, the system would simply
try again later, never missing a beat.
This has been running at an electric utility for about 10 years now.

--

AG
Email: npATadhdataDOTcom


"Neil" <(E-Mail Removed)> wrote in message
news:jkpl25$1sd$(E-Mail Removed)...
> Hi, Larry.
>
>> What do you mean by "run on a server and receive inputs from another
>> device"? And, indeed, are you asking specifically about Jet or ACE
>> databases? How will the database "receive inputs from another device"?

>
> It's a 2010 ACCDB file. There is Windows-based software called KEPServer
> which will receive data from external devices via the Internet, and then
> will write it to a database of the user's choosing. The KEPServer will be
> writing the values to my ACCDB file, one record at a time.
>
>> For the database to detect "when a record is written to a specific
>> table", unless it is your code doing the writing, the database will have
>> to support a feature called "triggers" -- Jet does not support triggers,
>> and they are new in ACE, but, even so, I have not read of many, if any,
>> "issues" with them.

>
> Yes, will be using the ACCDB AfterInsert Data Macro.
>
>> I'm guessing... just guessing from what you wrote... that either the
>> database application is still to be defined, or the database engine is
>> not yet selected. If so, then a server database would be a better choice
>> than either Jet or ACE. You do not describe what the purpose of the data
>> will be... surely it's not a "write-once, read never" thing -- that would
>> not be useful, unless to perhaps meet arbitrary requirements for saving
>> data that do not specify the saved data will ever be retrieved/used.

>
> Once the data is written to the raw data table by KEPServer, the
> AfterInsert macro will copy the record to another table in normalized
> format. Here is where the lookup is needed, since it needs to look up the
> device ID based on the information passed to it by KEPServer. The routine
> will also perform calculations and write them to a seperate table. These
> two tables (the normalized data and the separate calculation table) will
> be used for reporting purposes only. No editing of the data. So it's a
> write-once, read-many thing. (The only data the users will be editing are
> the lookup tables, client lists, etc.)
>
>
>> If you can clarify some of the points I mention here, you will vastly
>> improve the probability that someone can either offer useful suggestions
>> or direct you to a better source of information.

>
> Hopefully the above notes clarify. Sorry for not specifying the database
> format originally. Should have. If you need more info, let me know.
>
>
>> Because of the way Access, Jet, and ACE operate, to avoid the potential
>> of DB corruption, I avoid having recordsets "just sitting around open"...
>> I make use of recordsets as compact and contained as possible, often (and
>> that would certainly be the case where you do not know when, or how
>> often, activity will be needed), I open the Table/Query, read/write the
>> Record, and then immediately close the Table/Query to force the update to
>> take place. That would eliminate from my consideration an "always-open"
>> Recordset as you describe... having correct (uncorrupted) data is
>> essential; performance is worthless if the data is not correct.

>
> You make an excellent point. And I may just do it that way for that reason
> alone. However, I do want to point out that the recordset I was talking
> about leaving open continuously would be a snapshot recordset of a lookup
> table, used only for looking up values. No editing or writing to the
> recordset would be performed. So it's a slightly different situation than
> what you describe. Still, you make a valid point about corruption.
>
> Thanks, Larry!
>
> Neil
>



 
Reply With Quote
 
Neil
Guest
Posts: n/a
 
      27th Mar 2012

"ralph" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> For pure static views with long life-times and presenting a minimal
> impact on an active database file or server, you might checkout using
> a "disconnected Recordset". Plenty of examples on the web.


I searched for that. But, from what I can tell, that's purely an ADO
feature, right? I'm using DAO.

Thanks.


 
Reply With Quote
 
Neil
Guest
Posts: n/a
 
      27th Mar 2012
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. Shoot, I can't even imagine Windows itself,
without any apps running, going for too long without needing to be
restarted.

So can you tell me what their protocol is? Do they periodically reboot the
machine or at least restart Access?

Re. the array - that's a great idea, actually! I was originally thinking
that an array would be essentially the same as an open recordset, but I
guess not. Thinking about it, the recordset might not have all the items in
memory at all times, whereas the array would have to. So that probably would
be much faster than an open recordset.

Still, my main concern isn't with speed as much as system resources. But,
again, an array might be better at using fewer system resources.

Thanks!


"agiamb" <(E-Mail Removed)> wrote in message
news:jkq024$1sh$(E-Mail Removed)...
> If the lookup set is rarely updated, another option (also depending on
> number of rows), would be to use GetRows to put the data into an array.
> Then do all your lookups in the array. You'll be surprised at how fast it
> is.
>
> I built a similar project some years ago in Access 2000.
> Data input was via serial port, continuously streaming in.
> I had two local be files (be1, be2) with the same structure.
> The data was inserted into be1 from the fe, then periodically the fe would
> switch to inserting the data into the be2 and the data from be1 would be
> copied to a network file, then deleted.
> be1 would be then compacted.
> Periodically, the same would happen with be2.
>
> In the case of the network file being unavailable, the system would simply
> try again later, never missing a beat.
> This has been running at an electric utility for about 10 years now.
>
> --
>
> AG
> Email: npATadhdataDOTcom
>
>
> "Neil" <(E-Mail Removed)> wrote in message
> news:jkpl25$1sd$(E-Mail Removed)...
>> Hi, Larry.
>>
>>> What do you mean by "run on a server and receive inputs from another
>>> device"? And, indeed, are you asking specifically about Jet or ACE
>>> databases? How will the database "receive inputs from another device"?

>>
>> It's a 2010 ACCDB file. There is Windows-based software called KEPServer
>> which will receive data from external devices via the Internet, and then
>> will write it to a database of the user's choosing. The KEPServer will be
>> writing the values to my ACCDB file, one record at a time.
>>
>>> For the database to detect "when a record is written to a specific
>>> table", unless it is your code doing the writing, the database will have
>>> to support a feature called "triggers" -- Jet does not support triggers,
>>> and they are new in ACE, but, even so, I have not read of many, if any,
>>> "issues" with them.

>>
>> Yes, will be using the ACCDB AfterInsert Data Macro.
>>
>>> I'm guessing... just guessing from what you wrote... that either the
>>> database application is still to be defined, or the database engine is
>>> not yet selected. If so, then a server database would be a better choice
>>> than either Jet or ACE. You do not describe what the purpose of the data
>>> will be... surely it's not a "write-once, read never" thing -- that
>>> would not be useful, unless to perhaps meet arbitrary requirements for
>>> saving data that do not specify the saved data will ever be
>>> retrieved/used.

>>
>> Once the data is written to the raw data table by KEPServer, the
>> AfterInsert macro will copy the record to another table in normalized
>> format. Here is where the lookup is needed, since it needs to look up the
>> device ID based on the information passed to it by KEPServer. The routine
>> will also perform calculations and write them to a seperate table. These
>> two tables (the normalized data and the separate calculation table) will
>> be used for reporting purposes only. No editing of the data. So it's a
>> write-once, read-many thing. (The only data the users will be editing are
>> the lookup tables, client lists, etc.)
>>
>>
>>> If you can clarify some of the points I mention here, you will vastly
>>> improve the probability that someone can either offer useful suggestions
>>> or direct you to a better source of information.

>>
>> Hopefully the above notes clarify. Sorry for not specifying the database
>> format originally. Should have. If you need more info, let me know.
>>
>>
>>> Because of the way Access, Jet, and ACE operate, to avoid the potential
>>> of DB corruption, I avoid having recordsets "just sitting around
>>> open"... I make use of recordsets as compact and contained as possible,
>>> often (and that would certainly be the case where you do not know when,
>>> or how often, activity will be needed), I open the Table/Query,
>>> read/write the Record, and then immediately close the Table/Query to
>>> force the update to take place. That would eliminate from my
>>> consideration an "always-open" Recordset as you describe... having
>>> correct (uncorrupted) data is essential; performance is worthless if the
>>> data is not correct.

>>
>> You make an excellent point. And I may just do it that way for that
>> reason alone. However, I do want to point out that the recordset I was
>> talking about leaving open continuously would be a snapshot recordset of
>> a lookup table, used only for looking up values. No editing or writing to
>> the recordset would be performed. So it's a slightly different situation
>> than what you describe. Still, you make a valid point about corruption.
>>
>> Thanks, Larry!
>>
>> Neil
>>

>
>



 
Reply With Quote
 
agiamb
Guest
Posts: n/a
 
      27th Mar 2012
I have no idea if they restart or reboot, but if they were having a problem
I would surely have been contacted.
I do know that they have two dedicated machines running the app, just in
case one fails, they won't lose any data.
I've only worked on the system twice since it was initially stabilized. Once
when they got new equipment feeding the data and the line termination
characters changed. And once when they replaced a machine and linked the app
to the wrong back end.

As far as resources, etc., that's why I mentioned the number of rows.
Actually, it should be rows and columns (fields).
Several thousand, I probably wouldn't worry about, but millions, maybe.
You'll need to test it out.
I mentioned speed as it can be important depending on the frequency of the
lookups.
I first used the array method in a VB app about 12 years ago. We changed
from an Access be to SQL Server and went from DAO to ADO. There was a lot of
processing to be done in recordsets that could not be done in a set based
operation within SQL Server. Performance tanked going from DAO to ADO. I
started using arrays instead of recordsets and got all my performance back,
and then some.

--

AG
Email: npATadhdataDOTcom


"Neil" <(E-Mail Removed)> wrote in message
news:jks6ii$o85$(E-Mail Removed)...
> 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. Shoot, I can't even imagine Windows itself,
> without any apps running, going for too long without needing to be
> restarted.
>
> So can you tell me what their protocol is? Do they periodically reboot the
> machine or at least restart Access?
>
> Re. the array - that's a great idea, actually! I was originally thinking
> that an array would be essentially the same as an open recordset, but I
> guess not. Thinking about it, the recordset might not have all the items
> in memory at all times, whereas the array would have to. So that probably
> would be much faster than an open recordset.
>
> Still, my main concern isn't with speed as much as system resources. But,
> again, an array might be better at using fewer system resources.
>
> Thanks!
>
>
> "agiamb" <(E-Mail Removed)> wrote in message
> news:jkq024$1sh$(E-Mail Removed)...
>> If the lookup set is rarely updated, another option (also depending on
>> number of rows), would be to use GetRows to put the data into an array.
>> Then do all your lookups in the array. You'll be surprised at how fast it
>> is.
>>
>> I built a similar project some years ago in Access 2000.
>> Data input was via serial port, continuously streaming in.
>> I had two local be files (be1, be2) with the same structure.
>> The data was inserted into be1 from the fe, then periodically the fe
>> would switch to inserting the data into the be2 and the data from be1
>> would be copied to a network file, then deleted.
>> be1 would be then compacted.
>> Periodically, the same would happen with be2.
>>
>> In the case of the network file being unavailable, the system would
>> simply try again later, never missing a beat.
>> This has been running at an electric utility for about 10 years now.
>>
>> --
>>
>> AG
>> Email: npATadhdataDOTcom
>>
>>
>> "Neil" <(E-Mail Removed)> wrote in message
>> news:jkpl25$1sd$(E-Mail Removed)...
>>> Hi, Larry.
>>>
>>>> What do you mean by "run on a server and receive inputs from another
>>>> device"? And, indeed, are you asking specifically about Jet or ACE
>>>> databases? How will the database "receive inputs from another device"?
>>>
>>> It's a 2010 ACCDB file. There is Windows-based software called KEPServer
>>> which will receive data from external devices via the Internet, and then
>>> will write it to a database of the user's choosing. The KEPServer will
>>> be writing the values to my ACCDB file, one record at a time.
>>>
>>>> For the database to detect "when a record is written to a specific
>>>> table", unless it is your code doing the writing, the database will
>>>> have to support a feature called "triggers" -- Jet does not support
>>>> triggers, and they are new in ACE, but, even so, I have not read of
>>>> many, if any, "issues" with them.
>>>
>>> Yes, will be using the ACCDB AfterInsert Data Macro.
>>>
>>>> I'm guessing... just guessing from what you wrote... that either the
>>>> database application is still to be defined, or the database engine is
>>>> not yet selected. If so, then a server database would be a better
>>>> choice than either Jet or ACE. You do not describe what the purpose of
>>>> the data will be... surely it's not a "write-once, read never" thing --
>>>> that would not be useful, unless to perhaps meet arbitrary requirements
>>>> for saving data that do not specify the saved data will ever be
>>>> retrieved/used.
>>>
>>> Once the data is written to the raw data table by KEPServer, the
>>> AfterInsert macro will copy the record to another table in normalized
>>> format. Here is where the lookup is needed, since it needs to look up
>>> the device ID based on the information passed to it by KEPServer. The
>>> routine will also perform calculations and write them to a seperate
>>> table. These two tables (the normalized data and the separate
>>> calculation table) will be used for reporting purposes only. No editing
>>> of the data. So it's a write-once, read-many thing. (The only data the
>>> users will be editing are the lookup tables, client lists, etc.)
>>>
>>>
>>>> If you can clarify some of the points I mention here, you will vastly
>>>> improve the probability that someone can either offer useful
>>>> suggestions or direct you to a better source of information.
>>>
>>> Hopefully the above notes clarify. Sorry for not specifying the database
>>> format originally. Should have. If you need more info, let me know.
>>>
>>>
>>>> Because of the way Access, Jet, and ACE operate, to avoid the potential
>>>> of DB corruption, I avoid having recordsets "just sitting around
>>>> open"... I make use of recordsets as compact and contained as possible,
>>>> often (and that would certainly be the case where you do not know when,
>>>> or how often, activity will be needed), I open the Table/Query,
>>>> read/write the Record, and then immediately close the Table/Query to
>>>> force the update to take place. That would eliminate from my
>>>> consideration an "always-open" Recordset as you describe... having
>>>> correct (uncorrupted) data is essential; performance is worthless if
>>>> the data is not correct.
>>>
>>> You make an excellent point. And I may just do it that way for that
>>> reason alone. However, I do want to point out that the recordset I was
>>> talking about leaving open continuously would be a snapshot recordset of
>>> a lookup table, used only for looking up values. No editing or writing
>>> to the recordset would be performed. So it's a slightly different
>>> situation than what you describe. Still, you make a valid point about
>>> corruption.
>>>
>>> Thanks, Larry!
>>>
>>> Neil
>>>

>>
>>

>
>



 
Reply With Quote
 
ralph
Guest
Posts: n/a
 
      27th Mar 2012
On Tue, 27 Mar 2012 05:52:26 -0500, "Neil"
<(E-Mail Removed)> wrote:

>
>"ralph" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>> For pure static views with long life-times and presenting a minimal
>> impact on an active database file or server, you might checkout using
>> a "disconnected Recordset". Plenty of examples on the web.

>
>I searched for that. But, from what I can tell, that's purely an ADO
>feature, right? I'm using DAO.
>


Yes, a "disconnected Recordset" is purely an ADO technique. Should
have asked what data library you were using, but with a quick read of
your question it appeared you were having trouble with "variable
references", therefore I erroneously assumed ADO. (Which is how most
assumptions turn out. <g>)

DAO requires a connection to a database.

For situations where a client needed to maintain a data set with
fields completely divorced from other tables using DAO, TableDef that
is populated with the associated data is often used. You could
probably just as easily resolve problems by restructuring the
database.

However, GetRows appears to be a good solution. (Wish I had thought of
it before I posted. <g>)

Both DAO and ADO have a GetRows method. The difference between them is
DAO's GetRows(n) requires a recordnumber, and ADO's GetRows() will
retrieve all rows by default.

-ralph
 
Reply With Quote
 
New Member
Join Date: Mar 2012
Posts: 1
 
      27th Mar 2012
Can't you simply run an update query?
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Theoretical question as to datasets Earl Microsoft ADO .NET 4 30th Aug 2004 02:27 AM
Theoretical question Oriane Microsoft Dot NET 0 7th Jun 2004 09:36 AM
Sessions: A theoretical question Ron M. Newman Microsoft ADO .NET 10 3rd Apr 2004 07:28 AM
Client consideration (theoretical) Paul Microsoft ASP .NET 0 24th Mar 2004 07:19 PM
Theoretical Question : Explicit Object 'Destruction' jdn Microsoft C# .NET 3 31st Jul 2003 07:01 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 09:31 PM.