calls to extract data from an open database

[Spike]

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.

 

[RB Smissaert]

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]

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

 

[RB Smissaert]

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]

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