Access开发培训
网站公告
·Access专家课堂QQ群号:151711184    ·Access快速开发平台下载地址及教程    ·欢迎加入Access专家课堂微信群!    ·如何快速搜索本站文章|示例|资料    
您的位置: 首页 > 技术文章 > Access数据库-模块/函数/VBA

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

时 间:2008-01-18 09:11:42
作 者:UMVSOFT整理   ID:16  城市:江阴
摘 要:ADO连接Oracle Access示例及记录集处理源码
正 文:

本文给大家展示一个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


Access软件网QQ交流群 (群号:54525238)       Access源码网店

常见问答:

技术分类:

相关资源:

专栏作家

关于我们 | 服务条款 | 在线投稿 | 友情链接 | 网站统计 | 网站帮助