Conditional Statement

[Mikk]

When I boot up my database a macro deletes a table and
runs a seperate macro which in turn creates a table of
the same name. However, there is the rare occasion that
the seperate macro does not create a table. When this
happens I get an error when I boot up the database. Is
there a conditional statement that I can put in the macro
that will delete the table only if it exists?

 

[Steve Schapel]

Mikk,

AFAIK you can't do exactly what you ask with a macro. A workaround is
to run your Make-Table query twice, once before and once after the
delete. The one before will create the table if it's not there, and
overwrite the existing table if it is there... either way it doesn't
matter anyway because it's going to get trashed, and you will be assured
that there is one to delete so the DeleteObject macro action won't fail.
By the way, why is your existing make-table in a "sepaarate mmacro"?

 

[Jim/Chris]

I got this from a Dirk Goldgar post a while back.

Function fncTableExists(TableName As String) As Boolean
If Len(TableName) = 0 Then Err.Raise 5
On Error Resume Next
fncTableExists = IsObject(CurrentDb.TableDefs(TableName))
End Function

You would copy that code, paste it into a standard module,
and save the module. Then you could use it in a macro
condition, like this:

Condition: fncTableExists("MyTable")
Action: DeleteObject
ObjectType: Table
ObjectName: MyTable

I'd point out that, as long as you need to use some code
anyway, you
might rewrite your macro completely in code. The part that
deletes the
table could call the function first:

If fncTableExists("MyTable") Then
DoCmd.DeleteObject acTable, "MyTable"
End If

--
Dirk Goldgar, MS Access MVP

Good luck

Jim