ویرایش و حذف داده‌ها در SQL اکسس با VBA

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

در این آموزش می‌آموزید چگونه این دستورها را از محیط Excel VBA به‌صورت امن اجرا کنید. مثال‌ها با ADODB و روش Late Binding نوشته شده‌اند تا بدون فعال‌کردن Reference دستی در ویرایشگر VBA قابل اجرا باشند. علاوه بر ساختار SQL، درباره شرط WHERE، پارامترها، تراکنش، شمارش رکوردهای تحت تأثیر، کنترل خطا، ارتباط جداول، حذف نرم و راهکارهای جلوگیری از تغییر ناخواسته داده‌ها بحث می‌کنیم.

عملیات ویرایش و حذف داده‌ها چیست؟

کوئری UPDATE مقدار یک یا چند فیلد را در رکوردهای موجود تغییر می‌دهد و رکورد جدیدی ایجاد نمی‌کند. برای نمونه می‌توان وضعیت مشتری را فعال یا غیرفعال کرد، نشانی ایمیل را اصلاح کرد، مبلغ یک کالا را افزایش داد یا زمان آخرین ویرایش را ثبت کرد. کوئری DELETE رکوردهایی را که با شرط تعیین‌شده منطبق هستند به‌طور فیزیکی از جدول حذف می‌کند.

این دو دستور در گروه Action Query قرار می‌گیرند. برخلاف SELECT، نتیجه معمول آن‌ها یک Recordset برای نمایش نیست؛ بلکه تعداد رکوردهای تغییرکرده یا حذف‌شده اهمیت دارد. در کدنویسی حرفه‌ای VBA باید این تعداد را دریافت و با مقدار مورد انتظار مقایسه کرد. اگر قرار بوده یک رکورد ویرایش شود اما اکسس ده رکورد را گزارش کند، برنامه باید عملیات را مشکوک تلقی کند و در صورت استفاده از تراکنش، تغییر را بازگرداند.

چرا ایمنی در تغییر داده‌ها مهم است؟

بزرگ‌ترین خطر، حذف یا فراموش‌کردن شرط WHERE است. دستور UPDATE Employees SET IsActive = False همه کارکنان را غیرفعال می‌کند و دستور DELETE FROM Employees برای حذف همه رکوردهایی که محدودیت‌های پایگاه داده اجازه می‌دهند تلاش خواهد کرد. این دستورها از نظر نحوی صحیح‌اند؛ بنابراین موتور اکسس نمی‌تواند تشخیص دهد که هدف برنامه‌نویس فقط یک رکورد بوده است.

ایمنی باید در طراحی فرایند قرار گیرد. ابتدا با یک کوئری SELECT دقیقاً همان رکوردها را مشاهده یا شمارش کنید. سپس ورودی‌های اکسل و مسیر فایل را اعتبارسنجی کنید. اگر چند تغییر باید به‌صورت یک واحد انجام شوند، تراکنش آغاز کنید. پس از اجرا، تعداد رکوردهای تحت تأثیر را بررسی کنید و فقط در صورت منطقی‌بودن نتیجه، تراکنش را Commit کنید. ثبت گزارش عملیات نیز برای پیگیری خطا، حسابرسی و پیدا کردن منبع تغییرات بسیار مفید است.

ساختار دستورهای UPDATE و DELETE

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

اگر نام جدول یا فیلد فاصله داشته باشد یا از کلمات رزروشده استفاده کند، آن را داخل کروشه قرار دهید؛ مانند [Order Date]. در SQL مستقیم، متن با کوتیشن تکی مشخص می‌شود و تاریخ در اکسس معمولاً بین علامت # قرار می‌گیرد. با این حال، در برنامه واقعی بهتر است به‌جای چسباندن مقدارها به رشته SQL، از پارامتر استفاده کنید تا مشکل کوتیشن، قالب تاریخ و تنظیمات منطقه‌ای کاهش یابد.

اجزای مهم یک کوئری ایمن

در Provider مربوط به ACE OLE DB، علامت سؤال نقش جای‌نگهدار پارامتر را دارد و اتصال پارامترها بر اساس ترتیب انجام می‌شود. نامی که در VBA به پارامتر می‌دهید خوانایی کد را بهتر می‌کند، اما ترتیب واقعی همان ترتیب علامت‌های سؤال است. بنابراین پارامترها را دقیقاً مطابق ظاهرشدن آن‌ها در متن SQL به Command اضافه کنید.

