'模块描述 :ACCESS / orACLE ADO 连接和记录集操作 '************************************** '提示:要引用 Microsoft ActiveX Data Object 2.X 对象库 '**************************************'====================================== '模块开始 '====================================== Option Explicit Public Enum RSMethod VIEW_RECORD = 0 EDIT_RECORD = 1 EXEC_SQL = 2 NEW_RECORD = 3 End Enum 'dbConnection函数 Function dbConnection(strDatabaseType As String, strDBService As String, Optional strUserID As String, Optional strPassword As String) As ADODB.ConnectionDim objDB As New ADODB.Connection Dim strConnectionString As StringIf strDatabaseType = "ORACLE" Then '定义ORACLE数据库连接字符串 strConnectionString = "Driver={Microsoft ODBC Driver for oracle};ConnectString=" & strDBService & ";UID=" & strUserID & ";PWD=" & strPassword & ";" ElseIf strDatabaseType = "MSACCESS" Then '定义Access数据库连接字符串 strConnectionString = "DBQ=" & strDBService strConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)}; " & strConnectionString End IfWith objDB .Mode = adModeReadWrite ' 连接模式 .ConnectionTimeout = 10 .CommandTimeout = 5 .CursorLocation = adUseClient .Open strConnectionStringEnd WithSet 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.Recordsetif 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 TableNameCase 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_RECORDrs.ActiveConnection = dbConn 'dbConnection 'dbConn rs.Source = strSQL rs.CursorType = adOpenForwardOnly rs.CursorLocation = adUseClient rs.Open ' Debug.Print "Found " & rs.RecordCount ' & " records" rs.ActiveConnection = NothingCase 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 StringstrTableName = "YOURTABLE" strPassword = "YourPassword" strUserID = "YourUserID"If strDBType = "MSACCESS" Then ' strDBName 字符串是你的数据库名称 strDBName = App.Path & "\YourAccessDB.mdb"ElseIf strDBType = "ORACLE" Then ' strDBName 字符串是Oracle 服务器名称 strDBName = "YOUR_ORACLE_SERVICE_NAME" strTableName = strUserID & "." & strTableName Else MsgBox "数据库不符合ORACLE 或者 Microsoft" Exit Sub End IfSet objConn = dbConnection(strDBType, strDBName, "userid", "password") 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 IfobjRecSet.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 StringDim objRecSet As New ADODB.Recordset Dim objConn As New ADODB.ConnectionIf strDBType = "MSACCESS" Then strDBName = App.Path & "\YourAccessDB.mdb"ElseIf strDBType = "ORACLE" Then strDBName = "YOUR_ORACLE_SERVICE_NAME"Else MsgBox "数据库不符合ORACLE 或者 Microsoft" Exit Sub End IfstrPassword = "YourPassword" strUserID = "YourUserID" strSQL = "Select * from USER_TABLE"Set objConn = dbConnection(strDBType, strDBName, "userid", "password") Set objRecSet = CreateRecordSet(objConn, objRecSet, VIEW_RECORD, strSQL) objConn.Close Set objConn = Nothing 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 StringstrTableName = "YOURTABLE" strPassword = "YourPassword" strUserID = "YourUserID" If strDBType = "MSACCESS" Then strDBName = App.Path & "\YourAccessDB.mdb"ElseIf strDBType = "ORACLE" Then strDBName = "YOUR_ORACLE_SERVICE_NAME" Else MsgBox "数据库不符合ORACLE 或者 Microsoft" 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 = "your value1" .Update End With objRecSet.Close objConn.Close Set objRecSet = Nothing Set objConn = Nothing End Sub
'**************************************
'提示:要引用 Microsoft ActiveX Data Object 2.X 对象库
'**************************************'======================================
'模块开始
'======================================
Option Explicit
Public Enum RSMethod
VIEW_RECORD = 0
EDIT_RECORD = 1
EXEC_SQL = 2
NEW_RECORD = 3
End Enum
'dbConnection函数
Function dbConnection(strDatabaseType As String, strDBService As String, Optional strUserID As String, Optional strPassword As String) As ADODB.ConnectionDim objDB As New ADODB.Connection
Dim strConnectionString As StringIf strDatabaseType = "ORACLE" Then
'定义ORACLE数据库连接字符串
strConnectionString = "Driver={Microsoft ODBC Driver for oracle};ConnectString=" & strDBService & ";UID=" & strUserID & ";PWD=" & strPassword & ";"
ElseIf strDatabaseType = "MSACCESS" Then
'定义Access数据库连接字符串
strConnectionString = "DBQ=" & strDBService
strConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)}; " & strConnectionString
End IfWith objDB
.Mode = adModeReadWrite ' 连接模式
.ConnectionTimeout = 10
.CommandTimeout = 5
.CursorLocation = adUseClient
.Open strConnectionStringEnd WithSet 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.Recordsetif 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 TableNameCase 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_RECORDrs.ActiveConnection = dbConn 'dbConnection 'dbConn
rs.Source = strSQL
rs.CursorType = adOpenForwardOnly
rs.CursorLocation = adUseClient
rs.Open
' Debug.Print "Found " & rs.RecordCount
' & " records"
rs.ActiveConnection = NothingCase 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 StringstrTableName = "YOURTABLE"
strPassword = "YourPassword"
strUserID = "YourUserID"If strDBType = "MSACCESS" Then
' strDBName 字符串是你的数据库名称
strDBName = App.Path & "\YourAccessDB.mdb"ElseIf strDBType = "ORACLE" Then
' strDBName 字符串是Oracle 服务器名称
strDBName = "YOUR_ORACLE_SERVICE_NAME"
strTableName = strUserID & "." & strTableName
Else
MsgBox "数据库不符合ORACLE 或者 Microsoft"
Exit Sub
End IfSet objConn = dbConnection(strDBType, strDBName, "userid", "password")
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 IfobjRecSet.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 StringDim objRecSet As New ADODB.Recordset
Dim objConn As New ADODB.ConnectionIf strDBType = "MSACCESS" Then
strDBName = App.Path & "\YourAccessDB.mdb"ElseIf strDBType = "ORACLE" Then
strDBName = "YOUR_ORACLE_SERVICE_NAME"Else
MsgBox "数据库不符合ORACLE 或者 Microsoft"
Exit Sub
End IfstrPassword = "YourPassword"
strUserID = "YourUserID"
strSQL = "Select * from USER_TABLE"Set objConn = dbConnection(strDBType, strDBName, "userid", "password")
Set objRecSet = CreateRecordSet(objConn, objRecSet, VIEW_RECORD, strSQL)
objConn.Close
Set objConn = Nothing
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 StringstrTableName = "YOURTABLE"
strPassword = "YourPassword"
strUserID = "YourUserID"
If strDBType = "MSACCESS" Then
strDBName = App.Path & "\YourAccessDB.mdb"ElseIf strDBType = "ORACLE" Then
strDBName = "YOUR_ORACLE_SERVICE_NAME"
Else
MsgBox "数据库不符合ORACLE 或者 Microsoft"
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 = "your value1"
.Update
End With
objRecSet.Close
objConn.Close
Set objRecSet = Nothing
Set objConn = Nothing
End Sub