Attribute VB_Name = "Module1"
'變量要求聲明
Option Explicit

'Access數據庫文件的名稱
Public gFile As String

'數據庫連接對象
Public gCon As New ADODB.Connection

'數據庫集合對象
Public gRst As New ADODB.Recordset
'Excel程序對象
Public gX As Excel.Application
   '
   '打開數據庫文件
Public Sub OpenDBFile()
   
   '打開錯誤處理陷阱
   
   Dim intErrFileNo As Integer  '自由文件號
   On Error GoTo ErrGoto
   '----------------------------------------------------
    gCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & gFile & ";Mode=ReadWrite;Persist Security Info=False"
   '----------------------------------------------------
   Exit Sub
   '-----------------------------
ErrGoto:
   '把錯誤信息保存在文件里
   intErrFileNo = FreeFile()
   Open "YFSystem.ini" For Append As intErrFileNo
   Print #intErrFileNo, Chr(34) + Format(Now, "YYYY-MM-DD HH:MM:SS") + Chr(34), Chr(34) + "信息" + Chr(34), Chr(34) + Err.Description + Chr(34), Chr(34) + "OpenDBFile(Module1)" + Chr(34), Chr(34) + App.Title + Chr(34)
   Close #intErrFileNo
End Sub

   '
   '關閉數據庫連接
   '
Public Sub CloseDBFile()
   '打開錯誤處理陷阱
   Dim intErrFileNo As Integer  '自由文件號
   On Error GoTo ErrGoto
   '----------------------------------------------------
    gCon.Close
   '----------------------------------------------------
   Exit Sub
   '-----------------------------
ErrGoto:
   '把錯誤信息保存在文件里
   intErrFileNo = FreeFile()
   Open "YFSystem.ini" For Append As intErrFileNo
   Print #intErrFileNo, Chr(34) + Format(Now, "YYYY-MM-DD HH:MM:SS") + Chr(34), Chr(34) + "信息" + Chr(34), Chr(34) + Err.Description + Chr(34), Chr(34) + "CloseDBFile(Module1)" + Chr(34), Chr(34) + App.Title + Chr(34)
   Close #intErrFileNo
End Sub

'打開數據集
Public Sub OpenRS(SQL As String)
    '打開錯誤處理陷阱
    Dim intErrFileNo As Integer  '自由文件號
    On Error GoTo ErrGoto
    '----------------------------------------------------
    OpenDBFile
    gRst.Open SQL, gCon, adOpenStatic, adLockOptimistic
    
    '----------------------------------------------------
    Exit Sub
    '-----------------------------
ErrGoto:
    '把錯誤信息保存在文件里
    intErrFileNo = FreeFile()
    Open "YFSystem.ini" For Append As intErrFileNo
    Print #intErrFileNo, Chr(34) + Format(Now, "YYYY-MM-DD HH:MM:SS") + Chr(34), Chr(34) + "信息" + Chr(34), Chr(34) + Err.Description + Chr(34), Chr(34) + "OpenRS(Module1)" + Chr(34), Chr(34) + App.Title + Chr(34)
    Close #intErrFileNo
End Sub

'關閉打開的數據集合
Public Sub CloseRS()
    '打開錯誤處理陷阱
    Dim intErrFileNo As Integer  '自由文件號
    On Error GoTo ErrGoto
    '----------------------------------------------------
    gRst.Close
    CloseDBFile
    '----------------------------------------------------
    Exit Sub
    '-----------------------------
ErrGoto:
    '把錯誤信息保存在文件里
    intErrFileNo = FreeFile()
    Open "YFSystem.ini" For Append As intErrFileNo
    Print #intErrFileNo, Chr(34) + Format(Now, "YYYY-MM-DD HH:MM:SS") + Chr(34), Chr(34) + "信息" + Chr(34), Chr(34) + Err.Description + Chr(34), Chr(34) + "CloseRS(Module1)" + Chr(34), Chr(34) + App.Title + Chr(34)
    Close #intErrFileNo
End Sub