AccessOracleAdo

Function CreateRecordSet(ByRef dbConn As ADODB.Connection, _
        ByRef rs As ADODB.Recordset, ByVal method As RSMethod, _
        Optional strSQL As String, Optional TableName As String) _
        As ADODB.Recordset
    
    ' chiude il recordset se gia aperto...
    If rs.State=1 Then
        rs.close
    End If
    Select Case method
        Case RSMethod.NEW_RECORD
            rs.ActiveConnection = dbConn
            rs.CursorType = adOpenKeyset
            rs.LockType = adLockOptimistic
            rs.CursorLocation = adUseServer
            rs.Open TableName
        Case RSMethod.EDIT_RECORD
            rs.ActiveConnection = dbConn
            rs.Source = strSQL
            rs.CursorType = adOpenKeyset
            rs.LockType = adLockOptimistic
            rs.CursorLocation = adUseClient
            rs.Open
            ' Debug.Print "SQL Statement in EDIT Mod
            '     e (Createrecordset) : " & strSQL
            ' Debug.Print "Found " & rs.RecordCount
            '     & " records"
        Case RSMethod.VIEW_RECORD
            rs.ActiveConnection = dbConn 'dbConnection 'dbConn
            rs.Source = strSQL
            rs.CursorType = adOpenForwardOnly
            rs.CursorLocation = adUseClient
            rs.Open
            ' Debug.Print "Found " & rs.RecordCount
            '     & " records"
            rs.ActiveConnection = Nothing
        Case RSMethod.EXEC_SQL
            Set rs = dbConn.Execute(strSQL)
    End Select
    Set CreateRecordSet = rs
End Function

'======================================
'fine del modulo
'======================================
'======================================
'subroutines...
'======================================
Sub Add_New_Record()
    Dim objRecSet As New ADODB.Recordset
    Dim objConn As New ADODB.Connection
    Dim strUserID As String
    Dim strPassword As String
    Dim strTableName As String
    Dim strDBType As String
    Dim strDBName As String
    
    strTableName = "YOURTABLE"
    strPassword = "YourPassword"
    strUserID = "YourUserID"
    
    If strDBType = "MSACCESS" Then
        'strDBName e' il nome del Database
        strDBName = App.Path & "\AccessDB.mdb"
    ElseIf strDBType = "ORACLE" Then
        'strDBName e' il nome del servizio Oracle
        strDBName = "YOUR_ORACLE_SERVICE_NAME"
        strTableName = strUserID & "." & strTableName
        'ormato nome della tabella ::> USERID.TABLENAME
    Else
        MsgBox "Il Database non e' ORACLE o Access"
        Exit Sub
    End If
    
    Set objConn = dbConnection(strDBType, strDBName, "userid", "password")
    Set objRecSet = CreateRecordSet(objConn, objRecSet, NEW_RECORD, , strTableName)
    objConn.BeginTrans
    With objRecSet
        .AddNew
        .Fields("FIELD1").Value = " valore1"
        .Fields("FIELD2").Value = " valore2"
        .Fields("FIELD3").Value = " valore3"
        .Fields("FIELD4").Value = " valore4"
        .Fields("FIELD5").Value = " valore5"
        .Update
    End With
    If objConn.Errors.Count = 0 Then
        objConn.CommitTrans
    Else
        objConn.RollbackTrans
    End If
    
    objRecSet.Close
    objConn.Close
    Set objRecSet = Nothing
    Set objConn = Nothing
End Sub

Sub View_Record_Only()
    Dim strSQL As String
    Dim strDBName As String
    Dim strDBType As String
    Dim strUserID As String
    Dim strPassword As String
    
    Dim objRecSet As New ADODB.Recordset
    Dim objConn As New ADODB.Connection
    If strDBType = "MSACCESS" Then
        strDBName = App.Path & "\YourAccessDB.mdb"
        
    ElseIf strDBType = "ORACLE" Then
        strDBName = "YOUR_ORACLE_SERVICE_NAME"
    Else
        MsgBox "Nessun Database ORACLE o Access"
        Exit Sub
    End If
    
    strPassword = "YourPassword"
    strUserID = "YourUserID"
    strSQL = "SELECT * from USER_TABLE"
    
    Set objConn = dbConnection(strDBType, strDBName, "userid", "password")
    'disconnete il recordset
    Set objRecSet = CreateRecordSet(objConn, objRecSet, VIEW_RECORD, strSQL)
    objConn.Close
    Set objConn = Nothing
    'manipola recordset .....
    'manipola recordset .....
    'manipola recordset .....
    objRecSet.Close
    Set objRecSet = Nothing
