Searching database for a specific table

[austins54]

I have been tasked to work on a database that was created in Access 2.0 and now has been upgraded to Access 2000. The problem that I am having is that there are tables and queries that are created when a user selects certain things and the deleted when a user clicks on done. However if the user doesn't click on done and just closes the application then the tables don't get deleted and the next time the user goes to again select certain things then all they get is a message that the table or query already exits. Is there VBA code that will search through the database for a specific table and if it does exist then delete it and if it doesn't then continue on with its task?

 

[Pat Hartman]

Here's a function that you can use to determine if a table exists. You can run it before you run the maketable query and delete the existing table if it wasn't already deleted.

Function IsTableQuery(DbName As String, TName As String) As Integer
Dim DB As Database, Found As Integer, Test As String
Const NAME_NOT_IN_COLLECTION = 3265

Found = False
On Error Resume Next

If Trim$(DbName) = "" Then
Set DB = CurrentDb()
Else
Set DB = DBEngine.Workspaces(0).OpenDatabase(DbName)

If Err Then
MsgBox "Could not find database to open: " & DbName
IsTableQuery = False
Exit Function
End If
End If

Test = DB.TableDefs(TName).Name
If Err <> NAME_NOT_IN_COLLECTION Then Found = True

DB.Close

IsTableQuery = Found

End Function