Excel - Oracle database connection

Hi,

I have some problems!

- when I open the workbook I make oracle database connection:

Public Sub Workbook_Open()
Dim conOracle As ADODB.Connection
Set conOracle = New ADODB.Connection

conOracle.Open "DSN=db1; User ID=user; prompt = complete"
End Sub

- then in a normal module i run some macro and want to check if
connection is active

Sub Check_connection()
Dim conOracle As ADODB.Connection

check_con = conOracle.State

When your workbook open code ends so does conOracle... You could make
conOracle into a global varaible at which point it will persist after the
procedure ends. That being said I normally avoid holding connections to
databases for the duration of the application. It is possibly not an
efficient use of your system resources. I would be inclined to remove the
workbook open code and then connect and disconnect as is necessary. Something
like this

Sub Check_connection()
Dim conOracle As ADODB.Connection
Set conOracle = New ADODB.Connection

conOracle.Open "DSN=db1; User ID=user; prompt = complete"
check_con = conOracle.State

Thank you Jim.

I use solution what you proposed but at every database interrogation I
have to log in what is inconveniet when I login several times during 2-4
minutes and that is the reason why I wanted to open connection once.

Maybe it is better way to make this action in one module using "if"
connection active or not.

Best Regards
BR

Jim Thomlinson napisał(a):
> When your workbook open code ends so does conOracle... You could make
> conOracle into a global varaible at which point it will persist after the
> procedure ends. That being said I normally avoid holding connections to
> databases for the duration of the application. It is possibly not an
> efficient use of your system resources. I would be inclined to remove the
> workbook open code and then connect and disconnect as is necessary. Something
> like this
>
> Sub Check_connection()
> Dim conOracle As ADODB.Connection
> Set conOracle = New ADODB.Connection
>
> conOracle.Open "DSN=db1; User ID=user; prompt = complete"
> check_con = conOracle.State
> .
> .
> conOracle.close
> set conoracle = nothing
> end sub

What you are proposing is to use a global variable which is a viable route to
go. Use your on open code but instead of declaring the variable in the
procedure declare it public at the top of as standard module. My only comment
would be where you know you are going to have a long period of inactivity
close the connection. You can then use your ".state" to check if the
connection is active.
--
HTH...

Jim Thomlinson


"b_r" wrote:

> Thank you Jim.
>
> I use solution what you proposed but at every database interrogation I
> have to log in what is inconveniet when I login several times during 2-4
> minutes and that is the reason why I wanted to open connection once.
>
> Maybe it is better way to make this action in one module using "if"
> connection active or not.
>
> Best Regards
> BR
>
> Jim Thomlinson napisał(a):
> > When your workbook open code ends so does conOracle... You could make
> > conOracle into a global varaible at which point it will persist after the
> > procedure ends. That being said I normally avoid holding connections to
> > databases for the duration of the application. It is possibly not an
> > efficient use of your system resources. I would be inclined to remove the
> > workbook open code and then connect and disconnect as is necessary. Something
> > like this
> >
> > Sub Check_connection()
> > Dim conOracle As ADODB.Connection
> > Set conOracle = New ADODB.Connection
> >
> > conOracle.Open "DSN=db1; User ID=user; prompt = complete"
> > check_con = conOracle.State
> > .
> > .
> > conOracle.close
> > set conoracle = nothing
> > end sub
>