مثال‌های پایه SQL

ویرایش یک فیلد

UPDATE Employees
SET IsActive = False
WHERE EmployeeID = 125;

اگر EmployeeID کلید اصلی باشد، انتظار داریم فقط یک رکورد تغییر کند. استفاده از کلید اصلی یا یک مقدار Unique بهترین انتخاب برای ویرایش یک رکورد مشخص است.

ویرایش چند فیلد

UPDATE Customers
SET City = 'Tehran', LastUpdated = Now()
WHERE CustomerID = 42;

دو فیلد در یک عملیات تغییر می‌کنند. اگر اجرای کوئری موفق باشد، هر دو مقدار برای رکورد هدف ثبت می‌شوند و لازم نیست دو دستور جداگانه اجرا شود.

حذف رکوردهای انتخاب‌شده

DELETE FROM ImportStaging
WHERE Imported = True;

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

مثال کاربردی VBA برای ویرایش یک رکورد

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

Option Explicit

Public Sub UpdateEmployeeEmail()
    Const adCmdText As Long = 1
    Const adInteger As Long = 3
    Const adParamInput As Long = 1
    Const adVarWChar As Long = 202

    Dim connection As Object
    Dim command As Object
    Dim databasePath As String
    Dim employeeId As Long
    Dim newEmail As String
    Dim recordsAffected As Long

    databasePath = ThisWorkbook.Path & "\Training.accdb"
    employeeId = 125
    newEmail = "[email protected]"

    On Error GoTo CleanFail

    If Len(Dir$(databasePath)) = 0 Then
        Err.Raise vbObjectError + 1000, , "Database file not found."
    End If

    If employeeId <= 0 Then
        Err.Raise vbObjectError + 1001, , "Employee ID must be positive."
    End If

    If InStr(1, newEmail, "@", vbTextCompare) = 0 Then
        Err.Raise vbObjectError + 1002, , "Email address is invalid."
    End If

    Set connection = CreateObject("ADODB.Connection")
    connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & databasePath & ";"

    Set command = CreateObject("ADODB.Command")
    Set command.ActiveConnection = connection
    command.CommandType = adCmdText
    command.CommandText = _
        "UPDATE Employees " & _
        "SET EmailAddress = ?, LastUpdated = Now() " & _
        "WHERE EmployeeID = ?;"

    command.Parameters.Append command.CreateParameter( _
        "pEmail", adVarWChar, adParamInput, 255, newEmail)
    command.Parameters.Append command.CreateParameter( _
        "pEmployeeId", adInteger, adParamInput, , employeeId)

    command.Execute recordsAffected

    If recordsAffected <> 1 Then
        Err.Raise vbObjectError + 1003, , _
            "Expected one updated row, but Access reported " & recordsAffected & "."
    End If

    MsgBox "Employee email updated successfully.", vbInformation

CleanExit:
    On Error Resume Next
    If Not connection Is Nothing Then
        If connection.State <> 0 Then connection.Close
    End If
    Set command = Nothing
    Set connection = Nothing
    Exit Sub

CleanFail:
    MsgBox Err.Description, vbExclamation, "Update failed"
    Resume CleanExit
End Sub

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

دو پارامتر به ترتیب ایمیل و شناسه اضافه شده‌اند، چون در متن SQL ابتدا علامت سؤال مربوط به ایمیل و سپس علامت سؤال مربوط به شناسه دیده می‌شود. جابه‌جایی این ترتیب می‌تواند باعث Data Type Mismatch یا ثبت مقدار در جای نامناسب شود.

مثال کاربردی VBA برای پیش‌نمایش و حذف با تراکنش

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

Option Explicit

