کوئری پارامتری در SQL اکسس با QueryDef و VBA

ساخت SQL با چسباندن مقدارهای فرم در ابتدا ساده به نظر می‌رسد، اما تاریخ، apostrophe، جداکننده اعشاری، Null و فیلتر اختیاری رشته را شکننده می‌کنند. کوئری پارامتری در SQL اکسس مقدار را از ساختار دستور جدا می‌کند و نوع داده را قابل پیش‌بینی می‌سازد.

در این درس تعریف PARAMETERS، تخصیص مقدار با DAO QueryDef، نوع تاریخ و Currency، فیلتر اختیاری، اعتبارسنجی، نتیجه خالی و پاک‌سازی اشیا بررسی می‌شود. همچنین prompt تعاملی از پارامتر نام‌دار مخصوص VBA تفکیک می‌شود.

هدف فقط امنیت نیست. پارامتر نوع‌دار صحت، خوانایی، استفاده مجدد، آزمون و عیب‌یابی را بهتر می‌کند. QueryDef ذخیره‌شده را می‌توان مستقیم باز کرد، به فرم و گزارش داد یا از VBA اجرا کرد، بدون اینکه هر بار SQL بازسازی شود.

جایگاه این درس در مسیر آموزش SQL اکسس

پارامتری‌کردن پس از SELECT، WHERE، JOIN، تجمیع و زیرکوئری قرار می‌گیرد، زیرا یک کوئری ثابت و تأییدشده را به جزء قابل استفاده مجدد تبدیل می‌کند. منطق SQL باید پیش از افزودن ورودی پویا درست باشد.

QueryDef را مانند تابع ببینید: ورودی نام‌دار، خروجی مشخص و فرض‌های مستند دارد. تمام پارامترها را صریح تعریف کنید، نام پایدار بدهید، مقدار را پیش از تخصیص اعتبارسنجی کنید و ارجاع مخفی به کنترل فرم را داخل کوئری عمومی باقی نگذارید.

پیش‌نیازها و مدل داده نمونه

در مثال‌ها از جدول‌های Customers، Orders، OrderDetails، Products و Categories استفاده می‌شود. CustomerID کلید اصلی مشتری، OrderID کلید اصلی سفارش و ProductID کلید اصلی محصول است. فیلدهای کلید خارجی باید با کلید اصلی متناظر نوع داده سازگار داشته باشند و در پایگاه‌های بزرگ برای join و فیلترهای پرتکرار ایندکس شوند.

پیش از اجرای مثال‌ها از فایل اکسس نسخه پشتیبان تهیه کنید و نام جدول‌ها و فیلدها را با پایگاه خود تطبیق دهید. اگر نامی فاصله دارد آن را داخل کروشه قرار دهید. ابتدا یک SELECT ساده با تعداد رکورد محدود بسازید و سپس clauseها، پارامترها یا عبارت‌های پیشرفته را مرحله‌به‌مرحله اضافه کنید.

ساختار اصلی و مدل ذهنی

کوئری پارامتری مقدارها را هنگام اجرا دریافت می‌کند و آن‌ها را داخل متن SQL نمی‌چسباند. نام و نوع داده در PARAMETERS تعریف می‌شود، نام‌ها در SQL به‌کار می‌روند و VBA پس از دریافت QueryDef مقدار تمام پارامترهای لازم را تعیین می‌کند.

PARAMETERS [pStartDate] DateTime,
           [pEndDate] DateTime,
           [pCustomerID] Long;
SELECT OrderID, OrderDate, CustomerID, TotalAmount
FROM Orders
WHERE OrderDate Between [pStartDate] And [pEndDate]
  AND CustomerID = [pCustomerID]
ORDER BY OrderDate, OrderID;

