本文给大家展示一个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 |
精彩推荐：
|
|
|
