نحوه استفاده از انتقال اطلاعات (Transactions) در VBA

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

Transactions در اکسس

 اکسس (Jet/ACE-Database-Engine)، از Transaction ها نیز پشتیبانی می کند. و به طور ضمنی از آنها به صورت پیش فرض برای هر کوئری اکشنی که اجرا می شود استفاده می کند. تنها یک پرس و جو در داخل Transaction اجرا می شود، بنابراین شما به سختی متوجه وجود Transaction می شوید. با این حال، تضمین می‌کند که در صورت بروز خطا، می‌توان تمام تغییرات داده‌ها توسط پرس‌وجو را برگرداند. (شما می توانید با تنظیم Use Transaction -Property روی "No" استفاده از Transaction را برای یک پرس و جو غیرفعال کنید.)

اسکرین شات از برگه ویژگی یک پرس و جو شامل ویژگی Use Transaction

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

پیاده سازی Transaction ها در کد VBA
حالا اجازه دهید نگاهی به اجرای یک Transaction در VBA بیندازیم.

Public Sub TransactionDemo()

DAO.DBEngine.BeginTrans

On Error GoTo tran_Err

CurrentDb.Execute "INSERT INTO tblLog (Text1) VALUES ('New banana delivery')", dbFailOnError

CurrentDb.Execute "UPDATE tblInfo SET InfoText = 'Banana count: 2983' WHERE ID = 6 ", dbFailOnError

CurrentDb.Execute "DELETE FROM tblTemp", dbFailOnError

DAO.DBEngine.CommitTrans

Exit Sub

tran_Err:

DAO.DBEngine.Rollback

MsgBox "Transaction failed. Error: " & Err.Description

End Sub

(شما نمی توانید از Transaction های ماکرو در Access استفاده کنید.)

قبل از اجرای اولین کوئری، به DBEngine دستور می دهید تا یک Transaction را با استفاده از BeginTrans -Method شروع کند. سپس تمام کوئری های مربوطه را که می خواهید در Transaction گروه بندی کنید، اجرا می کنید.

اگر خطایی وجود نداشته باشد، معمولاً Transaction را با فراخوانی CommitTrans -Method انجام می‌دهید و با این کار، تمام تغییرات همه پرس‌وجوها را ادامه می‌دهید. اما این به شما بستگی دارد. شما می توانید یک Transaction را با روش Rollback -Method برگردانید، حتی اگر همه پرس و جوها بدون خطا موفق شوند. اگر برخی از پرس و جوهای موجود در آن شکست خوردند، همچنان می توانید یک Transaction را انجام دهید.

خوب، حالا بیایید به کد اجرای Transaction نگاه کنیم.

این خیلی سخت نیست، اما دو نکته مهم وجود دارد که باید از آنها آگاه باشید!

رسیدگی به خطاهایی که ممکن است در حین اجرای کوئری ها رخ دهد بسیار مهم است. در صورت بروز خطا، باید Transaction را برگردانید. در غیر این صورت، صفحات پایگاه داده (یک واحد ذخیره سازی داخلی از چندین رکورد) که تحت تأثیر Transaction قرار گرفته اند، قفل خواهند ماند و هیچ کاربر دیگری نمی تواند آن رکوردها را تغییر دهد. بنابراین بخشی از پایگاه داده خود را در حالت فقط خواندنی برای همه باقی می گذارید تا زمانی که نمونه Access که Transaction را شروع می کرد بسته شود. - که Transaction را عقب می اندازد. نکته مهم دیگر در اینجا استفاده از dbFailOnError -Option برای CurrentDb.Execute -Method است . اگر این گزینه را حذف کنید، دستور action SQL می‌تواند بی‌صدا از کار بیفتد. به عنوان مثال اگر پرس و جو باعث نقض قانون کلید یا اعتبار سنجی شود، رکوردها درج، به روز یا حذف نمی شوند، اما هیچ خطایی در VBA وجود نخواهد داشت. هنوز هم می‌توانستید Transaction را انجام دهید، اما فقط تغییرات سایر دستورات SQL واقعاً اجرا شد. - این دقیقاً همان مشکلی است که ما معمولاً می خواهیم با استفاده از Transaction ها از آن جلوگیری کنیم. می توانید هر تعداد پرس و جو را در یک Transaction گروه بندی کنید. اما همیشه به خاطر داشته باشید: سوابق آسیب‌دیده (صفحات پایگاه داده) تا پایان Transaction قفل خواهند شد. درخواست اقدام یا ویرایش دستی کاربر دیگری که به این سوابق دسترسی دارد، تا زمانی که قفل‌های سوابق داخل Transaction آزاد نشوند، با شکست مواجه می‌شوند.

اسکرین شات پیام خطای قفل رکورد

اگر چندین عملیات مختلف با استفاده از Transaction‌ها برای تغییر جداول مشابه دارید، همیشه به ترتیب به این جداول دسترسی داشته باشید، در غیر این صورت ممکن است بن بست (دو Transaction منتظر یکدیگر برای تکمیل و آزاد کردن قفل‌ها) رخ دهد.

بدون تعامل کاربر در یک Transaction!
در نهایت توصیه ای در مورد استفاده مفهومی از معاملات. در حالی که از نظر فنی امکان پذیر است، من قویاً توصیه می کنم از هرگونه تعامل با کاربر ("آیا می خواهید تغییرات را ذخیره کنید؟ - بله/خیر") در Transaction خودداری کنید.

همانطور که در بالا گفته شد، یک Transaction باز بخش‌هایی از پایگاه داده شما را قفل می‌کند تا زمانی که توسط یک commit یا rollback کامل شود. ایده بدی است که منتظر ورودی کاربر برای تکمیل Transaction باشید. ممکن است کاربر در غیر این صورت مشغول باشد و تبادل اطلاعات را برای مدتی باز بگذارد، یا ممکن است به استراحت ناهار خود برود یا به تعطیلات سه هفته ای خود برود. - و بخشی از پایگاه داده در برابر سایر عملیات نوشتن قفل می شود تا زمانی که او برگردد. دلیل دیگری وجود دارد که من توصیه نمی کنم تعامل کاربر در یک Transaction را شامل شود. این یکی برای Access است و برای Transaction‌های با پایگاه‌های داده سرویس‌دهنده/کلاینت مانند Microsoft SQL Server اعمال نمی‌شود. اگر در حین Transaction، مشکل فنی قطع اتصال برنامه Access Frontend از فایل Backend وجود داشته باشد، مانند خرابی شبکه یا قطع برق، خطر خراب شدن پایگاه داده شما وجود دارد. این خطر فساد نیز وجود دارد اگر یک حادثه مشابه در طول اجرای یک پرس و جو بدون Transaction رخ دهد. هر گونه تعامل با کاربر به ناچار زمان تکمیل Transaction را افزایش می دهد و در نتیجه خطر خرابی پایگاه داده را افزایش می دهد. بنابراین لطفاً Transaction‌ها را به‌عنوان یک واحد فنی کار ببینید که شامل تعامل کاربر نیست. اگر عملیاتی که می‌خواهید در Transaction انجام دهید نیاز به ورودی کاربر دارد، آن را قبلا دریافت کنید و قبل از شروع Transaction مطمئن شوید که معتبر و کامل است.

transactions در فرم های دسترسی - به شما هشدار داده شد!

اکنون، پس از اینکه رزروهای خود را در مورد تعامل کاربر در Transaction ها کاملاً روشن کردم، با این وجود توضیح خواهم داد که چگونه می توانید از Transaction ها با ورودی کاربر در فرم ها استفاده کنید. با استفاده از فرم‌های Access که از داده‌های پیش‌فرض اتصال به جدول یا پرس و جو استفاده می‌کنند، اصلاً نمی‌توانید از Transaction‌ها استفاده کنید. داده‌های موجود در فرم Access با اتصال پیش‌فرض خارج از محدوده فضاهای کاری قابل دسترسی برنامه‌نویسی اصلاح می‌شوند. بنابراین، حتی اگر قبل از تغییر داده‌ها در یک فرم، Transaction‌ها را شروع کنید، هر گونه commit یا بازگشتی که در کد انجام می‌شود هیچ تأثیری ندارد. با این حال، راهی برای عملی کردن آن وجود دارد. از Access 2002 به بعد، شما نه تنها می توانید یک فرم را با تنظیم Recordsource فرم به یک جدول یا نام پرس و جو یا هر عبارت SQL متصل کنید، بلکه می توانید فرم را با تنظیمات خاصیت Recordset به مجموعه رکوردهایی که ایجاد کرده اید، به صورت داده پیوند دهید. در کد VBA اگر این کار را انجام دهید، تغییرات داده ها در محدوده Transaction است. از این رو، اکنون می‌توانید یک Transaction را شروع کنید، به کاربر اجازه دهید داده‌ها را در یک فرم ویرایش کند و سپس Transaction را انجام دهد یا آن را برگرداند. این حتی با یک فرم حاوی یک فرم فرعی در رابطه اصلی-جزئیات کار می کند. سپس باید فرم اصلی و زیرفرم جزئیات را به یک مجموعه رکورد متصل کنید. با این حال، یک هشدار جزئی وجود دارد. به دلایلی که من متوجه نمی شوم، اگر از ویژگی های LinkMasterFields/LinkChildFields برای پیوند دادن خودکار داده ها در دو فرم استفاده کنید، این کار نمی کند . به جای آن باید فرم جزئیات را با Master for در کد خود همگام کنید. این شامل تنظیم مقدار فیلد کلید خارجی فرم فرزند به مقدار متناظر فرم اصلی است. این را می توان با تنظیم DefaultValue یک کنترل مخفی محدود به ستون کلید خارجی به مقدار کلید فرم اصلی انجام داد.

