You may find that server resources are better conserved by not holding
too many open connections concurrently. Windows is pretty good at
caching connections, so there is often little overhead in connecting
again. Of course, if you are making a large number of calls the best
approach is to keep it open.

If you want to keep the connection open just declare your connection
object as a global variable: that way it will maintain state between
calls.


Tim

"Eric Fung" <Pls type eric.fung plus @hld.com wrote in message
...
Dear all,

I am novice to programming in Excel.

I want to know, if possible, how to let an Excel user open a
connection to a
DB server (e.g. SQL server)
and then lets all operation for the user to work with that
connection,
and close the connection only when the user exit Excel.

This is what I expect from an old fashion client server program.

I've seen code sample of ADO in Excel VBA,
to open a connection, perform DB operation, and then close
connection.
however, as the series of operation is done within the scope of a
subroutine
for a particular event,
every such event will cause a new connection on the DB server.

I don't want to carry out logon / logoff on the SQL Server
repetitively,
to minmize the extra burden on SQL server.

Is my way of thoughts OK? Can it be done ?

Thanks in advance for anyone's kind help ...
Eric