فصل ۱۲ — Queryهای SQL

Queryهای SQL بخشی از زبان VBA نیستند، اما فهم نحوهٔ کار و توانایی‌های آن‌ها بسیار مهم است. هدف این فصل نشان‌دادن Queryهایی است که می‌توانید همراه VBA استفاده کنید و شیوهٔ استفاده از آن‌ها را توضیح می‌دهد.

در یک پایگاه‌دادهٔ رابطه‌ای، بسیار غیرمعمول است که فقط یک Table را باز کنید و اطلاعات کاملاً معناداری از آن ببینید. هر Table بخشی از اطلاعات را نگه می‌دارد و فقط با Join کردن Tableهاست که دید کامل‌تری از داده حاصل می‌شود. برای مثال، یک Table نام و نشانی مشتریان و Table دیگر سفارش‌های همان مشتریان را نگه می‌دارد. برای استخراج سفارش‌های هر مشتری، باید Queryای بسازید که دو Table را بر اساس یک Key Field مشترک، معمولاً یک ID عددی، به هم وصل کند.

Queryهای SQL شریان حیاتی برنامه‌های Access برای ارائه و دستکاری داده‌اند و می‌توان آن‌ها را به روش‌های مختلف از VBA به کار گرفت. بعضی مثال‌های فصل از Database نمونهٔ Northwind استفاده می‌کنند. Access را بدون بازکردن Database خاص اجرا کنید، Sample Templates را انتخاب و Northwind را Create کنید؛ برای جلوگیری از Autorun هنگام بازشدن، کلید SHIFT را نگه دارید. پس از Load، Navigation Pane را برای دیدن Objectهای Database بازتر کنید.

استفاده از Query Design Window

در Access می‌توان Queryها را با Query Design Window و رابط گرافیکی آن ساخت. از Create روی Query Design کلیک کنید.

در پنجرهٔ Show Table، Tableها و Queryهای موردنیاز را انتخاب، Add و سپس Close کنید. بعداً با راست‌کلیک در Query Design و انتخاب Show Table می‌توانید موارد بیشتری اضافه کنید. برای این مثال فقط Customers و Orders را انتخاب کنید.

Tableها به شکل گرافیکی با فهرست Fieldها ظاهر می‌شوند و می‌توان آن‌ها را جابه‌جا کرد. Access تلاش می‌کند Joinهای مناسب را نیز خودکار بسازد، اما همیشه درست نیست و نباید بدون بررسی به آن تکیه کنید.

در این مثال Access میان ID در Customers و Customer ID در Orders یک Join ساخته است که صحیح است. همچنین رابطهٔ One-to-Many را تشخیص داده؛ یعنی یک مشتری می‌تواند چند سفارش داشته باشد و این رابطه با نمادهای 1 و ∞ روی Join Line نشان داده می‌شود.

اگر بخواهید Fieldهای دیگری را Join کنید، روی Join Line راست‌کلیک و Delete را انتخاب کنید، سپس Field یک Table را روی Field متناظر Table دیگر Drag کنید. میان دو Table می‌توان بیش از یک Join ساخت و یک Query می‌تواند Tableهای زیادی داشته باشد، هرچند Performance افت می‌کند و ساختار شبیه «تار عنکبوت» و Debug آن دشوار می‌شود.

Access در این نمونه یک Right Join در نظر گرفته تا همهٔ سفارش‌ها، حتی بدون Customer متناظر، نمایش داده شوند. فلش کوچک در انتهای Join Line این نوع Join را نشان می‌دهد.

روی Join Line راست‌کلیک و Join Properties را باز کنید.

می‌توانید Inner Join مستقیم، Left Join یا Right Join را انتخاب کنید. در Outer Join همهٔ Recordهای یک طرف و فقط Recordهای برابر از طرف دیگر بازمی‌گردند. در این مثال Right Join همهٔ Orders را نشان می‌دهد و هر جا ممکن باشد Customer متناظر را اضافه می‌کند.