دستور را بر اساس جریان منطقی داده تحلیل کنید: ابتدا سطرهای منبع مشخص می‌شوند، سپس فیلتر سطح رکورد اجرا می‌شود، داده در صورت نیاز ترکیب یا گروه‌بندی می‌شود، ستون‌های خروجی محاسبه می‌شوند، شرط سطح نتیجه اعمال می‌شود و در پایان مرتب‌سازی صورت می‌گیرد. این مدل نشان می‌دهد چرا جابه‌جایی یک شرط می‌تواند نتیجه را تغییر دهد.

کاربردهای مناسب

• فیلتر گزارش بر اساس تاریخ و مشتری
• ارسال مقدار فرم بدون اتصال رشته SQL
• اجرای کوئری تجمیعی یا زیرکوئری قابل استفاده مجدد
• اجرای action query با ورودی اعتبارسنجی‌شده
• اشتراک یک کوئری میان فرم و گزارش و VBA

نمونه کاربردی با قواعد واقعی

مثال پیشرفته بازه تاریخ، حداقل مبلغ و وضعیت اختیاری می‌گیرد. اگر pStatus خالی باشد تمام وضعیت‌ها پذیرفته می‌شوند و در غیر این صورت فیلتر اعمال می‌شود. مقدار به‌عنوان داده مقایسه می‌شود و وارد ساختار SQL نمی‌شود.

PARAMETERS [pStartDate] DateTime,
           [pEndDate] DateTime,
           [pMinimumAmount] Currency,
           [pStatus] Text (20);
SELECT O.OrderID,
       O.OrderDate,
       C.CustomerName,
       O.TotalAmount,
       O.Status
FROM Customers AS C
INNER JOIN Orders AS O
    ON C.CustomerID = O.CustomerID
WHERE O.OrderDate Between [pStartDate] And [pEndDate]
  AND O.TotalAmount >= [pMinimumAmount]
  AND ([pStatus] = '' OR O.Status = [pStatus])
ORDER BY O.OrderDate DESC, O.OrderID DESC;

بخش PARAMETERS مانع حدس نوع توسط Access می‌شود. VBA ترتیب تاریخ را پیش از تخصیص کنترل و هر مقدار را با نام تعیین می‌کند. SQL خوانا می‌ماند و وجود apostrophe در متن ساختار دستور را نمی‌شکند.

اجرای کوئری از VBA

برای اتوماسیون قابل نگهداری، SQL را به‌صورت یک QueryDef نام‌دار ذخیره و از DAO اجرا کنید. پارامتر صریح از اتصال متن به رشته SQL بهتر است، زیرا Access نوع داده را پیش از اجرا می‌شناسد. روال زیر الگوی عمومی را نشان می‌دهد؛ نام QueryDef و پارامترها را با کوئری ذخیره‌شده همین درس هماهنگ کنید.

Option Explicit

Public Sub OpenFilteredOrders()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim startDate As Date
    Dim endDate As Date

    On Error GoTo CleanFail

    startDate = DateSerial(2026, 1, 1)
    endDate = DateSerial(2026, 12, 31)
    If startDate > endDate Then Err.Raise vbObjectError + 1000, , "Invalid date range"

    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryFilteredOrders")
    qdf.Parameters("pStartDate") = startDate
    qdf.Parameters("pEndDate") = endDate
    qdf.Parameters("pMinimumAmount") = 250@
    qdf.Parameters("pStatus") = "Completed"

    Set rs = qdf.OpenRecordset(dbOpenSnapshot)
    Do While Not rs.EOF
        Debug.Print rs!OrderID, rs!CustomerName, rs!TotalAmount
        rs.MoveNext
    Loop

CleanExit:
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub

CleanFail:
    Debug.Print Err.Number, Err.Description
    Resume CleanExit
End Sub

در این نمونه Recordset از نوع snapshot برای خواندن نتیجه باز می‌شود. برای INSERT، UPDATE یا DELETE باید از روش اجرای action query استفاده و RecordsAffected را کنترل کنید. بخش پاک‌سازی نیز باید اشیا را در حالت موفق و خطا ببندد تا اتصال یا Recordset باز باقی نماند.

