بخش سوم — تکنیک‌های پیشرفته در Access VBA

در این بخش می‌آموزید چگونه با Code، Chart و Graph بسازید، با Databaseهای خارجی کار کنید و از API یا Application Programming Interface برای کارهایی مانند پخش فایل WAV و دستکاری RibbonX استفاده کنید. همچنین Class Moduleها و Animation بررسی می‌شوند. این موضوعات قدرتمند امکان انجام کارهای غیرمعمول و پیشرفته با VBA و Access را فراهم می‌کنند.

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

فصل ۱۸ — نمودارها و Graphها

از Access 2007 به بعد، ساخت و دستکاری Chartها با VBA نسبتاً دشوار و پیچیده شده است. برخلاف Excel VBA، Methodی مانند AddChart وجود ندارد و اگر Chart را روی Report بسازید، Object Model آن لزوماً Propertyها و Methodهای معمول مورد انتظار را در اختیار نمی‌گذارد. از طرف دیگر Access Macro Recorder ندارد که بتوان با ضبط عملیات دستی، VBA لازم را تولید کرد.

برای استفاده از VBA با Chartها باید Reference به Microsoft Graph Object Library اضافه شود. متن کتاب به Microsoft Graph 12.0 اشاره می‌کند و در دستورالعمل انتخاب Library، مورد Microsoft Graph 14.0 Object Library را در فهرست References نشان می‌دهد. از VBE گزینهٔ Tools | References را باز، Library مناسب را تیک و OK کنید.

سپس Tableای برای دادهٔ Chart بسازید. از Create و Table Design دو Field ایجاد کنید: aName از نوع Text و aValue از نوع Number. Table را با نام tblChart ذخیره کنید و برای مثال نیازی به Primary Key نیست. Table را با چند Record نمونه پر کنید.

اکنون یک Template Graph لازم است که با VBA داخل Reportها قرار گیرد. Report خالی در Design View بسازید و از Controls، Chart را روی Report بکشید. نام Chart Control را در Properties روی Graph0 قرار دهید. Chart Wizard باز می‌شود؛ tblChart را Source انتخاب، هر دو Field را اضافه، یک Pie Chart استاندارد انتخاب و Finish کنید. Report را با نام rptTemplate ذخیره کنید.

گرچه Template فعلاً Pie Chart و Source مشخص دارد، بعداً بسیاری از این Propertyها با Code قابل تغییرند. با این حال برای ایجاد Template اولیه باید Source و شکل اولیه‌ای مشخص شود.

Code زیر Report جدید می‌سازد، Chart را از Template به آن اضافه می‌کند، Report را Save/Rename و سپس Chart را دستکاری می‌کند:

Sub CreateGraphOnReport()
Dim rep As Report
Dim ctl As control
Dim ch As Graph.Chart
Dim RepName As String
DoCmd.OpenReport "rptTemplate", acDesign, , , , acHidden
Set rep = CreateReport()
Set ctl = CreateReportControl(rep.Name, acObjectFrame, acDetail)
ctl.OleData = Reports!rptTemplate!Graph0.OleData
DoCmd.Restore
DoCmd.Close acReport, "rptTemplate"
RepName = rep.Name
rep.OLEUnbound0.RowSource = "tblChart"
rep.OLEUnbound0.RowSourceType = "Table/Query"
rep.OLEUnbound0.ColumnHeads = True
rep.OLEUnbound0.Top = 100
rep.OLEUnbound0.Left = 100
DoCmd.Restore
DoCmd.Save acDefault
DoCmd.Close acDefault
DoCmd.Rename "rptMyGraph", acReport, RepName
DoCmd.OpenReport "rptMyGraph", acViewDesign
DoCmd.Restore
Set ch = Reports("rptMyGraph").OLEUnbound0.Object
ch.HasTitle = True
ch.ChartTitle.Text = "My Chart"
ch.ChartType = xl3DPie
DoCmd.Save acDefault
DoCmd.Close acDefault
DoCmd.OpenReport "rptMyGraph", acViewReport
End Sub

ابتدا Objectهایی برای Report، Control و Chart از Graph Object Library تعریف می‌شوند. rptTemplate در Design Mode و به‌صورت Hidden باز می‌شود. CreateReport Report جدید می‌سازد و CreateReportControl یک Object Frame در Detail Section ایجاد می‌کند. سپس OleData از Graph0 در Template به Object Frame جدید کپی می‌شود و آن را عملاً به Chart Control تبدیل می‌کند.

