至顶网›软件频道 ›基础软件›ADO连接Oracle Access示例及记录集处理源码

ADO连接Oracle Access示例及记录集处理源码

扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条

本文给大家展示一个Visual Basic里，使用ADO连接Oracle、Access示例及记录集处理源码，大家可以参考一下。

作者：佚名来源：Mndsoft 2007年11月20日

本文给大家展示一个Visual Basic里，使用ADO连接Oracle、Access示例及记录集处理源码，大家可以参考一下。

以下是引用片段：
Option Explicit

Public Enum RSMethod
    VIEW_RECORD [color=#0000ff]= 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
        '定义 orACLE 数据库连接字符串
        strConnectionString = "Driver={Microsoft ODBC Driver For oracle};ConnectString=" & strDBService & ";UID=" & strUserID & ";PWD=" & strPassword & ";"
    ElseIf strDatabaseType = "MSACCESS" Then
        '定义 Microsoft Access 数据库连接字符串

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


    With objDB
        .Mode = adModeReadWrite  ' 连接模式 ???
        .ConnectionTimeout = 10  '超时
        .CommandTimeout = 5
        .CursorLocation = adUseClient
        .Open strConnectionString  '打开数据库连接

    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
    ' 如果打开应先关闭
    '     ...

    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
'======================================
'模块结束
'======================================

'=======================================
'======================================
'调用示例...
'======================================

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 = "你的表名称"
    strPassword = "密码"
    strUserID = "帐号"


    If strDBType = "MSACCESS" Then
        ' strDBName 是数据库名称
        strDBName = App.Path & "\YourAccessDB.mdb"

    ElseIf strDBType = "ORACLE" Then
        ' strDBName 是Oracle 服务器名称
        strDBName = "你的Oracle 服务器名称"
        strTableName = strUserID & "." & strTableName
        'Table name format ::> USERID.TABLENA
        '     ME
    Else
        MsgBox "Database is other than orACLE or Microsoft"
        Exit Sub
    End If

    Set objConn = dbConnection(strDBType, strDBName, "userid", "password")
    'send NEW_RECORD and strTableName as a p
    '     art of parameters
    Set objRecSet = CreateRecordSet(objConn, objRecSet, NEW_RECORD, , strTableName)

    objConn.BeginTrans

    With objRecSet
        .AddNew
        .Fields("FIELD1").Value = "your value1"
        .Fields("FIELD2").Value = "your value2"
        .Fields("FIELD3").Value = "your value3"
        .Fields("FIELD4").Value = "your value4"
        .Fields("FIELD5").Value = "your value5"
        .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 is your Database Name
        strDBName = App.Path & "\YourAccessDB.mdb"

    ElseIf strDBType = "ORACLE" Then
        ' strDBName is your oracle Service Name
        strDBName = "YOUR_ORACLE_SERVICE_NAME"

    Else
        MsgBox "Database is other than orACLE or Microsoft"
        Exit Sub
    End If

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

    Set objConn = dbConnection(strDBType, strDBName, "userid", "password")
    'create a disconnected recordset
    Set objRecSet = CreateRecordSet(objConn, objRecSet, VIEW_RECORD, strSQL)
    objConn.Close
    Set objConn = Nothing
    'manipulate the recordset here.....
    'manipulate the recordset here.....
    'manipulate the recordset here.....
    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 is your Database Name
        strDBName = App.Path & "\YourAccessDB.mdb"

    ElseIf strDBType = "ORACLE" Then
        ' strDBName is your oracle Service Name
        strDBName = "YOUR_ORACLE_SERVICE_NAME"
    Else
        MsgBox "Database is other than orACLE or Microsoft"
        Exit Sub
    End If
    strSQL = "Select * from YOUR_TABLE"
    Set objConn = dbConnection(strDBType, strDBName, "userid", "password")
    'send EDIT_RECORD and strSQL as a part o
    '     f parameters
    Set objRecSet = CreateRecordSet(objConn, objRecSet, EDIT_RECORD, strSQL)


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

查看本文来源

VIP专区

VIP用户

普通用户

邮件订阅

如果您非常迫切的想了解IT领域最新产品与技术信息，那么订阅至顶网技术邮件将是您的最佳途径之一。

重磅专题

往期文章

ADO连接Oracle Access示例及记录集处理源码

业界热点: