Hi,
Access (not Jet) assumes you have a lively open connection between the
application, the form, and the database, so we don't close the connection,
in general, after each use.
For applications that assumes you can have a very large number of users (100
to 1000+), it is recommended to close the connection asap, but Access
assumes a relatively modest number of users (no more that 255) and is not
built upon the assumption that we have to close the connection asap, doing
some job on the client side, disconnected. While you can use Jet for that
scenario too, I suggest you rather use MS SQL Server which has the built-in
sophistications to, as example, unroll a transaction that takes too long to
be complete (lost of the connection in the middle of an update, as example),
and other "robustness" against problems that can be encounter when there is
a large number of clients over a large number of external physical
connections. An alternative to use Access in such cases, is to use
Replication. Each user is continuously connected to his/her local copy of
the database, and, from time to time, Replication Synchronization is used
toward the "master" or toward dedicated copies. So, in summary:
Access: Continuous connection. Conflict manager is mostly just
around the "record locking" mechanism
VB6, ADO.Net disconnected: Point (one, of few, tables) data with
connection occurring at a very short interval of time between the next
connection, to get other data or to synchronize. Synchronization, if any,
mostly as update,may need conflict manager (since no lock occur on the
modified data, being disconnected). The local copy does not support multiple
users, since data is in memory (and can hardly share volatile memory between
different computers).
Replication: Connection in time, at relatively long interval of time
between reconnection, but for the whole data (not a table, or few tables).
Assumes the structure is mostly unchanged (no table added, no fields added,
generally). May need to customize the supplied conflict manager. The local
copy can support multiple users, since data is a local database.
Hoping it may help,
Vanderghast, Access MVP