نام Report در String نگه داشته می‌شود چون Report هنگام بازبودن قابل Rename نیست. Propertyهای OLE Object مانند RowSource، RowSourceType، ColumnHeads، Top و Left تنظیم می‌شوند. ColumnHeads=True مهم است؛ در غیر این صورت Row نخست Source Table از دادهٔ Chart جا می‌افتد.

Report Save و به rptMyGraph Rename می‌شود و دوباره در Design Mode باز می‌گردد؛ Code بعدی در غیر این حالت کار نمی‌کند. سپس Objectِ Chart به OLE Object روی Report متصل می‌شود و Propertyهای معمول Chart مانند Title و ChartType در دسترس قرار می‌گیرند. پیش از تعیین Title Text باید HasTitle=True باشد. بعضی Propertyها مانند Top و Left مربوط به OLE Object هستند، نه خود Chart.

همین الگو روی Form نیز قابل استفاده است. اگر از قبل با Chart Wizard، Chart Control روی Form/Report دارید و فقط می‌خواهید آن را دستکاری کنید:

Dim ch As Graph.Chart
Set ch = Me.Graph0.Object

Chart Typeها

Excel 97 و نسخه‌های بعد Constantهای داخلی متعددی برای Chart Type دارند که با Gallery موجود در Chart Wizard متناظرند:

کار با SeriesCollection

SeriesCollection داده‌های Chart را نمایندگی می‌کند و برای تغییر Appearance بسیار مفید است. مثال‌ها فرض می‌کنند Objectِ ch از Code قبلی موجود است.

روشن/خاموش‌کردن Data Label:

ch.SeriesCollection(1).HasDataLabels = True

Explode کردن Sliceهای Pie:

ch.SeriesCollection(1).Explosion = 8

Propertyِ Explosion یک Long Integer است و در توضیح کتاب هر Bit نمایندهٔ Slice در Pie Chart در نظر گرفته شده است. با چهار Slice، مقدار 8 همه را Explode می‌کند؛ مقدار 4 فقط Slice سوم را و مقدار 0 هیچ Sliceای را جدا نمی‌کند.

نمایش Value و Category Name در Labelها:

ch.SeriesCollection(1).DataLabels.ShowValue = True
ch.SeriesCollection(1).DataLabels.ShowCategoryName = True

Category Name همان Name نشان‌داده‌شده در Legend است.

تغییر Color یک Segment:

ch.SeriesCollection(1).Points(1).Interior.Color = QBColor(12)

نویسنده نمونه‌ای از Reportهای معاملات Foreign Exchange را شرح می‌دهد که برای هر Customer یک Pie Chart از Currencyها داشت. چون تعداد Currencyها برای مشتریان متفاوت بود، Color Segmentها بین Chartها ثابت نمی‌ماند. Requirement این بود که هر Currency مستقل از Customer همیشه Color خاص خود را داشته باشد؛ با VBA و پارامترهای ازپیش‌تعریف‌شده Color هر Segment به‌صورت برنامه‌نویسی تنظیم شد.

Export کردن Chart به Picture File

Chart را می‌توان به GIF، JPEG یا PNG Export کرد. مثال زیر فرض می‌کند Chart روی Report از قبل وجود دارد:

Sub OutputChart()
Dim ch As Graph.Chart
Set ch = Me.Graph0.Object
ExportFile = Access.CodeProject.Path & "\" MyChart.gif"
ch.Export Filename:=ExportFile, FilterName:="GIF"
End Sub

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

فصل ۱۹ — کار با Databaseهای خارجی

یکی از قابلیت‌های قدرتمند Access این است که می‌تواند به Databaseهای خارجی متصل شود و Tableهای آن‌ها را تقریباً مانند Tableهای داخلی Application استفاده کند. تا زمانی که Database با ODBC (Open Database Connectivity) سازگار و Driver مناسب موجود باشد، Code می‌تواند با آن تعامل کند. ODBC رابط استانداردی برای دسترسی Applicationها از زبان‌های مختلف، از جمله VBA، فراهم می‌کند.

برای مثال ممکن است بخواهید داده‌ای از Accounting System وارد Application کنید، در حالی که خود سیستم Accounting امکان Export مطلوب را در UI ندارد. اگر Database آن Driverِ ODBC داشته باشد، داده را می‌توان در شکل موردنیاز به Access آورد.

