ADO连接Oracle access示例及记录集处理源码
时 间:2008-01-18 09:11:42
作 者:UMVSOFT整理 ID:16 城市:江阴
摘 要: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 |
Access软件网QQ交流群 (群号:483923997) Access源码网店
常见问答:
技术分类:
源码示例
- 【源码QQ群号19834647...(12.17)
- 【Access小作品】简单的待...(04.30)
- 【Access窗体导出PDF】...(04.08)
- 【Access窗体导出PDF】...(04.07)
- Access两种方式实现即时更...(03.01)
- Access隐藏与显示lacc...(01.12)
- 【Access高效办公】将每个...(12.23)
- Access21点游戏源代码(12.13)
- 【Access窗体导出Exce...(11.15)
- 【Access开发】Acces...(11.14)
学习心得
最新文章
- 【Access小作品】简单的待办任...(04.30)
- 从另一个ACCESS数据库批量导入...(04.29)
- Access日期格式的数据导出Ex...(04.28)
- ACCESS精华集锦资料.CHM(04.25)
- Access VBA语句If Me...(04.24)
- 【Access修改记录示例】编辑选...(04.22)
- 【Access表名称命名建议】将A...(04.20)
- Access学习笔记--用Acce...(04.19)
- 【Access重复项查询示例】将A...(04.17)
- Access快速开发平台企业版--...(04.16)