برای اینکه Query خروجی داشته باشد، Fieldهایی را به بخش Results اضافه کنید. می‌توانید نام Field را Double-click یا به Row با نام Field بکشید. Company، Order Date و Ship Name را اضافه کنید. در Criteria زیر Order Date بنویسید:

>#01/01/2006#

علامت # در ابتدا و انتها نشان می‌دهد مقدار Date است. ورود تاریخ مطابق Locale ویندوز انجام می‌شود؛ در آمریکای شمالی mm/dd/yyyy و در بسیاری از Localeهای دیگر dd/mm/yyyy.

نکتهٔ مهم این است که SQL پشت GUI، تاریخ را در صورت ورود به شکل dd/mm/yyyy به قالب mm/dd/yyyy تبدیل می‌کند، مستقل از Locale. هنگام ساخت SQL داخل VBA باید این نکته را در نظر بگیرید.

Query را Run کنید، سپس از View گزینهٔ SQL View را ببینید. SQL ساخته‌شده چنین است:

SELECT Customers.Company, Orders.[Order Date], Orders.[Ship Name]
FROM Customers RIGHT JOIN Orders ON Customers.ID = Orders.[Customer ID]
WHERE (((Orders.[Order Date])>#1/1/2006#));

Query را با نام MyQuery ذخیره کنید. همین SQL چیزی است که می‌توانید در VBA استفاده کنید. اگر SQL را خوب می‌شناسید، می‌توانید مستقیماً در SQL View بنویسید، اما GUI احتمال اشتباه را کاهش می‌دهد و تغییرات SQL را نیز منعکس می‌کند.

برای Query پیچیده، به‌ویژه با چند Table، بهتر است ابتدا در Query Design آن را آزمایش کنید؛ هم نتیجهٔ عملی Query را می‌بینید و هم پیام‌های خطای مفیدتری نسبت به اجرای مستقیم در VBA دریافت می‌کنید. Queryهای بسیار پیچیده، به‌خصوص با رابطهٔ Many-to-Many، ممکن است Updatable نباشند و نتوان دادهٔ خروجی آن‌ها را از Result Window یا VBA تغییر داد.

Select Query

ساده‌ترین نوع Query است:

Select * from MyTable

* یعنی همهٔ Fieldها و در نتیجه تمام دادهٔ MyTable بازگردانده می‌شود. می‌توانید Inner/Outer Join، Criteria و Sort Order نیز اضافه کنید.

چون Select Query Record برمی‌گرداند، برای کار با آن در VBA معمولاً Recordset باز می‌شود. در VBE با ALT+F11 Module جدید بسازید:

Sub TestQuery()
Dim ReSet As Recordset
Set ReSet = CurrentDb.OpenRecordset("MyQuery")
Do Until ReSet.EOF
    MsgBox ReSet!Company
    ReSet.MoveNext
Loop
End Sub

کد فرض می‌کند Query با نام MyQuery ذخیره شده است. Recordset با نام ReSet ساخته و به Query متصل می‌شود. حلقهٔ Do Until ... Loop میان Recordها حرکت می‌کند و Field با نام Company را نمایش می‌دهد. توجه کنید برای دسترسی به Field از علامت ! استفاده شده است.

وجود MoveNext ضروری است؛ بدون آن Loop هیچ‌وقت از Record نخست عبور نمی‌کند و برنامه پایان نمی‌یابد.

می‌توان از Recordset برای Update استفاده کرد، هرچند Update Query معمولاً کارآمدتر است:

Sub TestQuery()
Dim ReSet As Recordset
Set ReSet = CurrentDb.OpenRecordset("MyQuery")
Do Until ReSet.EOF
    If ReSet![Ship Name] = "Karen Toh" Then
        ReSet.Edit
        ReSet![Ship Name] = "Unknown"
        ReSet.Update
    End If
    ReSet.MoveNext
Loop
End Sub

چون Ship Name فاصله دارد، نام Field داخل [] قرار گرفته است. اگر مقدار Karen Toh باشد Record وارد Edit Mode، مقدار به Unknown تغییر و با Update ذخیره می‌شود.

افزودن Record جدید:

Sub TestQuery()
Dim ReSet As Recordset
Set ReSet = CurrentDb.OpenRecordset("MyQuery")
ReSet.AddNew
ReSet!Company = "Richard"
ReSet![Order Date] = "01-Aug-2006"
ReSet![Ship Name] = "Richard Shepherd"
ReSet.Update
End Sub

AddNew Record جدید ایجاد می‌کند، Fieldها مقدار می‌گیرند و سپس Update آن را ذخیره می‌کند. Date به قالبی نوشته شده که با Localeهای مختلف سازگار باشد. تمام Rules مربوط به Tableها، مانند Required Fieldها، باید رعایت شوند وگرنه Error رخ می‌دهد. وقتی Query روی Form استفاده شده باشد بسیاری از عملیات Select، Browse، Update و Add Record از قبل توسط Form فراهم است، اما گاهی Customization مستقیم لازم می‌شود.

Union Query

Union گونه‌ای از Select Query است که نتیجهٔ چند Select را با هم ترکیب می‌کند. باید آن را مستقیماً در SQL View نوشت و Query Design GUI برای ساخت کامل آن قابل استفاده نیست:

Select * from MyTable1 union select * from MyTable2

هر Select باید تعداد ستون یکسان و برای هر ستون Data Type سازگار داشته باشد. UNION Recordهای تکراری را حذف می‌کند؛ برای حفظ Duplicateها از UNION ALL استفاده کنید:

Select * from MyTable1 union all select * from MyTable2

Union زمانی مفید است که دادهٔ عددی از چند Source با یک Reference مشترک باید در ستون‌های جدا ترکیب شود. فرض کنید MyTable1 دارای MyRef، Monday و Tuesday و MyTable2 دارای MyRef، Wednesday و Thursday است:

Select MyRef,Monday,Tuesday,0 as Wednesday,0 as Thursday from MyTable1
Union
Select MyRef,0 as Monday,0 as Tuesday,Wednesday,Thursday from MyTable2

صفرهای اضافه‌شده باعث می‌شوند هر دو Select ساختار ستونی یکسان داشته باشند. خروجی هنوز برای هر MyRef دو Row خواهد داشت؛ یکی دادهٔ Monday/Tuesday و دیگری Wednesday/Thursday. با Nested Query و Group By می‌توان آن‌ها را به یک Row تبدیل کرد:

Select MyRef,sum(Monday),sum(Tuesday),sum(Wednesday),sum(Thursday) from (
    Select MyRef,Monday,Tuesday,0 as Wednesday,0 as Thursday from MyTable1
    Union
    Select MyRef,0 as Monday,0 as Tuesday,Wednesday,Thursday from MyTable2
) group by MyRef

با پیچیده‌ترشدن SQL، می‌توانید Union Query را جداگانه با نامی مانند MyUnionQuery ذخیره و سپس Query گروه‌بندی را ساده‌تر بنویسید:

Select MyRef,sum(Monday),sum(Tuesday),sum(Wednesday),sum(Thursday)
from MyUnionQuery
group by MyRef

Delete Query

Delete Query Recordها را از Table حذف می‌کند؛ همهٔ Recordها یا فقط موارد مطابق Criteria. چون Record برنمی‌گرداند، به‌صورت Command اجرا می‌شود:

Delete * from MyTable

برای حذف انتخابی:

Delete * from MyTable where CustomerName="Richard"

در Query Design می‌توانید نوع Query را Delete کنید. پیش از اجرای واقعی، Datasheet View را ببینید تا مشخص شود چه Recordهایی حذف خواهند شد؛ اگر Criteria اشتباه باشد هنوز Database آسیب ندیده است.

اجرای Query ذخیره‌شده از VBA:

Sub DeleteQuery()
DoCmd.SetWarnings False
DoCmd.OpenQuery "MyDeleteQuery"
DoCmd.SetWarnings True
End Sub

SetWarnings False پیام تأیید Delete را موقتاً خاموش می‌کند. فراموش نکنید بعداً Warningها را دوباره روشن کنید؛ در غیر این صورت حتی پیام‌هایی مانند «آیا تغییرات Query ذخیره شود؟» نیز ظاهر نمی‌شوند و Access ممکن است به شکل پیش‌فرض Save کند، که در صورت تغییرات نادرست خطرناک است.

با Execute نیز می‌توان SQL را مستقیم اجرا کرد:

CurrentDb.Execute "delete * from MyTable"

به یاد داشته باشید حذف Recordها فضای فایل Database را تا زمان Compact آزاد نمی‌کند. اگر Compact انجام نشود، فایل می‌تواند پیوسته رشد کند تا به محدودیت 2GB برسد و دچار مشکل شود.

Make Table Query

Make Table Query بر اساس نتیجهٔ Query یک Table جدید ایجاد می‌کند. اگر Table هم‌نام وجود داشته باشد، با هشدارهای مربوط جایگزین می‌شود. ساده‌ترین شکل:

SELECT * INTO MyNewTable
FROM MyTable;

با Criteria:

SELECT * INTO MyNewTable
FROM MyTable
WHERE CustomerName="Richard"

در Query Design، Query Type را Make Table انتخاب کنید. اجرای ذخیره‌شده از VBA:

Sub MakeTableQuery()
DoCmd.SetWarnings False
DoCmd.OpenQuery "MyMakeTableQuery"
DoCmd.SetWarnings True
End Sub

یا SQL مستقیم:

CurrentDb.Execute "SELECT * INTO MyNewTable FROM MyTable;"

همان هشدار قبلی دربارهٔ روشن‌کردن دوبارهٔ Warningها پس از اجرا برقرار است.

Append Query

Append Query Recordهای Query را به Table دیگری اضافه می‌کند. داده باید با Rules جدول مقصد سازگار باشد؛ Data Type Fieldها باید متناظر و Required Fieldها مقداردهی شده باشند. Debug کردن Appendهای ناموفق، به‌خصوص با Fieldهای زیاد، می‌تواند دشوار باشد. Error Recordها ممکن است در Table خطا جمع شوند، اما یافتن علت همچنان سخت است. نویسنده پیشنهاد می‌کند Query را با Criteria به چند بخش کوچک تقسیم کنید تا بخش مشکل‌دار محدود و علت پیدا شود.

ساده‌ترین شکل:

INSERT INTO NewCustomers
SELECT *
FROM MyTable;

این Query Recordها را حذف نمی‌کند؛ هر بار اجرا، NewCustomers بزرگ‌تر می‌شود. با Criteria:

INSERT INTO NewCustomers
SELECT *
FROM MyTable
WHERE CustomerName="Richard"

می‌توانید Fieldهای مشخص را نیز تعیین کنید:

INSERT INTO NewCustomers (Company, CustomerName)
SELECT Company, CustomerName
FROM MyTable

نام Fieldها در مقصد می‌تواند متفاوت باشد، اما SQL باید نگاشت را درست و به ترتیب یکسان بیان کند. Data Typeها نیز باید سازگار باشند. این نوع Query می‌تواند به‌سرعت پیچیده و مستعد خطا شود.

در Query Design نوع Append را انتخاب و پیش از اجرای واقعی، Datasheet View را برای بررسی Recordهایی که قرار است اضافه شوند مشاهده کنید.

اجرای Query از VBA:

Sub MakeTableQuery()
DoCmd.SetWarnings False
DoCmd.OpenQuery "MyAppendQuery"
DoCmd.SetWarnings True
End Sub

یا با Execute:

CurrentDb.Execute "INSERT INTO NewCustomers SELECT * FROM MyTable"

برای SQL طولانی نمی‌توان Continuation Character را داخل یک String به کار برد؛ از String Variable و Concatenation استفاده کنید:

Src = " INSERT INTO NewCustomers "
Src = Src & "SELECT * FROM MyTable"
CurrentDb.Execute Src

اگر در Criteria رشته‌ای به Quote نیاز دارید، داخل SQL String از Single Quote استفاده کنید تا با Double Quoteهای String VBA تداخل پیدا نکند.

Update Query

Update Query Recordها را به مقدار مشخص تغییر می‌دهد. دادهٔ جدید باید Rules Table را رعایت کند؛ Null را نمی‌توان در Required Field قرار داد و Data Typeها باید سازگار باشند.

ساده‌ترین شکل:

UPDATE MyTable SET CustomerName = "Richard"

این مقدار CustomerName را در همهٔ Recordها به Richard تبدیل می‌کند. معمولاً Criteria لازم است:

UPDATE MyTable SET CustomerName = "Richard"
WHERE (((CustomerName)="Shepherd"))

چند Field و چند Criteria نیز ممکن است:

UPDATE MyTable SET CustomerName = "Richard", Company = "MGH"
WHERE (((CustomerName)="Shepherd") AND ((Company)="RBS"))

در این مثال فقط Recordهایی که CustomerName برابر Shepherd و Company برابر RBS دارند تغییر می‌کنند. در Query Design نوع Update را انتخاب و پیش از اجرا Datasheet View را برای بررسی Recordهای هدف ببینید.

اجرای Query ذخیره‌شده:

Sub MakeTableQuery()
DoCmd.SetWarnings False
DoCmd.OpenQuery "MyUpdateQuery"
DoCmd.SetWarnings True
End Sub

Warningها را پس از اجرا حتماً دوباره روشن کنید.

اجرای SQL مستقیم:

CurrentDb.Execute " UPDATE MyTable SET CustomerName = 'Richard'"

برای رشتهٔ طولانی:

Src = " UPDATE MyTable SET CustomerName = 'Richard' "
Src = Src & "WHERE (((CustomerName)='Shepherd'))"
CurrentDb.Execute Src

Pass Through Query

Pass Through Query نوع خاصی از Query برای تعامل با Databaseهای خارجی است و در فصل ۱۹ بررسی می‌شود.

استفاده از Function سفارشی در Query

گاهی لازم است روی یک Field عملی انجام دهید که انجام مستقیم آن با SQL ساده نیست. می‌توانید Functionای در VBA بنویسید و سپس آن را داخل SQL Query فراخوانی کنید.

نمونه‌ای واقعی: اگر مقدار Field خاصی کاملاً با حروف بزرگ نوشته شده باشد، Field عددی دیگری باید صفر نمایش داده شود و در غیر این صورت مقدار واقعی‌اش دیده شود. برای تشخیص Uppercase بودن رشته، Function زیر نوشته می‌شود:

Function CheckUpperCase(Target As String) As Boolean
Dim Flag As Integer
For n = 1 To Len(Target)
    If Asc(Mid(Target, n, 1)) >= 97 Then
        CheckUpperCase = False
        Exit Function
    End If
Next n
CheckUpperCase = True
End Function

سپس در Select Query:

SELECT IIf(CheckUpperCase(Company),0,ValueField) AS Rslt
FROM MyTable

IIf مقدار Boolean Function را تفسیر می‌کند و صفر یا ValueField را نمایش می‌دهد. مثال فرض می‌کند MyTable دارای Fieldهای Company و ValueField است. چیزی که در جلسه ممکن بود غیرممکن به نظر برسد، با استفادهٔ دقیق از VBA قابل حل شد.

اگر Custom Function را در SQL Query استفاده می‌کنید، هنگام اجرای Query پنجرهٔ VBE را ببندید. در غیر این صورت VBE با اجرای Query Refresh می‌شود و Overhead قابل توجهی ایجاد می‌کند، به‌خصوص زمانی که Query Recordهای زیادی بازمی‌گرداند.

فصل ۱۳ — Table Macroها

یکی از قابلیت‌های جدید Access 2010 امکان ایجاد Table Macro است. این قابلیت عملکردی شبیه Trigger در Databaseهایی مانند Oracle و SQL Server دارد. فرض کنید وقتی Recordای در Table مشخص Update می‌شود، بخواهید در Table دیگری Record تازه‌ای ایجاد شود. Record جدید حتی ممکن است رابطهٔ مستقیمی با Record اولیه نداشته باشد و صرفاً برای ثبت یادداشت یا رویداد ساخته شود.

در Databaseهای سنگین، Trigger در پاسخ به Event روی Table اصلی می‌تواند Update، Delete یا Insert روی Table مشخصی انجام دهد. Access 2010 نیز این قابلیت را اضافه کرده، اما با محدودیت‌هایی. از نظر نویسنده، طراحی منطقی‌تر این بود که هر Table مانند Form و Report یک VBA Module با Eventهای Update، Delete و Insert داشته باشد تا Developer بتواند VBA دلخواه بنویسد. در عوض Table Macroها از محیط شبه-Macro استفاده می‌کنند و آزادی Developer را محدود می‌سازند. بااین‌حال گام مهمی برای Access هستند و امکانات تازه‌ای باز می‌کنند.

ساخت Table Macro

یک Table نمونه با دو Field عددی بسازید. از Create روی Table Design کلیک و دو Field با نام‌های Number1 و Number2 ایجاد کنید.

Table را با نام tblTestTableMacro ذخیره کنید. برای این مثال Primary Key لازم نیست. در تب Design، از گروه Field, Record, and Table Events روی Create Data Macros و سپس After Insert کلیک کنید. از Action، گزینهٔ Edit Record را انتخاب کنید.

در نخستین Add New Action، گزینهٔ SetField را انتخاب کنید.

در Action، Field را Number2 و Value را:

3*[Number1]

قرار دهید.

برای ویرایش Action کافی است روی متن Set Field کلیک کنید. Macro و Table را Save کنید، سپس Table را در View Mode باز و در Number1 عددی وارد کنید. Number2 به‌طور خودکار سه برابر Number1 می‌شود. حتی اگر ابتدا برای Number2 مقداری وارد کنید، Table Macro آن را با مقدار محاسبه‌شده جایگزین می‌کند.

کاربرد این روش می‌تواند محاسبهٔ Fieldهای مشتق‌شده باشد؛ مثلاً Tableای که داده‌های مربوط به Risk Weighting را جمع می‌کند و بر اساس ورودی کاربر وزن ریسک را محاسبه می‌کند.

ایجاد Record در Table دیگر با Table Macro

Table Macro می‌تواند هنگام ورود داده در Table اصلی در Table دیگری Record بسازد. Table دوم را با یک Field عددی به نام Number2Copy ایجاد و با نام tblTableMacroCopy ذخیره کنید. Primary Key برای این مثال لازم نیست.

tblTestTableMacro را در Design View باز کنید، Create Data Macros > After Insert را انتخاب و از Add New Action گزینهٔ Create Record را اضافه کنید.

در Create Record In، Table دوم یعنی tblTableMacroCopy را انتخاب کنید. داخل Action خاکستری، Add New Action را باز و SetField را انتخاب کنید. Field Name را Number2Copy و Value را:

[tblTestTableMacro].[Number2]

قرار دهید.

Macro و Table را Save، Data Macro Window را ببندید و Table اصلی را در View Mode باز کنید. Record جدیدی با مقدار Number1 ایجاد کنید. Number2 مانند قبل محاسبه می‌شود و هم‌زمان در tblTableMacroCopy نیز Record جدیدی بر اساس Number2 ساخته خواهد شد.

این مثال بخشی از توانایی Table Macroها را نشان می‌دهد. نویسنده محدودیت نبود VBA و اجبار به زبان شبه-Macro را نکته‌ای منفی می‌داند، اما این قابلیت همچنان بُعد تازه‌ای به برنامه‌های Access اضافه می‌کند.