MS Access/ VBA : add if condition to vba code

Question

I have the following code :

Private Sub Command66_Click()
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "Table1"
DoCmd.DeleteObject acTable, "Table2"
DoCmd.DeleteObject acTable, "Table3"
DoCmd.SetWarnings True
End Sub

The problem is that if "Table1" is not present,I receive an VBA/debug Error. Can I add some condition that if the script doesn't find "Table1" it should go further and delete the other 2 tables without returning any error message?

See if it exists first: [how to check if table is exists or not in ms access for vb macros?](http://stackoverflow.com/questions/3350645/how-to-check-if-table-is-exists-or-not-in-ms-access-for-vb-macros) — Alex K., Jan 27 '16 at 16:06

score 4 · Accepted Answer · answered Jan 27 '16 at 16:04

You mean error trapping? Here's an example of how you can "trap" an error - If an error occured trying to delete Table1 it will ask you if you want to continue:

Private Sub Command66_Click()
DoCmd.SetWarnings False

On Error Resume Next
DoCmd.DeleteObject acTable, "Table1"

If Err.Number > 0 Then
    Err.Clear
    On Error GoTo 0
    If MsgBox("There was an error trying to delete Table1, do you want to continue?", vbYesNo) = vbYes Then
        DoCmd.DeleteObject acTable, "Table2"
        DoCmd.DeleteObject acTable, "Table3"
    End If
End If
DoCmd.SetWarnings True
End Sub

score 2 · Answer 2 · edited May 23 '17 at 12:15

If you decide to trap and ignore the error, start by ignoring only error 7874 (Microsoft Access can't find the object ''.) You may prefer not to ignore other errors, such as when the table is in use.

Private Sub Command66_Click()

    Dim strMsg As String

On Error GoTo ErrorHandler

    DoCmd.DeleteObject acTable, "Table1"
    DoCmd.DeleteObject acTable, "Table2"
    DoCmd.DeleteObject acTable, "Table3"

ExitHere:
    Exit Sub

ErrorHandler:
    Select Case Err.Number
    Case 7874 'Microsoft Access can't find the object
        Resume Next
    Case Else
        strMsg = "Error " & Err.Number & " (" & Err.Description _
            & ") in procedure Command66_Click"
        MsgBox strMsg
        GoTo ExitHere
    End Select
End Sub

Notes:

If you discover other errors you wish to ignore, you can add those error numbers to the first Case statement: Case 7874, <another number here>
I don't see any reason to turn SetWarnings off in this procedure.
Consider Alex's suggestion to check whether the table exists before attempting to delete it.

Arctics · Answer 3 · 2016-02-01T20:00:58.003

-1

Well, if you want to delete table that only exists in Access then you can simply check for the object, here is how you normally do it:

On Error Resume Next
If IsObject(CurrentDb.TableDefs("Table1")) Then
    DoCmd.DeleteObject acTable, "Table1"
End If

edited Feb 01 '16 at 20:00

answered Jan 27 '16 at 21:36

Arctics

1
1

did you put On Error Resume Next before if statement? – Arctics Feb 01 '16 at 20:02

MS Access/ VBA : add if condition to vba code

3 Answers3