فصل ۲۵ — ایجاد ردپای ممیزی (Audit Trail) روی جدول‌ها

حتی ساده‌ترین پایگاه‌داده می‌تواند در گذر زمان مقدار زیادی داده در خود نگه دارد؛ داده‌هایی که با افزودن رکوردهای جدید و ویرایش یا حذف رکوردهای موجود دائماً تغییر می‌کنند. شاید این تغییرات همیشه پیامد بزرگی نداشته باشند، اما در بسیاری از کاربردها لازم است بدانیم چه کسی، در چه زمانی و دقیقاً چه چیزی را تغییر داده است. نویسنده توضیح می‌دهد که در برنامه‌هایی که برای گزارش‌دهی به نهادهای نظارتی بیرونی نوشته، وجود ردپای ممیزی اهمیت اساسی داشته است.

قابلیت ثبت تغییرات در فیلدهای Memo در Access می‌تواند تاریخ و زمان تغییر را نگه دارد، اما به‌تنهایی مشخص نمی‌کند چه کسی تغییر را انجام داده یا چه چیزی تغییر کرده است. برای ثبت داستان کامل تغییرات باید این قابلیت را با VBA سفارشی کرد.

کاربر چه کسی است؟

اولین اطلاعات لازم، هویت کاربری است که تغییر را انجام داده است. با یک فراخوانی API می‌توان کاربر واقعی ویندوز را که برنامه را اجرا و داده را تغییر می‌دهد به‌دست آورد. در VBE از مسیر Insert | Module یک Module جدید بسازید و Declaration زیر را در بخش General قرار دهید:

Public Declare Function GetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

این Declaration به تابع GetUserName در کتابخانهٔ پویا advapi32.dll که بخشی از Windows است متصل می‌شود.

Public Function ReturnUserName()
Dim strUser As String, X As Integer
strUser = Space$(256)
X = GetUserName(strUser, 256)
strUser = RTrim(strUser)
ReturnUserName = Left(strUser, Len(strUser) - 1)
End Function

تابع عمومی ReturnUserName همیشه Login ویندوز کاربر جاری را برمی‌گرداند و چون Public است، در تمام Moduleهای Form قابل استفاده است.

ردپای ممیزی در ساختار جدول

برای مثال از پایگاه‌دادهٔ Northwind و جدول Customers استفاده می‌شود. در هر جدولی که به Audit Trail نیاز دارد، یک فیلد اضافه برای نگهداری اطلاعات ممیزی بسازید. جدول را در Design mode باز کنید و فیلدی از نوع Memo با نام AuditTrail اضافه و طراحی را ذخیره کنید.

نوع Memo انتخاب شده تا بتوان تمام تغییرات را ثبت کرد. این انتخاب ممکن است اندازهٔ پایگاه‌داده را زیاد کند؛ در صورت نیاز می‌توان از Text استاندارد استفاده کرد، ولی ظرفیت آن کمتر است.

استفاده از Eventها برای ایجاد Audit Trail

جدول منبع دادهٔ یک Form خواهد بود. در Northwind، فرم Customers List نمونهٔ مناسبی است. فرم را در Design mode باز کنید، Build Event و سپس Code Builder را انتخاب کنید. در Module فرم، از Drop-down سمت چپ Form و از سمت راست AfterUpdate را انتخاب و کد زیر را وارد کنید:

Dim RecSet As Recordset, Temp As Variant
Set RecSet = CurrentDb.OpenRecordset("select * from Customers where ID=" & Me.ID)
Temp = RecSet!AuditTrail
Temp = Temp & "Edit" & "|" & ReturnUserName & "|" & Now() & "|"
CurrentDb.Execute ("update Customers set AuditTrail='" & Temp & "' where ID="  _
&  Me.ID)

دو متغیر ساخته می‌شود: یک Recordset و یک Variant. دلیل استفاده از Variant این است که در ابتدا ممکن است مقدار AuditTrail برابر Null باشد و String مقدار Null را نمی‌پذیرد. Recordset با ID رکورد جاری به جدول Customers اشاره می‌کند. تا وقتی ID در Data Source فرم وجود داشته باشد، می‌توان با Me.ID به آن اشاره کرد، حتی اگر Control جداگانه‌ای برای آن روی فرم وجود نداشته باشد.

مقدار فعلی AuditTrail در Temp بارگذاری می‌شود؛ سپس نام Action یعنی Edit، نام کاربر و تاریخ/زمان جاری با جداکنندهٔ خط عمودی | به آن افزوده می‌شوند. در پایان یک Update Query مقدار جدید را در جدول می‌نویسد.

همین Procedure باید در Eventِ BeforeInsert نیز قرار گیرد؛ فقط نام Action از Edit به Insert تغییر کند. پس از چند تغییر در فرم، با مشاهدهٔ فیلد AuditTrail می‌توان دید چه کسی، چه کاری و در چه زمانی انجام داده است.

