فصل ۲۷ — جست‌وجو و جایگزینی در Queryها

ساختار Queryها در Applicationهای بزرگ Access می‌تواند بسیار پیچیده شود، به‌خصوص وقتی Queryها روی Queryهای دیگر لایه‌بندی می‌شوند. ممکن است بیش از صد Query در یک Database وجود داشته باشد و پیگیری Tableها و Subqueryهای مورد استفاده دشوار شود.

اگر نام Table یا Field عوض شود—چیزی که در Linked Tableهای خارجی کاملاً محتمل است—پیداکردن همهٔ Queryهای وابسته می‌تواند دردسر بزرگی باشد. همچنین با تغییر نیازهای کاربران ممکن است بعضی Tableها زائد شوند و Developer باید بداند چه Queryهایی به آن‌ها وابسته‌اند. برای این کار می‌توان Utilityهای VBA نوشت.

جست‌وجوی یک String مشخص در تمام Queryها

در Northwind ابتدا Table کوچکی برای نگهداری نتیجه‌ها بسازید: یک Field از نوع Text با نام QueryName و بدون Primary Key؛ Table را با نام tblSearchResults ذخیره کنید. سپس:

Sub SearchQueries()
Dim RecSet As Recordset, Qdef As QueryDef, SearchStr As String
CurrentDb.Execute "delete * from tblSearchResults"
Set RecSet = CurrentDb.OpenRecordset("tblSearchResults")
SearchStr = "orders"
For Each Qdef In CurrentDb.QueryDefs
If InStr(Qdef.SQL, SearchStr) Then
RecSet.AddNew
RecSet!QueryName = Qdef.Name
RecSet.Update
End If
Next Qdef
Set RecSet = Nothing
Set Qdef = Nothing
End Sub

Table نتایج ابتدا پاک می‌شود. Recordset روی آن باز و Search String برابر orders قرار داده می‌شود؛ این مقدار می‌تواند هر نام Table، Query یا Field دیگری باشد. Loop از تمام QueryDefها عبور می‌کند و InStr SQL هر Query را بررسی می‌کند. جست‌وجو در Access نسبت به Case حساس نیست. هر Query شامل String جست‌وجو به tblSearchResults افزوده می‌شود.

نویسنده هشدار می‌دهد حذف Query یا Table در Application Access بسیار خطرناک است، حتی اگر مطمئن باشید زائد شده است. روش امن‌تر این است که نام آن را موقتاً با پیشوند zz تغییر دهید؛ مثلاً qryMyQuery به zzqryMyQuery. سپس یک دورهٔ مناسب—مثلاً یک ماه—صبر کنید. اگر مشکلی پیش آمد با حذف zz شیء سریعاً برگردانده می‌شود؛ در غیر این صورت می‌توان آن را حذف نهایی کرد.

Search and Replace در یک Query

جست‌وجو و جایگزینی باید هر بار فقط روی یک Query انجام شود، زیرا جایگزینی سراسری در صورت اشتباه می‌تواند Application را به‌شدت خراب کند. یکی از روش‌های دستی، Copy کردن کل SQL به Word، Find/Replace و بازگرداندن SQL است؛ VBA می‌تواند این کار را سریع‌تر انجام دهد.

Sub SearchReplaceQueries()
Dim QName As String, SearchStr As String, ReplaceStr As String
Dim Temp As String
QName = "MyNewQuery"
SearchStr = "Company D"
ReplaceStr = "Company B"
Temp = CurrentDb.QueryDefs(QName).SQL
For n = 1 To Len(Temp)
X = InStr(n, Temp, SearchStr)
If X Then
Temp = Left(Temp, X - 1) & ReplaceStr & Mid(Temp, X + Len(ReplaceStr))
End If
Next n
CurrentDb.QueryDefs(QName).SQL = Temp
End Sub

پیش از اجرای کد بهتر است از Query هدف Copy تهیه شود. Stringهای نام Query، متن جست‌وجو و متن جایگزین مقداردهی می‌شوند و SQL در Temp قرار می‌گیرد. Loop با Start Position متحرک، تمام نمونه‌های Search String را پیدا و Replace String را در محل صحیح وارد می‌کند. این روش برای Queryهای طولانی و پیچیده از ویرایش دستی سریع‌تر و کم‌خطاتر است.

فصل ۲۸ — استفاده از تابع DateAdd

Access تابعی فراهم می‌کند که یک فاصلهٔ زمانی/تاریخی را به Date/Time موجود اضافه یا از آن کم می‌کند و تاریخ/زمان جدید را می‌دهد. برای مثال اگر User ماه و سال شروع را انتخاب کند و گزارش باید داده‌های n ماه بعد از روز اول آن ماه را پوشش دهد، محاسبهٔ تعداد روزهای ماه‌ها با Lookup Table پیچیده می‌شود؛ DateAdd این محاسبه را مستقیم انجام می‌دهد.

Function TestDateAdd(Target, Months As Integer)
Dim Temp As String
If IsDate(Target) = False Then
TestDateAdd = "Invalid date"
Exit Function
End If
Temp = Right(Target, 2)
If Temp <= 29 Then
Temp = "20" & Temp
Else
Temp = "19" & Temp
End If
Target = Month(Target) & "/" & Temp
TestDateAdd = DateAdd("m", Months + 1, Target)
TestDateAdd = DateAdd("d", -1, TestDateAdd)
End Function

پارامتر Target عمداً Date تعریف نشده و Variant است؛ چون ممکن است User فرمتی وارد کند که VBA نفهمد. در غیر این صورت Type Mismatch پیش از شروع Function رخ می‌دهد و فرصتی برای Error Trapping باقی نمی‌ماند.

IsDate ابتدا اعتبار تاریخ را بررسی می‌کند؛ برای نمونه «Next week» رد می‌شود و Function عبارت Invalid date را برمی‌گرداند. نکتهٔ جالب این است که 31-Nov-09 ممکن است معتبر تلقی شود اما سال اشتباه تفسیر گردد، در حالی که 31-Nov-2009 به‌درستی نامعتبر تشخیص داده می‌شود.

برای حل مشکل سال دو رقمی، دو کاراکتر انتهایی بررسی می‌شود: اگر ≤29 باشد پیشوند 20 و در غیر این صورت 19 اضافه می‌شود. سپس Target به Month و سال چهاررقمی تبدیل می‌شود و Day برای DateAdd به روز اول ماه Default می‌شود. برای یافتن آخرین روز ماه نهایی، یک ماه اضافه‌تر افزوده می‌شود تا به روز اول ماه بعد برسیم و سپس یک روز کم می‌شود.

نویسنده می‌گوید مستقل از Locale، اگر تاریخ با قالب محلی وارد شود، نتیجه آخرین روزِ ماهی است که n ماه جلوتر قرار دارد. حتی ورودی 31-Nov-09 در مثال به 01-Nov-2009 تفسیر می‌شود و با Months=3 نتیجهٔ 28-Feb-2010 خواهد بود.

استفاده از DateAdd برای Pause کردن Code

Function AccessWait(Target As Long)
TimeWait = DateAdd("s", Target, Time)
Do Until Time >= TimeWait
Loop
End Function

فراخوانی X = AccessWait(10) پردازش را 10 ثانیه متوقف می‌کند. Function تعداد ثانیه‌ها را به زمان جاری اضافه و در TimeWait نگه می‌دارد، سپس با Do..Until تا رسیدن Time جاری به TimeWait پردازش را نگه می‌دارد. این یک روش ساده و مؤثر برای Pause کردن Code است.

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