Option Explicit
Private con As ADODB.Connection
Private cst As String
Private rst As ADODB.Recordset
Private DBFunctionsMenuSetupFrequencyCount As Integer
Private DBConnectionFrequencyCount As Integer
' 1. Connection
' 1.1. Start
Public Sub setDBConnection()
' Exit if the connection
is already present
If Not IsEmpty(DBConnectionFrequencyCount)
_
And DBConnectionFrequencyCount
> 0 _
Then
'Exit
Sub
End If
' Open the connection
Set con = New ADODB.Connection
con.ConnectionTimeout = 3 'Seconds
con.CursorLocation = adUseClient
con.IsolationLevel = adXactReadUncommitted
cst = _
"Data Source=DB_ODBC_NAME; " & _
"Database=DATABASE_NAME; "
& _
"Session Mode=TERA;" 'or ANSI (Teradata)
con.Open cst
' Generic Recordset
Declaration
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.CursorType
= adOpenForwardOnly
rst.LockType = adLockReadOnly
rst.MaxRecords = gloMaxRecords
' Count the number of
times a connection has been made
DBConnectionFrequencyCount
= DBConnectionFrequencyCount + 1
End Sub
' 1.2. Terminate
Public Sub getDBConnectionTerminated()
Set con = Nothing
DBConnectionFrequencyCount
= 0
End Sub
' 2.1. getCustomerName
Public Function getCustomerName( customerId As String) As String
' SQL
Dim sql As String
sql =
_
"SELECT max( clientName ) as Result
" & _
"FROM " & _
"CLIENTS " & _
"WHERE " & _
"clientId='" & customerId & "' " & _
";"
' Open connection and
recordset
Call setDBConnection
rst.Open sql, con
'Debug.Print rst.ActiveConnection.ConnectionString
' Use the recordset
Do While Not rst.EOF
getCustomerName = rst.Fields("Result")
rst.MoveNext
Loop
' Exit, cleanup
rst.Close
'getDBConnectionTerminated
con.Close
Exit Function
End Function