Databaseهایی مانند Microsoft Access، Microsoft SQL Server و Oracle از ODBC پشتیبانی می‌کنند. با Permission مناسب می‌توان Data را خواند و حتی Write-back انجام داد. نوشتن در Database خارجی باید با احتیاط شدید باشد؛ تغییر یک ID که در Relationship با Table دیگری استفاده شده می‌تواند ظاهر Table را درست نگه دارد ولی Referential Integrity کل Database را خراب کند. امن‌ترین حالت معمولاً Read-only Account است و مالک Database نیز غالباً همین سطح را اعطا می‌کند.

به دلیل انعطاف ODBC، Access می‌تواند مانند «Junction Box» میان چند Database با Platformهای متفاوت عمل کند. مثلاً می‌توان Linked Tableهایی از Oracle و SQL Server هم‌زمان داشت و با Key Fieldهایی مثل Customer ID یا Order Number دادهٔ دو System را در یک Query ترکیب کرد. نویسنده این روش را برای Reconcile کردن دادهٔ موجود در SQL Server و Oracle مفید دانسته است.

Linked Databaseها اندازهٔ فایل Access را نیز کوچک می‌کنند، زیرا Data جای دیگری ذخیره می‌شود. حتی می‌توان برای دورزدن Limit دو گیگابایتی Access، Master Database را برای Query، Form، Report و VBA نگه داشت و Data را در Access Database دیگری یا Server قوی‌تری مانند SQL Server/Oracle قرار داد. Database Serverهای سنگین‌تر می‌توانند Robustness بیشتری فراهم کنند.

Link به Access Database دیگر

از External Data و سپس Access در Import Group استفاده کنید. Database هدف را Browse کنید و گزینهٔ Link to Data Source by Creating a Linked Table را انتخاب کنید. هنگام تعیین Location، در Network بهتر است به‌جای Drive Letter از آدرس Server/UNC استفاده کنید؛ چون یک Share ممکن است روی سیستم شما J: و روی سیستم کاربر H: Map شده باشد.

Tableهای موردنظر را انتخاب کنید. آن‌ها به‌صورت Linked Table در Navigation Pane ظاهر می‌شوند. با Double-click داده دیده می‌شود و می‌توان تقریباً مانند Table داخلی از آن استفاده کرد، به‌جز اینکه Structure Table خارجی از این مسیر قابل تغییر نیست.

ODBC Link و DSN

برای اتصال به Server خارجی مانند SQL Server یا Oracle معمولاً چهار اطلاعات لازم است: Server Name، Login ID، Password و Database Name. کتاب نسخهٔ زمان خود را با SQL Server Native Client 10.0 توضیح می‌دهد.

ابتدا DSN یا Data Source Name بسازید. در Windows، ODBC Data Source Administrator را از Control Panel/Data Sources (ODBC) باز کنید. اگر Driverهای SQL Server نصب باشند در فهرست نمایش داده می‌شوند.

اگر DSN ندارید Add را انتخاب کنید و Driver مناسب را از فهرست انتخاب کنید. DSN نامی است که به ODBC Link اشاره می‌کند.

SQL Server Native Client را انتخاب کنید، برای DSN نام توصیفی بنویسید و Server Name یا IP Address را وارد کنید. برای SQL Server محلی نیز می‌توان از نام Computer یا مقدار Local متناسب با Setup استفاده کرد. فهرست Drop-down همیشه همهٔ Serverها را نشان نمی‌دهد. Description اختیاری است و فقط برای شناسایی DSN در ابزار Administrator استفاده می‌شود.

در مرحلهٔ Authentication، SQL Server Authentication را انتخاب و Login/Password را وارد کنید. اگر Connection Error رخ داد، Server Name، Driver، ID و Password را بررسی کنید. در صورت داشتن IP می‌توان با ping <ip address> دسترسی Network را آزمایش کرد.

پس از Connection، گزینهٔ Change the Default Database را فعال و Database موردنظر را انتخاب کنید. Wizard را Finish و Test Data Source را اجرا کنید. موفقیت Test نشان می‌دهد DSN آماده است.

استفاده از DSN در Access

از External Data روی ODBC در Import & Link کلیک کنید، گزینهٔ Link to the data source by creating a linked table را انتخاب و از Machine Data Source، DSN ساخته‌شده را برگزینید.