روش کنترل نتیجه

1. کوئری منبع را بدون clause پیشرفته اجرا و تعداد سطرهای اولیه را ثبت کنید.
2. یک حالت کوچک را دستی محاسبه و با خروجی SQL مقایسه کنید.
3. پارامتری وارد کنید که نتیجه خالی بدهد و رفتار کد فراخوان را بررسی کنید.
4. یک مقدار Null یا رکورد تکراری آزمایشی اضافه و انطباق نتیجه با قاعده مستند را کنترل کنید.
5. تعداد رکوردهای QueryDef ذخیره‌شده را با Recordset بازشده در VBA مقایسه کنید.
6. نام و نوع ستون‌های نهایی مورد نیاز گزارش یا export را ثبت کنید.

خطاهای رایج و روش اصلاح

کارایی، ایمنی و نگهداری

برای اجرای مطمئن کوئری پارامتری در SQL اکسس بهتر است ابتدا یک نسخه آزمایشی از پایگاه داده بسازید و چند رکورد با مقادیر قابل تشخیص در آن قرار دهید. کوئری را پیش از اتصال به فرم، گزارش یا کد VBA مستقیماً در محیط Query Design اجرا کنید. این روش خطای طراحی SQL را از خطای اتوماسیون جدا می‌کند و بررسی نتیجه را ساده‌تر می‌سازد.

زبان SQL اکسس به SQL استاندارد نزدیک است، اما در نشانه‌گذاری تاریخ، wildcardها، پارامترها و موتور عبارت‌ها تفاوت‌هایی دارد. هنگام کار با کوئری پارامتری در SQL اکسس ابتدا دستور را در خود Access آزمایش کنید. پس از تأیید نتیجه، همان منطق را به QueryDef ذخیره‌شده یا رشته SQL در VBA منتقل کنید تا خطاهای نقل‌قول و نوع پارامتر زودتر آشکار شوند.

برای فیلدهای محاسباتی alias روشن انتخاب کنید. نام مستعار باید معنای خروجی را نشان دهد، نه اینکه فقط عبارت را تکرار کند. alias مناسب اتصال گزارش، خواندن Recordset در VBA و نگهداری پروژه را آسان می‌کند. اگر نام فیلد فاصله دارد یا با واژه رزروشده تداخل پیدا می‌کند، آن را داخل کروشه قرار دهید.

رفتار مقدار Null را از ابتدا مشخص کنید. توابع تجمیعی، شرط‌ها، joinها و عبارت‌های محاسباتی همگی Null را یکسان مدیریت نمی‌کنند. ابتدا تعیین کنید Null در منطق کسب‌وکار به معنی مقدار ناشناخته، نامرتبط یا صفر است. تابع Nz را فقط زمانی به‌کار ببرید که جایگزینی Null با مقدار پیش‌فرض واقعاً درست باشد.

اعتبارسنجی ورودی را از اجرای کوئری جدا کنید. تاریخ‌ها، شناسه‌ها و حدود عددی باید پیش از تخصیص به پارامتر بررسی شوند. متن واردشده توسط کاربر را در صورت وجود پارامتر به SQL نچسبانید. پارامترها علاوه بر کاهش خطاهای قالب تاریخ و جداکننده اعشاری، مشکل apostrophe در متن را نیز کنترل می‌کنند.

کارایی را با داده‌ای نزدیک به حجم واقعی اندازه بگیرید. کوئری‌ای که روی بیست رکورد سریع است ممکن است روی صدها هزار رکورد کند شود. فیلدهای پرتکرار در join و فیلتر انتخابی را ایندکس کنید، روی فیلد ایندکس‌شده تابع غیرضروری اعمال نکنید و فقط ستون‌هایی را برگردانید که مرحله بعد واقعاً لازم دارد.

