• [공유] MS-Access DB(MDB) Transaction Log
    끄적이는/정보공유 2016. 8. 24. 15:33

    출처: http://accessprogrammer.blogspot.kr/2005/09/transaction-log-for-microsoft-access.html

    MSSQL의 경우 트랜젝션이라는 로그파일로 select, insert, update, delete 등의 로그를 확인 할 수 있고,

    트리거를 이용해 update, insert, delete 된 경우에 어떻게 처리할 것인지 무언가 작업을 할 수도 있다.

    그렇다면 MS-Access DB (.mdb)의 경우에도 insert, update, delete 했을 경우

    기존의 자료들을 로그형태나 백업형태로 확인하는 방법이 있을까?

    MDB에서는 원래 불가능 하나

    아래 코드를 참고해 Transaction Log를 만들면 어느정도 가능하다.

    자세한 설명은 http://accessprogrammer.blogspot.kr/2005/09/transaction-log-for-microsoft-access.html

    Public Function basLogTrans(Frm As Form, MyKeyName As Variant, MyKey As Variant) As Boolean

    'Michael Red 8/1/2002 Transaction log for Ms. Access BOUND forms.

    'The following three functions and table definitions are
    '(should be) sufficient to establish a 'poor mans' transaction
    'log for Ms. Access bound forms.

    'As noted in basAddHist, you will need to create the tables
    'for History (general fields) and Memo History (history of
    'JUST memo fields).

    'Also, note that these functions are designed to work in
    'conjunction with Ms. Access Bound forms where the view is
    'set to single form. Further, the history will only capture
    'the user if the db App is "secured".

    'In the following, "MyKey" refers to the Record ID (Unique value)
    'for the single record which the form is 'tied' to.

    Dim MyDb As DAO.Database
    Dim MyCtrl As Control
    Dim MyMsg As String
    Dim Hist As String

    'In the Statement block below, basFlgValidRec is a routine which
    'includes validation of all fields necessary to 'define' a record
    'is valid. Commented out here, as I do not know what validation
    'rules need to be checked/enforced.

    ' If (Not basFlgValidRec) Then
    ' basLogTrans = False 'Let User know of FAILURE!!
    ' Exit Sub
    ' End If

    For Each MyCtrl In Frm.Controls
    If (basActiveCtrl(MyCtrl)) Then 'Consider replacing w/ test for "ControlSource"
    If ((MyCtrl.Value <> MyCtrl.OldValue) _
    Or (IsNull(MyCtrl) And Not IsNull(MyCtrl.OldValue)) _
    Or (Not IsNull(MyCtrl) And IsNull(MyCtrl.OldValue))) Then
    If (MyCtrl.ControlType = dbMemo) Then
    Hist = "tblHistMemo"
    Hist = "tblHist"
    End If
    Call basAddHist(Hist, Frm.Name, MyKey.Name, MyCtrl)
    End If
    End If
    Next MyCtrl

    basLogTrans = True 'Let User know sucess

    End Function
    Public Function basActiveCtrl(Ctl As Control) As Boolean
    Select Case Ctl.ControlType

    Case Is = acTextBox
    basActiveCtrl = True

    Case Is = acLabel
    Case Is = acRectangle
    Case Is = acLine
    Case Is = acImage
    Case Is = acCommandButton
    Case Is = acOptionButton
    Case Is = acCheckBox
    basActiveCtrl = True
    Case Is = acOptionGroup
    Case Is = acBoundObjectFrame
    Case Is = acListBox
    basActiveCtrl = True
    Case Is = acComboBox
    basActiveCtrl = True
    Case Is = acSubform
    Case Is = acObjectFrame
    Case Is = acPageBreak
    Case Is = acPage
    Case Is = acCustomControl
    Case Is = acToggleButton
    Case Is = acTabCtl

    End Select
    End Function
    Public Function basAddHist(Hist As String, Frm As String, MyKeyName As String, MyCtrl As Control)

    'FrmName Text 80 Name of the form where change Occured
    'FldName Text 80 Field Name of the changed value
    'dtChg Date/Time 8 Date/Time of Change (MACHINE value!!)
    'OldVal Text 255 Field Value BEFORE change
    'NewVal Text 255 Field Value after change
    'UserId Text 50 User who Made Change
    'MyKey Variant ?? KeyField as Indentified by Caller
    'MyKeyName Text 80 'Key Field Contents

    'tblHistMemo is the same structure except the "type" for the fields
    'OldContents and NewContents are Memo (and therfore the length is "??")

    Dim dbs As DAO.Database
    Dim tblHistTable As DAO.Recordset

    Set dbs = CurrentDb
    Set tblHistTable = dbs.OpenRecordset(Hist, dbOpenDynaset)

    With tblHistTable
    !MyKey = Forms(Frm).Controls(MyKeyName)
    !MyKeyName = MyKeyName
    !frmName = Frm
    !FldName = MyCtrl.ControlSource
    !dtChg = Now()
    ' !UserId = Environ("Username") 'Orlando's Way
    !UserId =CurrentUser() 'Mike's way, example of current user would be Admin
    !OldVal = MyCtrl.OldValue
    !NewVal = MyCtrl
    End With

    End Function

    댓글 0

Designed by Tistory.