Public Sub DeleteOldStagingRows()
    Const adCmdText As Long = 1
    Const adDate As Long = 7
    Const adParamInput As Long = 1
    Const maxAllowedDeletes As Long = 500

    Dim connection As Object
    Dim countCommand As Object
    Dim deleteCommand As Object
    Dim recordset As Object
    Dim databasePath As String
    Dim cutoffDate As Date
    Dim candidateCount As Long
    Dim recordsAffected As Long
    Dim transactionStarted As Boolean
    Dim errorDescription As String

    databasePath = ThisWorkbook.Path & "\Training.accdb"
    cutoffDate = DateAdd("m", -6, Date)

    On Error GoTo CleanFail

    If Len(Dir$(databasePath)) = 0 Then
        Err.Raise vbObjectError + 1100, , "Database file not found."
    End If

    Set connection = CreateObject("ADODB.Connection")
    connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & databasePath & ";"

    Set countCommand = CreateObject("ADODB.Command")
    Set countCommand.ActiveConnection = connection
    countCommand.CommandType = adCmdText
    countCommand.CommandText = _
        "SELECT Count(*) AS CandidateCount " & _
        "FROM ImportStaging " & _
        "WHERE Imported = True AND ImportedAt < ?;"
    countCommand.Parameters.Append countCommand.CreateParameter( _
        "pCutoff", adDate, adParamInput, , cutoffDate)

    Set recordset = countCommand.Execute
    candidateCount = CLng(recordset.Fields("CandidateCount").Value)
    recordset.Close

    If candidateCount = 0 Then
        MsgBox "No staging rows match the deletion rule.", vbInformation
        GoTo CleanExit
    End If

    If candidateCount > maxAllowedDeletes Then
        Err.Raise vbObjectError + 1101, , _
            "Deletion stopped: " & candidateCount & _
            " rows exceed the safety limit."
    End If

    connection.BeginTrans
    transactionStarted = True

    Set deleteCommand = CreateObject("ADODB.Command")
    Set deleteCommand.ActiveConnection = connection
    deleteCommand.CommandType = adCmdText
    deleteCommand.CommandText = _
        "DELETE FROM ImportStaging " & _
        "WHERE Imported = True AND ImportedAt < ?;"
    deleteCommand.Parameters.Append deleteCommand.CreateParameter( _
        "pCutoff", adDate, adParamInput, , cutoffDate)

    deleteCommand.Execute recordsAffected

    If recordsAffected <> candidateCount Then
        Err.Raise vbObjectError + 1102, , _
            "Preview and deletion counts do not match."
    End If

    connection.CommitTrans
    transactionStarted = False

    MsgBox recordsAffected & " staging rows deleted.", vbInformation

CleanExit:
    On Error Resume Next
    If Not recordset Is Nothing Then
        If recordset.State <> 0 Then recordset.Close
    End If
    If Not connection Is Nothing Then
        If connection.State <> 0 Then connection.Close
    End If
    Set recordset = Nothing
    Set deleteCommand = Nothing
    Set countCommand = Nothing
    Set connection = Nothing
    Exit Sub

CleanFail:
    errorDescription = Err.Description
    On Error Resume Next
    If transactionStarted Then connection.RollbackTrans
    On Error GoTo 0
    MsgBox errorDescription, vbCritical, "Delete failed"
    Resume CleanExit
End Sub

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

توضیح مرحله‌به‌مرحله کد

1. ساخت و کنترل مسیر: مسیر با ThisWorkbook.Path ساخته می‌شود تا فایل مورد انتظار کنار Workbook باشد؛ سپس وجود فایل بررسی می‌گردد.
2. اعتبارسنجی ورودی: شناسه مثبت، متن خالی، قالب ایمیل، تاریخ و سقف تعداد پیش از اجرا کنترل می‌شوند.
3. بازکردن Connection: با CreateObject اتصال ADODB به روش Late Binding ایجاد می‌شود.
4. ساخت Command پارامتری: متن SQL ثابت است و مقدارهای کاربر جداگانه و با نوع داده مشخص ارسال می‌شوند.
5. اجرای کوئری و دریافت تعداد: متغیر recordsAffected تعداد رکوردهای تغییرکرده را نگه می‌دارد.
6. تأیید یا بازگردانی: تنها زمانی Commit انجام می‌شود که نتیجه مطابق انتظار باشد؛ در غیر این صورت Rollback اجرا می‌شود.
7. آزادسازی منابع: Recordset و Connection در مسیر موفقیت و مسیر خطا بسته می‌شوند.

در پروژه واقعی بهتر است هر عملیات در یک جدول Log ثبت شود. اطلاعات مفید شامل نام روال، کاربر ویندوز، نام Workbook، مسیر پایگاه داده، نوع عملیات، کلید رکورد، مقدار قبلی و جدید، تعداد رکوردهای تحت تأثیر، زمان اجرا و شماره خطا است. البته نباید داده‌های حساس را بدون ضرورت در فایل متنی یا جدول بدون حفاظت ذخیره کرد.

خطاهای رایج و روش رفع آن‌ها