برنامه آزمون باید داده معمولی، مقدار مرزی، Null، رکورد تکراری و نتیجه خالی را پوشش دهد. بررسی کنید اگر هیچ رکوردی پیدا نشد، اگر یک گروه فقط یک عضو داشت یا اگر دو سطر مقدار مرتب‌سازی یکسان داشتند، خروجی همچنان قابل پیش‌بینی باشد. نتیجه مورد انتظار را پیش از بازنویسی کوئری ثبت کنید.

کوئری ذخیره‌شده بخشی از مستندات پروژه است. برای QueryDef نام توصیفی انتخاب کنید، در ماژول VBA یک توضیح کوتاه درباره ورودی و خروجی بنویسید و هر کوئری را بر یک مسئولیت متمرکز نگه دارید. چنین کوئری‌ای مستقیماً قابل آزمایش است و می‌تواند میان فرم، گزارش و کد مشترک باشد.

اگر خروجی کوئری برای گزارش مهم یا عملیات تغییر داده استفاده می‌شود، پارامترها و تعداد رکوردهای برگردانده‌شده را ثبت کنید. لازم نیست داده حساس در log ذخیره شود. زمان اجرا، نام کوئری، خلاصه پارامترها و تعداد سطرها معمولاً برای عیب‌یابی کافی است.

کوئری پارامتری در SQL اکسس را بخشی از یک زنجیره پردازش داده ببینید. ورودی، شکل خروجی و مصرف‌کننده نتیجه باید روشن باشد. اینکه خروجی برای نمودار، گزارش، export به Excel یا مرحله ویرایش داده استفاده می‌شود، روی aliasها، ترتیب، سیاست Null و میزان جزئیات اثر مستقیم دارد.

تمرین مرحله‌به‌مرحله

تمرین را در سه نسخه بسازید. در نسخه اول فقط سطرهای خام را با join و فیلتر ضروری برگردانید. در نسخه دوم تکنیک اصلی این درس را اضافه و نتیجه را با داده کم کنترل کنید. در نسخه سوم پارامتر، alias، مرتب‌سازی و روال VBA را بیفزایید. هر نسخه را موقتاً با نام جدا ذخیره کنید تا علت هر تغییر در خروجی مشخص باشد.

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

در پایان کوئری را از دید توسعه‌دهنده دیگری بازبینی کنید. آیا بدون بازکردن فرم‌ها می‌توان جدول‌های ورودی، پارامترها، ستون‌های خروجی و سطح هر سطر را تشخیص داد؟ aliasهای مبهم را اصلاح، ستون‌های بلااستفاده را حذف و در ماژول VBA یک توضیح کوتاه درباره فرض‌ها ثبت کنید.

درس‌های مرتبط این مجموعه

این درس پس از مباحث SELECT، WHERE، JOIN و ویرایش داده قرار می‌گیرد. برای مرور عملیات تغییر رکورد می‌توانید مقاله ویرایش و حذف داده‌ها در SQL اکسس با VBA را بخوانید. پیشنهادهای لینک داخلی، درس‌های بعدی این مجموعه را نیز تا زمان انتشار با وضعیت planned نگه می‌دارند.

جمع‌بندی

پس از این درس می‌توانید رابط QueryDef را طراحی، نوع پارامتر را انتخاب، ورودی را کنترل، مقدار را از VBA تعیین، خطا و نتیجه خالی را مدیریت و زمان استفاده از QueryDef موقت را تشخیص دهید.

روش قابل اعتماد، ساخت تدریجی است: SQL را با داده کنترل‌شده ثابت کنید، نوع پارامترها را صریح بنویسید، رفتار Null و داده تکراری را بیازمایید و سپس اتوماسیون را اضافه کنید. با این ترتیب، تغییر جدول‌ها، فرم‌ها یا نیاز گزارش‌گیری هزینه کمتری خواهد داشت.