اکنون دلیل استفاده از Memo روشن می‌شود: Text فقط 255 کاراکتر ظرفیت دارد و در جدولی با Updateهای زیاد سریع پر می‌شود. اگر مجبور به استفاده از Text هستید، مقدار قبلی را Concatenate نکنید و فقط آخرین رویداد را ذخیره کنید:

Dim Temp As Variant
Temp = Temp & "Edit" & "|" & ReturnUserName & "|" & Now() & "|"
CurrentDb.Execute ("update Customers set AuditTrail='" & Temp & "' where ID="  _
&  Me.ID)

مشکل حذف رکورد این است که Audit Trail نیز همراه رکورد از بین می‌رود. راه‌حل پیشنهادی این است که Property فرم با نام Allow Deletions را روی No قرار دهید، یک فیلد Boolean (Yes/No) به نام Deleted بسازید و به‌جای حذف واقعی، دکمهٔ Delete سفارشی قرار دهید:

CurrentDb.Execute ("update Customers set Deleted=True where ID="  _
&  Me.ID)

Query فرم باید فقط رکوردهایی را نشان دهد که Deleted=False است. کد Audit Trail را نیز روی دکمهٔ Delete قرار دهید و Action را به Delete تغییر دهید.

غنی‌تر کردن Audit Trail

برای جزئیات بیشتر می‌توان Snapshot مقادیر Fieldهای فرم را نیز در Audit Trail ثبت کرد:

Private Sub Form_AfterUpdate()
Dim RecSet As Recordset, Temp As Variant
Set RecSet = CurrentDb.OpenRecordset("select * from Customers where ID=" & Me.ID)
Temp = RecSet!AuditTrail
Temp = Temp & "Edit" & "|" & ReturnUserName & "|" _
& Now() & "|" & Me.First_Name & "|" & Me.Last_Name & "|" & Me.E_mail_Address
CurrentDb.Execute ("update Customers set AuditTrail='" & Temp & "' where ID=" _
& Me.ID)
End Sub

اگر Form فیلدهای زیادی داشته باشد رشتهٔ ممیزی طولانی و شلوغ می‌شود، اما برای بعضی برنامه‌ها همین سطح از Granularity ضروری است.

فصل ۲۶ — ایجاد و ویرایش Queryها در VBA

SQL Queryها در Access ابزار اصلی مشاهده و به‌روزرسانی داده‌اند. در پایگاه‌دادهٔ رابطه‌ای به‌ندرت می‌توان دادهٔ معنادار را تنها از یک Table به‌دست آورد؛ Queryها جدول‌ها را Join می‌کنند یا برای Update، Delete و Append رکوردها استفاده می‌شوند. هنگام کار با Form گاهی لازم است Query تازه‌ای برای عملیات مشخص ساخته شود یا SQL آن متناسب با انتخاب کاربر تغییر کند.

ایجاد Query جدید

VBA اجازه می‌دهد Query جدیدی به مجموعهٔ QueryDefs اضافه شود. مثال بر اساس جدول Orders در Northwind است:

Sub CreateQuery()
Dim Qd As New QueryDef, Qds As QueryDefs
Set Qds = CurrentDb.QueryDefs
Qd.Name = "MyNewQuery"
Qd.SQL = "Select * from orders"
Qds.Append Qd
Application.RefreshDatabaseWindow
Set Qd = Nothing
Set Qds = Nothing
End Sub

فرض بر این است که Queryای با نام MyNewQuery از قبل وجود ندارد. Qd تعریف Query جدید و Qds مجموعهٔ QueryDefs را نگه می‌دارد. نام و SQL تعیین، Query به Collection افزوده و Database Window Refresh می‌شود تا در Navigation pane دیده شود.

حذف Query موجود

Sub RemoveQuery()
CurrentDb.QueryDefs.Delete "MyNewQuery"
Application.RefreshDatabaseWindow
End Sub

این کد Query را بدون پیام هشدار حذف می‌کند؛ بنابراین باید با احتیاط استفاده شود.

به‌روزرسانی SQL یک Query

Sub ChangeQuery()
CurrentDb.QueryDefs("MyNewQuery").SQL = _
"update orders set customer ='Company Unknown' where customer='Company D'"
Application.RefreshDatabaseWindow
End Sub

با تغییر Propertyِ SQL حتی نوع Query نیز می‌تواند تغییر کند. پس از اجرای مثال، Query در Navigation pane با آیکن Update نمایش داده می‌شود. نمونهٔ Delete Query:

Sub ChangeQuery()
CurrentDb.QueryDefs("MyNewQuery").SQL = _
"delete * from orders where customer='Customer D'"
Application.RefreshDatabaseWindow
End Sub

همچنین Propertyِ ODBC timeout را می‌توان تغییر داد. این زمان مشخص می‌کند اگر داده‌ای دریافت نشود Query چه مدت منتظر بماند. مقدار پیش‌فرض 60 ثانیه است و برای External Table یا Server کند ممکن است لازم باشد افزایش یابد:

Sub SetTimeout()
CurrentDb.QueryDefs("MyNewQuery").ODBCTimeout = 120
End Sub

صفحهٔ پایانی این بخش در نسخهٔ اصلی عمداً خالی است.