یکی از خطاهای پنهان، چسباندن تاریخ یا عدد اعشاری به رشته SQL است. قالب روز و ماه در سیستم‌ها متفاوت است و جداکننده اعشار نیز می‌تواند تغییر کند. پارامتر باعث می‌شود مقدار به‌صورت Date یا Number واقعی به Provider برسد و به تفسیر یک رشته وابسته نباشد.

بهترین روش‌ها برای ویرایش و حذف داده‌ها

• برای ویرایش یک رکورد از کلید اصلی یا فیلد Unique و ایندکس‌شده استفاده کنید.
• همان شرط را ابتدا با SELECT اجرا و رکوردهای هدف را مشاهده یا شمارش کنید.
• ورودی خالی، صفر، تاریخ نامعتبر و مقدار پیش‌فرض ناخواسته را رد کنید.
• به‌جای اتصال متن سلول‌ها به SQL از Command پارامتری استفاده کنید.
• برای عملیات خودکار یک سقف RecordsAffected تعیین کنید.
• چند تغییر وابسته را داخل یک تراکنش انجام دهید.
• نسخه پشتیبان معتبر داشته باشید و بازیابی آن را قبلاً آزمایش کنید.
• نوع عملیات و تعداد رکوردها را بدون افشای اطلاعات غیرضروری ثبت کنید.
• پیش از حذف رکورد اصلی، روابط و Cascade Delete را بررسی نمایید.
• در صورت نیاز به حسابرسی یا بازگردانی، حذف نرم را ترجیح دهید.

الگوی حذف نرم

UPDATE Customers
SET IsDeleted = True,
    DeletedAt = Now()
WHERE CustomerID = 42;

در حذف نرم، رکورد در جدول باقی می‌ماند اما با یک پرچم از نتایج عادی کنار گذاشته می‌شود. همه کوئری‌های معمول باید شرطی مانند WHERE IsDeleted = False داشته باشند. مزیت این روش امکان بازیابی و حسابرسی بهتر است. هزینه آن افزایش حجم و ضرورت رعایت همیشگی شرط وضعیت است. پس از پایان دوره نگهداری می‌توان رکوردهای علامت‌گذاری‌شده را با یک فرایند کنترل‌شده پاک‌سازی کرد.

موضوعات مرتبط

عملیات UPDATE و DELETE به مباحث قبلی SQL اکسس وابسته‌اند. شرط WHERE تعیین می‌کند کدام رکوردها تغییر کنند. کلید اصلی و ایندکس‌ها باعث هدف‌گیری دقیق‌تر و اجرای سریع‌تر می‌شوند. کلید خارجی و قوانین ارتباطی مشخص می‌کنند حذف رکورد اصلی مجاز است یا خیر. شناخت JOIN نیز برای شناسایی رکوردهای مرتبط مفید است، هرچند Action Queryهای مبتنی بر چند جدول همیشه قابل ویرایش نیستند و باید روی نسخه آزمایشی بررسی شوند.

پس از این مبحث، یادگیری Query پارامتری، تراکنش، اعتبارسنجی داده، جدول ثبت رویداد و ساخت توابع کمکی پایگاه داده در VBA مسیر مناسبی است. ترکیب این موضوعات باعث می‌شود ماکروهای پراکنده به یک لایه مدیریت داده قابل نگهداری تبدیل شوند.

جمع‌بندی

اجرای UPDATE و DELETE در SQL اکسس از محیط Excel VBA از نظر دستور ساده است، اما اتوماسیون قابل اعتماد به کنترل‌های اطراف آن وابسته است. شرط دقیق، پارامتر تایپ‌شده، اعتبارسنجی ورودی، پیش‌نمایش، شمارش رکوردها، تراکنش، نسخه پشتیبان و مدیریت خطا را به بخشی ثابت از هر روال تبدیل کنید. در عملیات برگشت‌ناپذیر، توقف برنامه هنگام مشاهده نتیجه غیرمنتظره بسیار بهتر از ادامه خودکار است.

یک روال حرفه‌ای فقط SQL را اجرا نمی‌کند؛ بلکه ثابت می‌کند فایل درست انتخاب شده، ورودی معتبر است، رکوردهای هدف شناخته شده‌اند، تعداد تغییر در محدوده مجاز قرار دارد و در صورت خطا مسیر Rollback یا Restore وجود دارد. با این الگو می‌توان Action Queryهای اکسس را از ماکروهای پرریسک به ابزارهای تکرارپذیر و قابل اعتماد برای مدیریت داده تبدیل کرد.