End Sub

Sub Edit_Existing_Record()
    Dim objRecSet As New ADODB.Recordset
    Dim objConn As New ADODB.Connection
    Dim strUserID As String
    Dim strPassword As String
    Dim strSQL As String
    Dim strDBType As String
    Dim strDBName As String
    
    strTableName = "YOURTABLE"
    strPassword = "YourPassword"
    strUserID = "YourUserID"
    If strDBType = "MSACCESS" Then
        strDBName = App.Path & "\YourAccessDB.mdb"
    ElseIf strDBType = "ORACLE" Then
        strDBName = "YOUR_ORACLE_SERVICE_NAME"
    Else
        Exit Sub
    End If
    strSQL = "Select * from YOUR_TABLE"
    Set objConn = dbConnection(strDBType, strDBName, "userid", "password")
    Set objRecSet = CreateRecordSet(objConn, objRecSet, EDIT_RECORD, strSQL)
    
    With objRecSet
        .Fields("FIELD1").Value = " valore1"
        .Update
    End With
    objRecSet.Close
    objConn.Close
    Set objRecSet = Nothing
    Set objConn = Nothing
End Sub

'======================================
'Fine subroutines...
'======================================
 'Codice da inserire in un Modulo
'
Option Explicit
Public Enum RSMethod
    VIEW_RECORD = 0
    EDIT_RECORD = 1
    EXEC_SQL = 2
    NEW_RECORD = 3
End Enum
Function dbConnection(strDatabaseType As String, strDBService _
        As String, Optional strUserID As String, _
        Optional strPassword As String) As ADODB.Connection
    
    Dim objDB As New ADODB.Connection
    Dim strConnectionString As String
    If strDatabaseType = "ORACLE" Then
        'Definisce la stringa di connessine al DB Oracle
        strConnectionString = _
            "Driver={Microsoft ODBC Driver For Oracle};ConnectString=" & _
            strDBService & ";UID=" & strUserID & ";PWD=" & strPassword & ";"
    ElseIf strDatabaseType = "MSACCESS" Then
        'Definisce la stringa di connessione a Microsoft Access
        strConnectionString = "DBQ=" & strDBService
        strConnectionString = _
            "DRIVER={Microsoft Access Driver (*.mdb)}; " & strConnectionString
    End If
    
    With objDB
        .Mode = adModeReadWrite 'modalita di connessione ???
        .ConnectionTimeout = 10 'Indica il tempo di attesa per stabilire
        'la connessione prima di terminare i tentativi e generare un errore.
        .CommandTimeout = 5 ' secondi timeout per eseguire qualsiasi comando
        .CursorLocation = adUseClient ' tipo di cursore ???
        .Open strConnectionString ' apre la connessione al database
        
    End With
    
    Set dbConnection = objDB
End Function

'======================================
'Fine subroutines...
'======================================

'Codice da inserire in un Modulo

Option Explicit
Public Enum RSMethod
VIEW_RECORD = 0
EDIT_RECORD = 1
EXEC_SQL = 2
NEW_RECORD = 3
End Enum
Function dbConnection(strDatabaseType As String, strDBService _
As String, Optional strUserID As String, _
Optional strPassword As String) As ADODB.Connection

Dim objDB As New ADODB.Connection
Dim strConnectionString As String
If strDatabaseType = "ORACLE" Then
'Definisce la stringa di connessine al DB Oracle

strConnectionString = _
"Driver={Microsoft ODBC Driver For Oracle};ConnectString=" & _
strDBService & ";UID=" & strUserID & ";PWD=" & strPassword & ";"
ElseIf strDatabaseType = "MSACCESS" Then
'Definisce la stringa di connessione a Microsoft Access

