calls to extract data from an open database
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" wrote in message
...
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
.
|