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