در اینجا اسکرین شات یک فرم اصلی جزئیات ساده و کد VBA مربوطه برای کنترل Transaction است.

با مدیریت Transaction به فرم اصلی جزئیات دسترسی پیدا کنید

Private Sub btnBeginEdit_Click()

    DBEngine.BeginTrans

End Sub

Private Sub btnSaveChanges_Click()

    DBEngine.CommitTrans

End Sub

Private Sub btnUndoChanges_Click()

    DBEngine.Rollback

End Sub

Private Sub Form_Current()

    ' loading the child records for the current master record

    Set Me.subActorRatings.Form.Recordset = _

        CurrentDb.OpenRecordset("SELECT * FROM tblActorRatings WHERE ActorId=" & Me!ActorId)

    ' Setting the correct default value for new records

    Me.subActorRatings.Form.Controls("txtActorIdHidden").DefaultValue = Me!ActorId

End Sub

Private Sub Form_Load()

    Set Me.Recordset = CurrentDb.OpenRecordset("SELECT * FROM tblActors")

End Sub

مدیریت Transaction در اینجا به منظور نشان دادن مفهوم کاملاً دستی است. این کد به هیچ وجه برای استفاده در تولید مناسب نیست. در سناریوی تولید، باید Transaction را به صورت خودکار شروع کنید و تمام تعاملات احتمالی کاربر مانند بستن فرم، مرور سوابق، و همچنین حذف و افزودن رکوردهای جدید را انجام دهید. اجرای این امر به شیوه ای قوی یک چالش خواهد بود. متأسفانه، کتابخانه دسترسی به داده DAO هیچ ویژگی یا روشی را برای دریافت وضعیت Transaction های فعلی ارائه نمی دهد. اگر بخواهید یک Transaction غیرموجود را انجام دهید یا برگردانید، خطایی رخ خواهد داد، اما هیچ چیز مانع از باز کردن تصادفی چندین Transaction تو در تو در آن واحد نمی‌شود. بنابراین، هنگام طراحی کد رسیدگی به Transaction باید نهایت دقت را به خرج دهید تا از بروز هرگونه خطای سخت ردیابی جلوگیری کنید. از آنجایی که به هیچ وجه گنجاندن تعاملات کاربر در Transaction‌ها را توصیه نمی‌کنم، هرگز از رویکرد فوق در یک برنامه تولید استفاده نکرده‌ام. نمی توانم بگویم که با چندین کاربر قابل اعتماد کار می کند یا برای مدت طولانی.

Transaction ها و شماره های خودکار
برخی از افراد شکاف در توالی شماره خودکار خود را دوست ندارند. اگر شما یکی از آن‌ها هستید، ممکن است این ایده به ذهنتان برسد که از Transaction‌ها برای جلوگیری از «از دست رفتن» مقادیر یک دنباله شماره خودکار با قرار دادن کدی که رکوردهای جدید را در جدولی با AutoNumber وارد می‌کند، استفاده کنید. ایده خوبی است، اما کار نمی کند. هیچ مقدار AutoNumber را که در یک Transaction برگشتی استفاده شده است، پس نخواهید گرفت. این ارزش ها هنوز "از دست رفته" هستند. منطق مدیریت دنباله شماره خودکار خارج از محدوده هر Transaction است. باید اینجوری باشه در غیر این صورت، هر Transactionی که یک AutoNumber جدید درخواست کند، همه کاربران دیگر را از درج رکوردهای جدید در همان جدول تا زمانی که آن Transaction کامل شود مسدود می کند.