Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    10

    Unanswered: Problems running a macro to delete a table

    Hello,

    Access 2000

    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

    TIA

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    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.


    Code:
    Public Function DeleteTables(pTableName As String)
        On Error GoTo Err_DeleteTables
        
        DoCmd.DeleteObject acTable, pTableName
        Exit Function
        
    Err_DeleteTables:
        'if table doesn't exist skip
        If Err.Number = 7874 Then
            Resume Next
        'else show user error
        Else: MsgBox Err.Number & ": " & Err.Description
        End If
    End Function

    Canupus

    Quote Originally Posted by 24jedi
    Hello,

    Access 2000

    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

    TIA

  3. #3
    Join Date
    Nov 2003
    Posts
    10
    canupus...Thanks

    How would I pass the table name from the macro. Honestly, I have done very little using VBA.

    Thanks again.

  4. #4
    Join Date
    Nov 2009
    Posts
    1

    How I was able to use the function

    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.

    Here is an example of the RunCode

    DeleteTables("myFirstTable")

    The name of the table is in quotes.

  5. #5
    Join Date
    May 2014
    Posts
    1

    Deleting a table

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •