Delete tables if the exist

[dumbledown]

I have several tables that may or may not exist in my database (e.g. tableAPPLE, tableORANGE, tablePEAR)



How would I write code to delete these tables if they exist?

and for a bonus is it possible to delete text files (knowing the folder) in the same way? so if APPLE.txt and ORANGE.txt are present they are deleted, but if PEAR.txt isn't nothing happens?

Many thanks

[PRMiller]

I have several tables that may or may not exist in my database (e.g. tableAPPLE, tableORANGE, tablePEAR)

How would I write code to delete these tables if they exist?

Try something like this:

Private Sub DeleteSomeTable()

    Dim dbCurrent As DAO.Database
    Dim strTable As String
    Dim tdfTable As DAO.TableDef
    
    Set dbCurrent = CurrentDb
    strTable = "SomeTableName"
    
    For Each tdfTable In dbCurrent.TableDefs
        If tdfTable.Name = strTable Then
            dbCurrent.TableDefs.Delete (strTable)
        End If
    Next

    Set tdf = Nothing
    Set dbCurrent = Nothing

End Sub

and for a bonus is it possible to delete text files (knowing the folder) in the same way? so if APPLE.txt and ORANGE.txt are present they are deleted, but if PEAR.txt isn't nothing happens?

Use the FileSystemObject:

Private Sub TestDelete()
    
    Dim fs As Object
    Dim strFileName As String
    Dim strFilePath As String
    
    strFileName = "apple.txt"
    strFilePath = "C:\Documents and Settings\prmiller\My Documents\"
    If FileExists(strFilePath & strFileName) = False Then
        Exit Sub
    End If
    'strFileName = Dir(strFilePath)
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.DeleteFile strFilePath & strFileName
    Set fs = Nothing
End Sub

This might be sloppy (wrote it before 6am) but it should do the trick. I left the "strFileName = Dir(strFilePath)" in there but commented out to give you some ideas -- you could adapt this to check for the existence of ANY file, then perhaps write a Case statement to tell the application what to do depending on the filename.

For the FileExists function, see Allen Browne's website at http://allenbrowne.com/func-11.html.