I have a workbook that on the “open” event connects to an Oracle database
using ADO, this works fine.

I need to code various calls to the data base to run several queries. How
do I achieve this? Do I have to repeat the ADO connection string to the
database complete with password etc; I am sure this is not necessary.

Any code will be very gratefully received.

--
with kind regards

Spike

The usual way to handle this is have a Public or Private ADO connection
object so you can set that up once and
keep it alive till you don't need it anymore.
So for example:

Option Explicit
Public oADOConn As ADODB.Connection

Sub OpenConnection(strConnString As String, _
strUserName As String, _
strPassWord As String)

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

If oADOConn.State = 0 Then
oADOConn.Open strConnString, strUserName, strPassWord
End If

End Sub


RBS


"Spike" <(E-Mail Removed)> wrote in message
news7EE54B2-88F2-46BE-B80B-(E-Mail Removed)...
>I have a workbook that on the “open” event connects to an Oracle database
> using ADO, this works fine.
>
> I need to code various calls to the data base to run several queries. How
> do I achieve this? Do I have to repeat the ADO connection string to the
> database complete with password etc; I am sure this is not necessary.
>
> Any code will be very gratefully received.
>
> --
> with kind regards
>
> Spike

Thank you for that. Does the connection stay open to the workbook until it
is closed; so the while the connection is open one can run a completely
different macro and then run a macro that will extract data from the
database. If so is the code something like as below or does one have to
provide user name and password every time?

With oADOConn
.CommandText = "SELECT PB_RATES.COB_DATE, PB_RATES.MAT_BIN_START,
PB_RATES.MAT_BIN_END," _
& "PB_RATES.MAT_RATE_LOAN" _
& " FROM OPS$ORA_ADMIN.PB_RATES PB_RATES" _
& " WHERE (PB_RATES.COB_DATE={ts '" & strRateDate & "'})"

..CommandType = adCmdText

Set RS = New ADODB.Recordset
RS.CursorType = adOpenStatic
RS.LockType = adLockReadOnly
Set RS.Source = CM

bolDataBaseErr = True 'changed to false if opens RS correctly

RS.Open
RS.MoveFirst

Etc etc

--
with kind regards

Spike


"RB Smissaert" wrote:

> The usual way to handle this is have a Public or Private ADO connection
> object so you can set that up once and
> keep it alive till you don't need it anymore.
> So for example:
>
> Option Explicit
> Public oADOConn As ADODB.Connection
>
> Sub OpenConnection(strConnString As String, _
> strUserName As String, _
> strPassWord As String)
>
> If oADOConn Is Nothing Then
> Set oADOConn = New ADODB.Connection
> End If
>
> If oADOConn.State = 0 Then
> oADOConn.Open strConnString, strUserName, strPassWord
> End If
>
> End Sub
>
>
> RBS
>
>
> "Spike" <(E-Mail Removed)> wrote in message
> news7EE54B2-88F2-46BE-B80B-(E-Mail Removed)...
> >I have a workbook that on the “open” event connects to an Oracle database
> > using ADO, this works fine.
> >
> > I need to code various calls to the data base to run several queries. How
> > do I achieve this? Do I have to repeat the ADO connection string to the
> > database complete with password etc; I am sure this is not necessary.
> >
> > Any code will be very gratefully received.
> >
> > --
> > with kind regards
> >
> > Spike
>
> .
>

> Does the connection stay open to the workbook until it is closed?
Yes, unless you close it actively with oADOConn.Close or Set oADOConn =
Nothing or if you close the Workbook.

> If so is the code something like as below?
Yes, connection has been set up once already and stays alive.


RBS


