VB Forum / Database Access / December 2007

The latter.

Why your having trouble finding a satisfactory answer is probably over what
a particular author means by a "connection". When a client creates an
initial connection by creating an ADODB.Connection object, this is where all
the heavy work gets done. The ADO library seeks out the requested provider,
validates it, then seeks out the database, wakes up the engine, ... in short
assembles a pathway from your client to the database thru all the
intervening components (or wire if involved).

It then setups a 'connection pool' with one connection. When you call .Open
on the ADODB.Connection it then pulls an available connection from the pool.
If one is busy it creates another one for you. This part is quick. Think of
the initial connection as precompiled and ready of use at a moments notice.

Also once you have created an ADODB.Connection object, if you create another
one using the same primary elements of the previous Connection String - you
get the same pool as the first. (So you can have mulitple Connection
objects, yet only one 'connection')

So the reason to keep one connection around is to avoid the expense of
re-creating a connection, but the ADO library is doing this for you. There
is always one available in the pool.

Also note that connections are automatically closed after a certain amount
of time of inactivity. So even if you think you're keeping a connection open
for the duration of an App - it is actually being open/close implicitly
anyway.

The best way to manage ADO is to create one ADODB.Connection object and keep
it for the life of the App. Then only open and close it as you need it.

If you can get a copy of Bill Vaughn's "ADO Examples and Best Practices",
Apress.

hth
-ralph

¤ Hi all,
¤
¤ This question has probably been answerd before but I don't find an
¤ satisfying answer in Google.
¤
¤ What is best?
¤  Open the connection when the application loads and keep it open or
¤
¤ Open the connection just when I need it and close it immediately after the
¤ dbase operation.
¤
¤ Thanks for the advise

Personally I think it depends upon the database you are working with. With a local file based system
there probably isn't much reason to be opening and closing connections frequently. It just adds
overhead.

Most server based systems implement some sort of connection pooling and an effective use of that
would be to open and close connections only when data is required.

Paul
~~~~
Microsoft MVP (Visual Basic)