【ACCESS精品源码栏目提醒】:网学会员--在 ACCESS精品源码编辑为广大网友搜集整理了:ADO连接Oracle和Access示例及记录集处理源码 - 经营企划绩等信息,祝愿广大网友取得需要的信息,参考学习。
ADO 连接 Oracle/
Access 示例及记录集处理
源码Option ExplicitPublic Enum RSMethod VIEW_RECORD color0000ff 0 EDIT_RECORD 1 EXEC_SQL 2 NEW_RECORD 3End EnumFunction dbConnectionstrDatabaseType As String strDBService As String OptionalstrUserID 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 DriverMicrosoft ODBC Driver FororacleConnectString strDBService UID strUserID PWD strPassword ElseIf strDatabaseType MSACCESS Then 定义 Microsoft
Access 数据库连接字符串 strConnectionString DBQ strDBService strConnectionString DRIVERMicrosoft
Access Driver .mdb strConnectionString End If With objDB .Mode adModeReadWrite 连接模式 .ConnectionTimeout 10 超时 .CommandTimeout 5 .CursorLocation adUseClient .Open strConnectionString 打开数据库连接 End With Set dbConnection objDBEnd FunctionFunction CreateRecordSetByRef dbConn As ADODB.Connection ByRef rs AsADODB.Recordset ByVal method As RSMethod Optional strSQL As String OptionalTableName As String As ADODB.Recordset 如果打开应先关闭 ... If rs.State1 Then rs.close End If Select Case method Case RSMethod.NEW_RECORD rs.ActiveConnection dbConn rs.CursorType adOpenKeysetrs.LockType adLockOptimisticrs.CursorLocation adUseServerrs.Open TableNameCase RSMethod.EDIT_RECORDrs.ActiveConnection dbConnrs.Source strSQLrs.CursorType adOpenKeysetrs.LockType adLockOptimisticrs.CursorLocation adUseClientrs.Open Debug.Print SQL Statement in EDIT Mod e Createrecordset : strSQL Debug.Print Found rs.RecordCount recordsCase RSMethod.VIEW_RECORDrs.ActiveConnection dbConn dbConnection dbConnrs.Source strSQLrs.CursorType adOpenForwardOnlyrs.CursorLocation adUseClient rs.Open Debug.Print Found rs.RecordCount records rs.ActiveConnection Nothing Case RSMethod.EXEC_SQL Set rs dbConn.ExecutestrSQL End SelectSet CreateRecordSet rsEnd Function模块结束调用示例...Sub Add_New_Record Dim objRecSet As New ADODB.RecordsetDim objConn As New ADODB.ConnectionDim strUserID As StringDim strPassword As StringDim strTableName As StringDim strDBType As StringDim strDBName As StringstrTableName 你的表名称strPassword 密码strUserID 帐号If strDBType MSACCESS Then strDBName 是数据库名称 strDBName App.Path YourAccessDB.mdbElseIf 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 dbConnectionstrDBType strDBName userid password send NEW_RECORD and strTableName as a p art of parameters Set objRecSet CreateRecordSetobjConn objRecSet NEW_RECORD strTableName objConn.BeginTrans With objRecSet .AddNew .FieldsFIELD1.Value your value1 .FieldsFIELD2.Value your value2 .FieldsFIELD3.Value your value3 .FieldsFIELD4.Value your value4 .FieldsFIELD5.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 NothingEnd SubSub View_Record_Only Dim strSQL As String Dim strDBName As String Dim strDBType As String Dim strUserID As StringDim strPassword As StringDim objRecSet As New ADODB.RecordsetDim objConn As New ADODB.ConnectionIf strDBType MSACCESS Then strDBName is your Database Name strDBName App.Path YourAccessDB.mdbElseIf strDBType ORACLE Then strDBName is your oracle Service Name strDBName YOUR_ORACLE_SERVICE_NAMEElse MsgBox Database is other than orACLE or Microsoft Exit SubEnd IfstrPassword YourPasswordstrUserID YourUserID strSQL Select from USER_TABLE Set objConn dbConnectionstrDBType strDBName userid password create a disconnected recordset Set objRecSet CreateRecordSetobjConn 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 NothingEnd SubSub 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 StringDim strDBName As StringstrTableName YOURTABLEstrPassword YourPasswordstrUserID YourUserIDIf strDBType MSACCESS Then strDBName is your Database Name strDBName App.Path YourAccessDB.mdbElseIf strDBType ORACLE Then strDBName is your oracle Service Name strDBName YOUR_ORACLE_SERVICE_NAMEElse MsgBox Database is other than orACLE or Microsoft Exit SubEnd IfstrSQL Select from YOUR_TABLESet objConn dbConnectionstrDBType strDBName userid passwordsend EDIT_RECORD and strSQL as a part o f parameters Set objRecSet CreateRecordSetobjConn objRecSet EDIT_RECORD strSQL With objRecSet .FieldsFIELD1.Value your value1 .Update End With objRecSet.Close objConn.Close Set objRecSet Nothing Set objConn NothingEnd Sub示例结束...