"Spike" <(E-Mail Removed)> wrote in message
news:30087751-AAF3-482E-86A9-(E-Mail Removed)...
> Thank you for that. Does the connection stay open to the workbook until
> it
> is closed; so the while the connection is open one can run a completely
> different macro and then run a macro that will extract data from the
> database. If so is the code something like as below or does one have to
> provide user name and password every time?
>
> With oADOConn
> .CommandText = "SELECT PB_RATES.COB_DATE, PB_RATES.MAT_BIN_START,
> PB_RATES.MAT_BIN_END," _
> & "PB_RATES.MAT_RATE_LOAN" _
> & " FROM OPS$ORA_ADMIN.PB_RATES PB_RATES" _
> & " WHERE (PB_RATES.COB_DATE={ts '" & strRateDate & "'})"
>
> .CommandType = adCmdText
>
> Set RS = New ADODB.Recordset
> RS.CursorType = adOpenStatic
> RS.LockType = adLockReadOnly
> Set RS.Source = CM
>
> bolDataBaseErr = True 'changed to false if opens RS correctly
>
> RS.Open
> RS.MoveFirst
>
> Etc etc
>
> --
> with kind regards
>
> Spike
>
>
> "RB Smissaert" wrote:
>
>> The usual way to handle this is have a Public or Private ADO connection
>> object so you can set that up once and
>> keep it alive till you don't need it anymore.
>> So for example:
>>
>> Option Explicit
>> Public oADOConn As ADODB.Connection
>>
>> Sub OpenConnection(strConnString As String, _
>> strUserName As String, _
>> strPassWord As String)
>>
>> If oADOConn Is Nothing Then
>> Set oADOConn = New ADODB.Connection
>> End If
>>
>> If oADOConn.State = 0 Then
>> oADOConn.Open strConnString, strUserName, strPassWord
>> End If
>>
>> End Sub
>>
>>
>> RBS
>>
>>
>> "Spike" <(E-Mail Removed)> wrote in message
>> news7EE54B2-88F2-46BE-B80B-(E-Mail Removed)...
>> >I have a workbook that on the “open” event connects to an Oracle
>> >database
>> > using ADO, this works fine.
>> >
>> > I need to code various calls to the data base to run several queries.
>> > How
>> > do I achieve this? Do I have to repeat the ADO connection string to
>> > the
>> > database complete with password etc; I am sure this is not necessary.
>> >
>> > Any code will be very gratefully received.
>> >
>> > --
>> > with kind regards
>> >
>> > Spike
>>
>> .
>>

thank you very much for that i will code it up tomorrow. i am most grateful.
--
with kind regards

Spike


"RB Smissaert" wrote:

> > Does the connection stay open to the workbook until it is closed?
> Yes, unless you close it actively with oADOConn.Close or Set oADOConn =
> Nothing or if you close the Workbook.
>
> > If so is the code something like as below?
> Yes, connection has been set up once already and stays alive.
>
>
> RBS
>
>
> "Spike" <(E-Mail Removed)> wrote in message
> news:30087751-AAF3-482E-86A9-(E-Mail Removed)...
> > Thank you for that. Does the connection stay open to the workbook until
> > it
> > is closed; so the while the connection is open one can run a completely
> > different macro and then run a macro that will extract data from the
> > database. If so is the code something like as below or does one have to
> > provide user name and password every time?
> >
> > With oADOConn
> > .CommandText = "SELECT PB_RATES.COB_DATE, PB_RATES.MAT_BIN_START,
> > PB_RATES.MAT_BIN_END," _
> > & "PB_RATES.MAT_RATE_LOAN" _
> > & " FROM OPS$ORA_ADMIN.PB_RATES PB_RATES" _
> > & " WHERE (PB_RATES.COB_DATE={ts '" & strRateDate & "'})"
> >
> > .CommandType = adCmdText
> >
> > Set RS = New ADODB.Recordset
> > RS.CursorType = adOpenStatic
> > RS.LockType = adLockReadOnly
> > Set RS.Source = CM
> >
> > bolDataBaseErr = True 'changed to false if opens RS correctly
> >
> > RS.Open
> > RS.MoveFirst
> >
> > Etc etc
> >
> > --
> > with kind regards
> >
> > Spike
> >
> >
> > "RB Smissaert" wrote:
> >
> >> The usual way to handle this is have a Public or Private ADO connection
> >> object so you can set that up once and
> >> keep it alive till you don't need it anymore.
> >> So for example:
> >>
> >> Option Explicit
> >> Public oADOConn As ADODB.Connection
> >>
> >> Sub OpenConnection(strConnString As String, _
> >> strUserName As String, _
> >> strPassWord As String)
> >>
> >> If oADOConn Is Nothing Then
> >> Set oADOConn = New ADODB.Connection
> >> End If
> >>
> >> If oADOConn.State = 0 Then
> >> oADOConn.Open strConnString, strUserName, strPassWord
> >> End If
> >>
> >> End Sub
> >>
> >>
> >> RBS
> >>
> >>
> >> "Spike" <(E-Mail Removed)> wrote in message
> >> news7EE54B2-88F2-46BE-B80B-(E-Mail Removed)...
> >> >I have a workbook that on the “open” event connects to an Oracle
> >> >database
> >> > using ADO, this works fine.
> >> >
> >> > I need to code various calls to the data base to run several queries.
> >> > How
> >> > do I achieve this? Do I have to repeat the ADO connection string to
> >> > the
> >> > database complete with password etc; I am sure this is not necessary.
> >> >
> >> > Any code will be very gratefully received.
> >> >
> >> > --
> >> > with kind regards
> >> >
> >> > Spike
> >>
> >> .
> >>
>
> .
>