Can DB connection retain throughout the Excel working session ?
 

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

Can DB connection retain throughout the Excel working session ?
 

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

Can DB connection retain throughout the Excel working session ?
 

Tim Williams wrote:
just declare your connection
object as a global variable

A Public variable is not required. I'd recommend a Friend Property in
the ThisWorkbook code module which doesn't open the connection until
the first time it is used e.g.

Option Explicit

Private m_Connection As ADODB.Connection

Friend Property Get Connection() As ADODB.Connection

Const CONN_STRING As String = "<<connection string here"

If m_Connection Is Nothing Then
Set m_Connection = New ADODB.Connection
End If

With m_Connection

If m_Connection.State = adStateClosed Then
.ConnectionString = CONN_STRING

On Error Resume Next
.Open
' (test for and handle connection errors here)
On Error GoTo 0
End If
End With

Set Connection = m_Connection
End Property


Jamie.

--

Can DB connection retain throughout the Excel working session ?
 

Thanks Tim and Jamie.

I have tried both method - Public Variable and Friend Property.
Both can achieve what I want.

Just for curiosity :-
1. Does the Friend property safer in terms of security ?
i.e. If I use public variable, is other programs / workbooks
running in the same machine
able to hijack my SQL connection to do their own tasks ?
2. Although I see when the workbook is close, Office will help to close the
connection for me.
I hope to be more discipline, to close the connection by myself
explicitly
(anyway I think it should be my responsbility as I am the one who
create it)
However, I've tried to code at ThisWorkbook event - "BeforeClose"
and "Deactivate",
either event seems happen after Office already close the
connection for me.
Is my guess right ? If so, where should I code the close connection
code?

Thanks again for anyone 's help ... Eric

"Jamie Collins" ???
ups.com ???...
Tim Williams wrote:
just declare your connection
object as a global variable

A Public variable is not required. I'd recommend a Friend Property in
the ThisWorkbook code module which doesn't open the connection until
the first time it is used e.g.

Option Explicit

Private m_Connection As ADODB.Connection

Friend Property Get Connection() As ADODB.Connection

Const CONN_STRING As String = "<<connection string here"

If m_Connection Is Nothing Then
Set m_Connection = New ADODB.Connection
End If

With m_Connection

If m_Connection.State = adStateClosed Then
.ConnectionString = CONN_STRING

On Error Resume Next
.Open
' (test for and handle connection errors here)
On Error GoTo 0
End If
End With

Set Connection = m_Connection
End Property


Jamie.

--

Can DB connection retain throughout the Excel working session ?
 

Eric Fung wrote:
1. Does the Friend property safer in terms of security ?

I try to use the minimum scope possible e.g. if the connection isn't
required outside of the VBA project then don't expose it via a public
property/variable. Security isn't really a consideration here because
an Excel workbook's VBA project isn't secure enough to contain
sensitive information.

2. Although I see when the workbook is close, Office will help to
close the
connection for me.
I hope to be more discipline, to close the connection by myself
explicitly
However, I've tried to code at ThisWorkbook event - "BeforeClose"
and "Deactivate",
either event seems happen after Office already close the
connection for me.

I'd use the _BeforeClose event, with the extra code required to handle
the situation where the user cancels a save and therefore cancels the
close. Module level variables in the ThisWorkbook code module should
still be in scope (i.e. should not have been garbage-collected) when
the _BeforeClose and _Deactivate events fire, unless something has
happened to reset the VBA project e.g. hitting the reset button in the
Visual Basic Editor, adding controls to a worksheet, etc.

Jamie.

--