This would be a great time to use some VBA code. Here's a simple function that will delete whatever table name you pass to it and if it doesn't exist it will just skip it.
Public Function DeleteTables(pTableName As String)
On Error GoTo Err_DeleteTables
DoCmd.DeleteObject acTable, pTableName
'if table doesn't exist skip
If Err.Number = 7874 Then
'else show user error
Else: MsgBox Err.Number & ": " & Err.Description
Originally Posted by 24jedi
I have created a macro to delete a table using the "DeleteObject" Action. This works ok.
The problem is when the table doesn't exsit, the macro halts. I can't figure out how to add a condition expression to check the existance of a table and skip the action if it doesn't exist.
What can I put in the "Condition" cell that if the table doesn't exist goto the next action statement on the next line.
Basically, I am trying to delete any existing tables (if they exist) before importing a more current table from an external source.
The macro is current just two lines
>>delete exising table (skip if it doesn't exist)
>>Import new table
I had the same issue with deleting tables in macros. The function supplied by canupus did the trick.
Here is how I use it in my Access 2000 database.
1. Create a Module and copy his function into it.
2. Then in place of the DeleteObject line in my macro, I used RunCode
3. RunCode asks for the name of the function and allows you to pass the table name.
I realize this is an old post but in case anyone looks up the subject there is another alternative. From reading the question it appears VB is not a something they use. A simple solution is to always make sure the table exists. This can be done by creating a backup table. For example if the able you wish to delete in the macro is called "table1" create a backup file called "table1 backup". Then in the macro you can the CopyObject action to copy the backup table and create a new table with the name of the table you wish to delete. The CopyObject action should be placed before the DeleteObject action. This will recreate the missing table and allow the macro to continue when it reaches the DeleteObject action.