ممکن است Password دوباره درخواست شود؛ این لایهٔ Security اضافه برای جلوگیری از Unauthorized Access است. سپس Access Tableهای Server را فهرست می‌کند. در Database بزرگ با هزاران Table این مرحله ممکن است چند دقیقه طول بکشد.

در Link Tables، کتاب توصیه می‌کند Save Password را انتخاب کنید تا هر بار بازشدن Table Prompt ظاهر نشود. برای Tableهایی که قرار است Write شوند ممکن است Index Field نیز خواسته شود؛ معمولاً Unique Field. با این حال، Read-only Access ایمن‌تر است چون تغییر Data در Relational Database بدون شناخت Relationshipها خطرناک است.

Linked Tableهای خارجی در Navigation Pane ظاهر می‌شوند و تقریباً مانند Table داخلی استفاده می‌شوند، جز تغییر Structure.

مشکلات Linked Tableها

اگر Locationِ Database هدف تغییر کند، Link خراب می‌شود. برای Access Database خارجی از Linked Table Manager در External Data استفاده کنید، Tableهای لازم را تیک بزنید و Always prompt for new location را فعال کنید تا Target جدید انتخاب شود.

SQL Server یا Oracle معمولاً کمتر جابه‌جا می‌شوند؛ اگر Server/Database عوض شود می‌توان DSN را در ODBC Administrator ویرایش کرد. تغییر Structure نیز مسئلهٔ مهمی است: اضافه‌شدن Field جدید یا Rename شدن Field را می‌توان با Refresh در Linked Table Manager دریافت کرد، اما Queryها و Code وابسته باید دستی اصلاح شوند.

مشکل Deployment این است که DSN ساخته‌شده فقط روی Computer شما وجود دارد. Application روی سیستم User تا زمانی که همان DSN ایجاد نشود کار نمی‌کند. کتاب نمونهٔ ساخت DSN با Code را نشان می‌دهد:

DBEngine.RegisterDatabase "MyDSN", "SQL Server Native Client 10.0", True, _
"MyServer" _
& vbCr & "Database=MyDatabase"

این Code DSN با نام MyDSN را ایجاد یا Overwrite می‌کند و آن را به MyServer/MyDatabase وصل می‌کند. ID و Password در نمونه داخل Code نیستند؛ کتاب اشاره می‌کند نمایش Password در Source Code از نظر Security نامناسب است.

Performance مشکل دیگر Linked Tableهاست. اگر Table بزرگ یا فاقد Index مناسب باشد، Access ممکن است حجم زیادی Data از Server بگیرد و سپس Query را محلی Process کند. این کار هم Application را کند می‌کند و هم می‌تواند Load روی External Database را بالا ببرد. راهکارهای بعدی Pass-through و ADO هستند.

Pass-Through Query

Pass-through Query شبیه Access Query است، اما روی Server اجرا می‌شود و Client فقط Data موردنیاز را دریافت می‌کند. از نظر رفتار شبیه اجرای Stored Procedure روی Server است و در دادهٔ حجیم می‌تواند Speed بسیار بیشتری داشته باشد.

برای ساخت آن DSN لازم است. Query Design جدید باز کنید، Show Table را ببندید، SQL View را انتخاب، Query Type را روی Pass-Through بگذارید و Property Sheet را باز کنید.

در Propertyِ ODBC Connect Str DSN را انتخاب کنید. Wizard ممکن است بپرسد Password در Connection String ذخیره شود یا نه. اگر ذخیره نشود User هر بار باید Password وارد کند؛ اگر ذخیره شود هرکس به Query Design دسترسی داشته باشد ممکن است آن را ببیند. کتاب راهکار Lock کردن Database را به فصل ۲۴ ارجاع می‌دهد.

Pass-through Query با GUI معمول Access ساخته نمی‌شود؛ SQL باید مطابق Syntaxِ Server مقصد نوشته شود، زیرا Query مستقیم روی Server اجرا می‌شود. متن کتاب برای SQL Server و Oracle یادآوری می‌کند Wildcard و String Syntax با Access فرق دارد و به جای IIF باید از الگوی CASE WHEN ... THEN ... ELSE ... END استفاده شود.

بهتر است Query ابتدا در Tool خود Database مانند SQL Server Manager یا TOAD آزمایش شود، هرچند ممکن است Developer Access به چنین Toolهایی Permission نداشته باشد. این روش از Linked Table پیچیده‌تر است، اما Speed بسیار بالاتر می‌تواند برای Application حیاتی باشد.

