Closing connections and recordsets

[Guest]

Hello -

Most of the code I see involving connections shows closing the recordset and
closing the connection immediately thereafter. I know it's important to
close everything and set it = nothing.

If I am going to be moving first, next, previous and last, the whole time
there has to be an active connection; correct? Then for the Form_Load, where
I establish the connection and retrieve the records, the connection cannot be
closed until the user is done; correct? Inserts, deletes and updates in the
meantime are closed, but all the while there is one on-going connection.

Is there a better way to do this type of thing where someone couldn't
essentially keep a connection open for an entire day?

I'm using Sql Server stored procedures.

Any input will be greatly appreciated!

 

[Michel Walsh]

Hi,


Most of the recent literature, indeed, mentions to open and then, close the
connection as soon as possible after, and to minimize the work done on the
server. That does not apply to Access, the application, where the connection
is assumed to be constantly alive, and most of the work is performed on the
client, anyway. The problem of leaving a "pending" operation on an active
connection, with Access, is that you may lock out other users (depend of the
type of record locking you use, or if you use explicit transactions). You
are at a high risk to get an error, with Access, if, indeed, you loose the
connection with the Jet database behind it. It is probably less of a problem
for linked tables, in most cases I have experienced, anyhow. Note that your
transactions, on MS SQL Server, may time-out by themselves, and be
rolled-back, but that is not to happen automatically on Jet. So, while
keeping the connection alive is not really a problem with Access, still keep
your explicit transactions, if any, as short as possible, to not lock out
other users.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks, Michel!
--
Sandy

Hi,


Most of the recent literature, indeed, mentions to open and then, close the
connection as soon as possible after, and to minimize the work done on the
server. That does not apply to Access, the application, where the connection
is assumed to be constantly alive, and most of the work is performed on the
client, anyway. The problem of leaving a "pending" operation on an active
connection, with Access, is that you may lock out other users (depend of the
type of record locking you use, or if you use explicit transactions). You
are at a high risk to get an error, with Access, if, indeed, you loose the
connection with the Jet database behind it. It is probably less of a problem
for linked tables, in most cases I have experienced, anyhow. Note that your
transactions, on MS SQL Server, may time-out by themselves, and be
rolled-back, but that is not to happen automatically on Jet. So, while
keeping the connection alive is not really a problem with Access, still keep
your explicit transactions, if any, as short as possible, to not lock out
other users.


Hoping it may help,
Vanderghast, Access MVP