ACCESS VBA code to delete a table if it exists

9
votes

First, I must admit, that I am not trained in coding with VBA. I use MS Access macros and queries to build my application. I use some temporary import files, and need to either run a macro, or some VBA, to test if they exist, and then if they do, to delete them.

My table name is "TempImport1"

I've researched this via google searches and have come across some VBA that might work, but I am lost trying to figure out how to put the code into a module or a click sub button. I have cut/pasted VBA code under a button function in the past, and it worked, but I can't figure out why it's not working this time.

Honestly, I'm sure it's my lack of understanding of private vs public functions, as well as of course, the fact that I don't know VBA.

Here's the code I'm trying to make work:

Function IsTable(sTblName As String) As Boolean
    'does table exists and work ?
    'note: finding the name in the TableDefs collection is not enough,
    '      since the backend might be invalid or missing

    On Error GoTo TrapError
    Dim x
    x = DCount("*", sTblName)
    IsTable = True
    Exit Function
TrapError:
    Debug.Print Now, sTblName, Err.Number, Err.Description
    IsTable = False

End Function
3
vbams-access
The function is just trying to check whether the table exists. You call it like this: myTest = IsTable("table_name") and this function tries to count the number of records on this table. If the table exists, the function will be able to make the count so will return IsTable = True. If not, then the error handling above will catch the error and set IsTable = False. So the way you should use it is just to test in your code: If isTable("yourTable") Then... do something.... Else.... do something else. – Matteo NNZ

3 Answers

10
votes

First you should check whether the table exists and then you should try to close it, if it exists. Then you should set warnings to False, so it does not ask you whether you are sure that you want to delete the table.

In the example below, you delete Table3. The If Not IsNull is checking whether the table exists:

Option Compare Database
Option Explicit

Public Sub DeleteIfExists()

    Dim tableName As String
    tableName = "Table3"

    If Not IsNull(DLookup("Name", "MSysObjects", "Name='" & tableName & "'")) Then
        DoCmd.SetWarnings False
        DoCmd.Close acTable, tableName, acSaveYes
        DoCmd.DeleteObject acTable = acDefault, tableName
        Debug.Print "Table" & tableName & "deleted..."
        DoCmd.SetWarnings True
    End If

End Sub

Pretty much the code should work.

7
votes

To delete the TempImport1 table if it exists just use the below function.

Function DeleteTables()

    If Not IsNull(DLookup("Name", "MSysObjects", "Name='TempImport1' AND Type = 1")) Then
    DoCmd.DeleteObject acTable, "TempImport1"
    End If

End Function

Once the function has been created, create a macro, add the action run code then type in DeleteTables() in to the Function Name.
You then have a macro to run to delete the table if it exists.

0
votes

Checking MSysObjects (used in other answers) misreported a table as existing if it was recently deleted. I found the following test more reliable.

Option Compare Database
Option Explicit

Public Sub DeleteIfExists()

    Dim tableName As String
    tableName = "Table3"

    On Error Resume Next
    Set td = db.TableDefs(tableName)
    If Err.Number <> 0 Then
        DoCmd.SetWarnings False
        DoCmd.Close acTable, tableName, acSaveYes
        DoCmd.DeleteObject acTable = acDefault, tableName
        Debug.Print "Table" & tableName & "deleted..."
        DoCmd.SetWarnings True
    End If

End Sub