استفاده از ADO

ADO (ActiveX Data Objects) Technology مایکروسافت برای اتصال به Databaseهاست. ADO یک COM است که می‌تواند با Connection String یا DSN به External Database وصل شود. Connection اطلاعات Location، Credential و مسیر دسترسی را فراهم می‌کند و ADO ابزارهای Read/Write را در اختیار Code می‌گذارد.

از Tools | References، Microsoft ActiveX Data Objects Library و ActiveX Data Objects Recordset Library را انتخاب کنید. کتاب Version 6.0 را نشان می‌دهد و می‌گوید اگر موجود نیست جدیدترین Version نصب‌شده را انتخاب کنید.

مثال اتصال به SQL Server:

Sub ADOExtract()
Dim RsADO As ADODB.Recordset, RsAccess As Recordset
Dim Cnct As String,Cnct1 as String, Cnct2 as String
Set Connection = New ADODB.Connection
Cnct1 = "Provider=SQLOLEDB;Driver={SQL Server Native Client 10.0};"
Cnct2="Server=MyServer;Database=MyDatabase;Uid=MyId;Pwd=MyPassword;"
Cnct=Cnct1 & Cnct2
Connection.CommandTimeout = 60
Connection.Open ConnectionString:=Cnct
Set RsAccess = CurrentDb.OpenRecordset("MyDestinationTable")
Set RsADO = New ADODB.Recordset
With RsADO
    .Open Source:="select * from MySourceTable", _
    ActiveConnection:=Connection, CursorType:=adOpenStatic
    Do Until RsADO.EOF
        RsAccess.AddNew
        RsAccess!Field1 = RsADO!Field1
        RsAccess!Field2 = RsADO!Field2
        RsAccess.Update
        RsADO.MoveNext
    Loop
End With
Set RsADO = Nothing
Set RsAccess = Nothing
End Sub

دو Recordset ساخته می‌شوند: یکی ADO و دیگری DAO Access. Connection String شامل Server، Database، ID و Password ساخته، Timeout تعیین و Connection باز می‌شود. DAO Recordset به Destination Table اشاره می‌کند و ADO Recordset Source Table را روی SQL Server باز می‌کند. سپس Recordهای Source پیمایش و با AddNew/Update به Destination اضافه می‌شوند. در پایان Objectها Release می‌شوند.

Connection String می‌تواند بر پایهٔ DSN نیز باشد:

OLEDB;DSN=MyDSN;Uid=MyId;Pwd=MyPassword;

یا به Access Database دیگر متصل شود:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;

هنگام اجرای SQL مستقیم روی External Database باید Syntax همان Platform رعایت شود. متن کتاب بار دیگر تفاوت Wildcard/String و استفاده از CASE WHEN به‌جای IIF را یادآوری می‌کند.

دو عیب ذکر می‌شود. نخست Securityِ Password در Connection String است؛ می‌توان Database را Lock کرد یا Password را حذف و از User درخواست کرد، ولی Prompt برای Overnight Job مناسب نیست. دوم Performance: Loop کردن Record به Record برای Table کوچک زیر حدود 100 Record خوب است، ولی برای صدها هزار Record بسیار کند می‌شود. Access Object Model Methodی مثل Excel CopyFromRecordset ندارد.

برای انتقال سریع‌تر می‌توان Database Engine را وادار کرد Data را مستقیم به Temporary/Destination Table منتقل کند:

Sub FastTransferofData()
Dim Cnct As String, Ccnt1 as String, Ccnt2 as String
Cnct1 = "ODBC;Provider=MDASQL;Driver={SQL Server};"
Ccnt2 = "Server=MyServer;Database=MyDatabase;Uid=MyId;Pwd=MyPassword;"
Ccnt = Ccnt1 & Ccnt2
CurrentDb.Execute "insert into DestTble select * from [" & Cnct & "].SrceTble"
End Sub

این بار Connection بر پایهٔ ODBC و Provider/Driver متفاوت ساخته می‌شود و یک SQL Insert استاندارد Recordها را از SrceTble خارجی به DestTble در Access منتقل می‌کند. مثال فرض می‌کند Structure دو Table سازگار است. می‌توان Connection Stringهای دیگر یا DSN موجود را نیز جایگزین کرد.

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