Find Then Delete Table

[Lamar]

I want to check to see if a table (Storage) is in the database and then
delete it.

I know about Docmd.DeleteObject but sometimes the table will not be in the
database. So how can I check to see if the table is there and if it is then
delete. I have tried SelectObject and that does not work.

DoCmd.DeleteObject acTable, "Storage"

Thanks for any help.

 

[Klatuu]

If TableExists("Storage") Then
Docmd.DeleteObject acTable, "Storage"
End If

Public Function TableExists(strTableName As String) As Boolean
Dim tdfs As TableDefs
Dim tdf As TableDef

TableExists = False
Set tdfs = CurrentDb.TableDefs
For Each tdf In tdfs
If tdf.Name = strTableName Then
TableExists = True
Exit For
End If
Next tdf
Set tdfs = Nothing

End Function

 

[Lamar]

Hey Klatuu,

Can I just paste the code in an event procedure (i.e. Cmd Button Click). Do
I have call the function but it did work.

 

[Klatuu]

I have the TableExists function in a standard module where I keep alsorts of
utilities like that. The call to it can be in the Click event of a command
button.
When you say it didn't work, what happened? Error message, if so, what and
where?

 

[Lamar]

I get this message: "Compile Error: Argument not optional" I can not figure
it out?

Here is the the Call on the Click event on a cmd button:

Call TableExists '(strTableName As String) As Boolean

If TableExists("Storage") Then
DoCmd.DeleteObject acTable, "Storage"
End If

 

[Klatuu]

You are commenting out the argument. It looks like you did a copy paste that
was not correct.

Call TableExists '(strTableName As String) As Boolean
^ - Problem is here

Assuming your table name is Storage, the original code I posted should work:

If TableExists("Storage") Then
Docmd.DeleteObject acTable, "Storage"
End If

 

[Lamar]

Yes, that was the issue. I did not have to call the function. Thank you
very much.