When to Open and Close ADO Connections?

[Elena]

Hi,

Just a quick question. I have several functions that get information from a database and display it on a form. Should I open the connection to the Database in one place, rather than opening and closing in each function? What's the difference?

Thanks,
Elena

 

[zemp]

It is really up to you. Some guidlines that I use are the frequency of database trips and the potential length of the trip. If the app is constantly going to the database for information then I like to keep one connection open for the duration of the app.

Another factor is the app and network setup (local vs client/server). In other words, how long does it take to open a connection and would it be faster to keep one open rather than opening and closing many?

Take Care,

zemp

"Show me someone with both feet on the ground and I will show you someone who can't put their pants on."

 

[CCLINT]

Which ever way you go, I suggest just having ONE connection object for one database.
Use it with-in the same application everywhere.

Whether you continuously open and close it or not, is a different matter, and where zemp's advice is then to be considered.

 

[BobRodes]

One other thing to look at: If the info that you're getting isn't subject to change during the lifetime of the app, but you need access to it on an off and on basis in the app, you might consider disconnected recordsets. You can keep the data locally and access it, even when the connection is closed.

Bob

 

[Elena]

Bob,

That is way over my head. I haven't even heard of a disconnected recordset.

Elena

 

[BobRodes]

You might want to read up on them, they're not difficult. I use them all the time.

All you do is set the following properties:

rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic

This will give you a static recordset that will persist after you close the connection. You can also update this recordset locally, and then call the UpdateBatch method to send all of the changes to the server at one time.

HTH

Bob