How to delete a table if it exists?

Question

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 some VBA that might work. I have cut/pasted VBA code under a button in the past, and it worked, but not this time. How do I put the code into a module or a click sub button?

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

Symbolics

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. – Siam

Answer 1

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.

Pyrography

I would separate this in to two functions: TableExists and DeleteTable. DeleteTable has no work to do if the table doesn't exist and so there is no value in stating that in the name. – Tepee

Removed the edit with Added the criteria "Type = 1" in the DLookup. Otherwise the if clause can be true even when there are only other objects (not tables) with the same name., as if I ever have an object, that I think is a table and I delete it by mistake because of the equal name, probably it will be a good thing. Still, thanks for the edit. – Pyrography

@Tepee - it is a question of taste, I guess (or listening too much of Uncle Bob). Anyway, deleting without existing and closing may result in an error, thus I will keep it as it is. – Pyrography

The two work together. DeleteTable would say If TableExists(tableName) Then. If your uncle Bob breaks his problems down in to atomic parts I would listen to him. – Tepee

@Tepee - Uncle Bob is this guy - "Robert Cecil Martin". As I said, it is a question of taste - breaking too much the code may not be ok in a big project. It depends on the most senior developer and the last book s/he read. :) In my case, I would rather keep it like this. It is a better idea to put the table name in a parameter, but then the illustrational purpose of the answer would be gone. – Pyrography

Answer 2

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.

Niggerhead

Answer 3

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

Negligible

Answer 4

Here is the version I created to get rid of import error tables. The Err. Number must be 0 to actually remove table. TRACE is my internal flag.

Public Function RemoveImportErrorTables(Optional strTableBaseName As String = "rngExportDaily_ImportErrors") As Integer
'Purpose:
'  Remove ImportError Tables
'In:
'  Tables base Name
'Out:
'  number of tables flushed
'History:
'  Created 2021-12-06 16:10 Anton Sachs; Last modified 2021-12-06 16:15 Anton Sachs
'
  Dim intResult As Integer
  Dim strTableName As String
  Dim dbCur As Database
  Dim tdfTableDef  As TableDef
  Dim intTableIndex As Integer

  On Error GoTo RemoveImportErrorTables_Err
  
  Set dbCur = CurrentDb()
  
  For intTableIndex = 0 To 100
    If intTableIndex = 0 Then
      strTableName = strTableBaseName
    Else
      strTableName = strTableBaseName & CStr(intTableIndex)
    End If
  
    On Error Resume Next
    
    Set tdfTableDef = dbCur.TableDefs(strTableName)
    
    If Err.Number = 0 Then
      DoCmd.SetWarnings False
      DoCmd.Close acTable, strTableName, acSaveYes
      DoCmd.DeleteObject acTable = acDefault, strTableName
      DoCmd.SetWarnings True
      intResult = intResult + 1
    Else
      On Error GoTo RemoveImportErrorTables_Err
      Exit For
    End If
  
  Next intTableIndex

RemoveImportErrorTables_Exit:
  RemoveImportErrorTables = intResult
  If Not tdfTableDef Is Nothing Then
    Set tdfTableDef = Nothing
  End If
  If Not dbCur Is Nothing Then
    Set dbCur = Nothing
  End If
  Exit Function

RemoveImportErrorTables_Err:
  If TRACE = 0 Then TRACE = GetStandard("Trace")

If TRACE <> False Then
  Debug.Print "Error " & Err.Number & " " & Err.Description & " in RemoveImportErrorTables"
  Err.Clear
  If TRACE = CTR±Stop Then
    Stop
    Resume Next
  End If
Else
  Err.Clear
    Resume Next
End If

RemoveImportErrorTables_Fail:
  intResult = False
  GoTo RemoveImportErrorTables_Exit
End Function

Faucet