strConnectionString = "DBQ=" & strDBService
strConnectionString = _
"DRIVER={Microsoft Access Driver (*.mdb)}; " & strConnectionString
End If

With objDB
.Mode = adModeReadWrite 'modalita di connessione ???
.ConnectionTimeout = 10 'Indica il tempo di attesa per stabilire
'la connessione prima di terminare i tentativi e generare un errore.

.CommandTimeout = 5 ' secondi timeout per eseguire qualsiasi comando
.CursorLocation = adUseClient ' tipo di cursore ???
.Open strConnectionString ' apre la connessione al database

End With

Set dbConnection = objDB
End Function

Function CreateRecordSet(ByRef dbConn As ADODB.Connection, _
ByRef rs As ADODB.Recordset, ByVal method As RSMethod, _
Optional strSQL As String, Optional TableName As String) _
As ADODB.Recordset

' chiude il recordset se gia aperto...

If rs.State=1 Then
rs.close
End If
Select Case method
Case RSMethod.NEW_RECORD
rs.ActiveConnection = dbConn
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseServer
rs.Open TableName
Case RSMethod.EDIT_RECORD
rs.ActiveConnection = dbConn
rs.Source = strSQL
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Open
' Debug.Print "SQL Statement in EDIT Mod

' e (Createrecordset) : " & strSQL

' Debug.Print "Found " & rs.RecordCount

' & " records"

Case RSMethod.VIEW_RECORD
rs.ActiveConnection = dbConn 'dbConnection 'dbConn
rs.Source = strSQL
rs.CursorType = adOpenForwardOnly
rs.CursorLocation = adUseClient
rs.Open
' Debug.Print "Found " & rs.RecordCount

' & " records"

rs.ActiveConnection = Nothing
Case RSMethod.EXEC_SQL
Set rs = dbConn.Execute(strSQL)
End Select
Set CreateRecordSet = rs
End Function

'======================================

'fine del modulo

'======================================

'======================================

'subroutines...

'======================================

Sub Add_New_Record()
Dim objRecSet As New ADODB.Recordset
Dim objConn As New ADODB.Connection
Dim strUserID As String
Dim strPassword As String
Dim strTableName As String
Dim strDBType As String
Dim strDBName As String

strTableName = "YOURTABLE"
strPassword = "YourPassword"
strUserID = "YourUserID"

If strDBType = "MSACCESS" Then
'strDBName e' il nome del Database

strDBName = App.Path & "\AccessDB.mdb"
ElseIf strDBType = "ORACLE" Then
'strDBName e' il nome del servizio Oracle

strDBName = "YOUR_ORACLE_SERVICE_NAME"
strTableName = strUserID & "." & strTableName
'ormato nome della tabella ::> USERID.TABLENAME

Else
MsgBox "Il Database non e' ORACLE o Access"
Exit Sub
End If

Set objConn = dbConnection(strDBType, strDBName, "userid", "password")
Set objRecSet = CreateRecordSet(objConn, objRecSet, NEW_RECORD, , strTableName)
objConn.BeginTrans
With objRecSet
.AddNew
.Fields("FIELD1").Value = " valore1"
.Fields("FIELD2").Value = " valore2"
.Fields("FIELD3").Value = " valore3"
.Fields("FIELD4").Value = " valore4"
.Fields("FIELD5").Value = " valore5"
.Update
End With
If objConn.Errors.Count = 0 Then
objConn.CommitTrans
Else
objConn.RollbackTrans
End If

objRecSet.Close
objConn.Close
Set objRecSet = Nothing
Set objConn = Nothing
End Sub

Sub View_Record_Only()
Dim strSQL As String
Dim strDBName As String
Dim strDBType As String
Dim strUserID As String
Dim strPassword As String

Dim objRecSet As New ADODB.Recordset
Dim objConn As New ADODB.Connection
If strDBType = "MSACCESS" Then
strDBName = App.Path & "\YourAccessDB.mdb"

ElseIf strDBType = "ORACLE" Then
strDBName = "YOUR_ORACLE_SERVICE_NAME"
Else
MsgBox "Nessun Database ORACLE o Access"
Exit Sub
End If

strPassword = "YourPassword"
strUserID = "YourUserID"
strSQL = "SELECT * from USER_TABLE"

Set objConn = dbConnection(strDBType, strDBName, "userid", "password")
'disconnete il recordset

Set objRecSet = CreateRecordSet(objConn, objRecSet, VIEW_RECORD, strSQL)
objConn.Close
Set objConn = Nothing
'manipola recordset .....

'manipola recordset .....

'manipola recordset .....

objRecSet.Close
Set objRecSet = Nothing
End Sub

Sub Edit_Existing_Record()
Dim objRecSet As New ADODB.Recordset
Dim objConn As New ADODB.Connection
Dim strUserID As String
Dim strPassword As String
Dim strSQL As String
Dim strDBType As String
Dim strDBName As String

strTableName = "YOURTABLE"
strPassword = "YourPassword"
strUserID = "YourUserID"
If strDBType = "MSACCESS" Then
strDBName = App.Path & "\YourAccessDB.mdb"
ElseIf strDBType = "ORACLE" Then
strDBName = "YOUR_ORACLE_SERVICE_NAME"
Else
Exit Sub
End If
strSQL = "Select * from YOUR_TABLE"
Set objConn = dbConnection(strDBType, strDBName, "userid", "password")
Set objRecSet = CreateRecordSet(objConn, objRecSet, EDIT_RECORD, strSQL)

With objRecSet
.Fields("FIELD1").Value = " valore1"
.Update
End With
objRecSet.Close
objConn.Close
Set objRecSet = Nothing
Set objConn = Nothing
End Sub

'======================================

'Fine subroutines...

'======================================

'Codice da inserire in un Modulo

'

Option Explicit
Public Enum RSMethod
VIEW_RECORD = 0
EDIT_RECORD = 1
EXEC_SQL = 2
NEW_RECORD = 3
End Enum
Function dbConnection(strDatabaseType As String, strDBService _
As String, Optional strUserID As String, _
Optional strPassword As String) As ADODB.Connection

Dim objDB As New ADODB.Connection
Dim strConnectionString As String
If strDatabaseType = "ORACLE" Then
'Definisce la stringa di connessine al DB Oracle

strConnectionString = _
"Driver={Microsoft ODBC Driver For Oracle};ConnectString=" & _
strDBService & ";UID=" & strUserID & ";PWD=" & strPassword & ";"
ElseIf strDatabaseType = "MSACCESS" Then
'Definisce la stringa di connessione a Microsoft Access

strConnectionString = "DBQ=" & strDBService
strConnectionString = _
"DRIVER={Microsoft Access Driver (*.mdb)}; " & strConnectionString
End If

With objDB
.Mode = adModeReadWrite 'modalita di connessione ???
.ConnectionTimeout = 10 'Indica il tempo di attesa per stabilire
'la connessione prima di terminare i tentativi e generare un errore.

.CommandTimeout = 5 ' secondi timeout per eseguire qualsiasi comando
.CursorLocation = adUseClient ' tipo di cursore ???
.Open strConnectionString ' apre la connessione al database

End With

Set dbConnection = objDB
End Function

Function CreateRecordSet(ByRef dbConn As ADODB.Connection, _
ByRef rs As ADODB.Recordset, ByVal method As RSMethod, _
Optional strSQL As String, Optional TableName As String) _
As ADODB.Recordset

' chiude il recordset se gia aperto...

If rs.State=1 Then
rs.close
End If
Select Case method
Case RSMethod.NEW_RECORD
rs.ActiveConnection = dbConn
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseServer
rs.Open TableName
Case RSMethod.EDIT_RECORD
rs.ActiveConnection = dbConn
rs.Source = strSQL
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Open
' Debug.Print "SQL Statement in EDIT Mod

' e (Createrecordset) : " & strSQL

' Debug.Print "Found " & rs.RecordCount

' & " records"

Case RSMethod.VIEW_RECORD
rs.ActiveConnection = dbConn 'dbConnection 'dbConn
rs.Source = strSQL
rs.CursorType = adOpenForwardOnly
rs.CursorLocation = adUseClient
rs.Open
' Debug.Print "Found " & rs.RecordCount

