moduledb.bas

用VB实现连接oracle817数据月报数输入并统计

Attribute VB_Name = "ModuleDB"
'执行一条sql语句，不返回记录集
Public Function fExecSql(strSql As String) As Boolean

fExecSql = False

Set COMM = New ADODB.Command
   
   COMM.ActiveConnection = CONN
   CONN.BeginTrans
   
   On Error GoTo errorHandle
   COMM.CommandText = strSql
   COMM.Execute
   
   CONN.CommitTrans
   fExecSql = True
   Exit Function

errorHandle:
    MsgBox Err.Description
    CONN.RollbackTrans
    Screen.MousePointer = vbDefault
    Exit Function

End Function

    '执行多条sql语句，不返回记录集
    Public Function fExecMidSql(arrSQL() As String, nSl As Long) As Boolean
    
    Dim i As Long
    Dim iCount As Long
    
    fExecMidSql = False

    Set COMM = New ADODB.Command

       COMM.ActiveConnection = CONN
       CONN.BeginTrans

       On Error GoTo errorHandle
       
       
       iCount = UBound(arrSQL)
       For i = 0 To nSl
       
         COMM.CommandText = arrSQL(i)
         COMM.Execute
       Next i
  
       
       CONN.CommitTrans
       fExecMidSql = True
       Exit Function

errorHandle:
        MsgBox Err.Description
        CONN.RollbackTrans
        Screen.MousePointer = vbDefault
        Exit Function

    End Function

'执行sql语句，返回记录集
Public Function fGetRS(ByVal tmpSql As String, _
        ByRef tmpArry As Variant, _
        ByRef tmpHaveRs As Boolean) As Boolean

  Dim m_ADOCN  As New ADODB.Connection
  Dim m_ADORs As New ADODB.Recordset
  Dim iCount As Long
  
  iCount = 0

Start:
  
  fGetRS = False
  
 
   On Error GoTo errHandle
   
   m_ADOCN.Open CONN.ConnectionString, , PWD
   m_ADOCN.CommandTimeout = 0
   m_ADORs.Open tmpSql, m_ADOCN, adOpenStatic, adLockReadOnly
   If Not (m_ADORs.EOF And m_ADORs.BOF) Then
     tmpArry = m_ADORs.GetRows()
     tmpHaveRs = True
   Else
     tmpHaveRs = False
     
   End If

   m_ADORs.Close
   m_ADOCN.Close
  
   Set m_ADORs = Nothing
   Set m_ADOCN = Nothing
   fGetRS = True
  
   Exit Function
  
errHandle:
   Set m_ADORs = Nothing
   Set m_ADOCN = Nothing
   If Not fConnServer And iCount < 1 Then
      iCount = iCount + 1
      GoTo Start
   End If
      Err.Raise APP_ERROR, "财务月报.数据服务.打开数据游标", Err.Description
End Function