' & " records"

rs.ActiveConnection = Nothing
Case RSMethod.EXEC_SQL
Set rs = dbConn.Execute(strSQL)
End Select
Set CreateRecordSet = rs
End Function

'======================================

'fine del modulo

'======================================

'======================================

'subroutines...

'======================================

Sub Add_New_Record()
Dim objRecSet As New ADODB.Recordset
Dim objConn As New ADODB.Connection
Dim strUserID As String
Dim strPassword As String
Dim strTableName As String
Dim strDBType As String
Dim strDBName As String

strTableName = "YOURTABLE"
strPassword = "YourPassword"
strUserID = "YourUserID"

If strDBType = "MSACCESS" Then
'strDBName e' il nome del Database

strDBName = App.Path & "\AccessDB.mdb"
ElseIf strDBType = "ORACLE" Then
'strDBName e' il nome del servizio Oracle

strDBName = "YOUR_ORACLE_SERVICE_NAME"
strTableName = strUserID & "." & strTableName
'ormato nome della tabella ::> USERID.TABLENAME

Else
MsgBox "Il Database non e' ORACLE o Access"
Exit Sub
End If

Set objConn = dbConnection(strDBType, strDBName, "userid", "password")
Set objRecSet = CreateRecordSet(objConn, objRecSet, NEW_RECORD, , strTableName)
objConn.BeginTrans
With objRecSet
.AddNew
.Fields("FIELD1").Value = " valore1"
.Fields("FIELD2").Value = " valore2"
.Fields("FIELD3").Value = " valore3"
.Fields("FIELD4").Value = " valore4"
.Fields("FIELD5").Value = " valore5"
.Update
End With
If objConn.Errors.Count = 0 Then
objConn.CommitTrans
Else
objConn.RollbackTrans
End If

objRecSet.Close
objConn.Close
Set objRecSet = Nothing
Set objConn = Nothing
End Sub

Sub View_Record_Only()
Dim strSQL As String
Dim strDBName As String
Dim strDBType As String
Dim strUserID As String
Dim strPassword As String

Dim objRecSet As New ADODB.Recordset
Dim objConn As New ADODB.Connection
If strDBType = "MSACCESS" Then
strDBName = App.Path & "\YourAccessDB.mdb"

ElseIf strDBType = "ORACLE" Then
strDBName = "YOUR_ORACLE_SERVICE_NAME"
Else
MsgBox "Nessun Database ORACLE o Access"
Exit Sub
End If

strPassword = "YourPassword"
strUserID = "YourUserID"
strSQL = "SELECT * from USER_TABLE"

Set objConn = dbConnection(strDBType, strDBName, "userid", "password")
'disconnete il recordset

Set objRecSet = CreateRecordSet(objConn, objRecSet, VIEW_RECORD, strSQL)
objConn.Close
Set objConn = Nothing
'manipola recordset .....

'manipola recordset .....

'manipola recordset .....

objRecSet.Close
Set objRecSet = Nothing
End Sub

Sub Edit_Existing_Record()
Dim objRecSet As New ADODB.Recordset
Dim objConn As New ADODB.Connection
Dim strUserID As String
Dim strPassword As String
Dim strSQL As String
Dim strDBType As String
Dim strDBName As String

strTableName = "YOURTABLE"
strPassword = "YourPassword"
strUserID = "YourUserID"
If strDBType = "MSACCESS" Then
strDBName = App.Path & "\YourAccessDB.mdb"
ElseIf strDBType = "ORACLE" Then
strDBName = "YOUR_ORACLE_SERVICE_NAME"
Else
Exit Sub
End If
strSQL = "Select * from YOUR_TABLE"
Set objConn = dbConnection(strDBType, strDBName, "userid", "password")
Set objRecSet = CreateRecordSet(objConn, objRecSet, EDIT_RECORD, strSQL)

With objRecSet
.Fields("FIELD1").Value = " valore1"
.Update
End With
objRecSet.Close
objConn.Close
Set objRecSet = Nothing
Set objConn = Nothing
End Sub

'======================================

'Fine subroutines...

'======================================