Conditional deleting tables with macro

[Guest]

Hi,

My database does sometimes create tables (purposely) while running, which
should be deleted at quitting. The names of those tables are known. If I
delete those tables while they are not present the macro gives an error
message. What condition should be created in the macro so that it will
continue to run if a table does not exist??

Thanks
Willem

 

[tina]

AFAIK, you can't handle errors in a macro, or test for the existence of an
object, either. you'll need to use VBA code.

hth

 

[Steve Schapel]

Willem,

You can't do this directly. As far as I know, the closest you will get
is to set up a Make-Table Query for each of the tables in question (it
sounds like you might already have them?), and then put an OpenQurery
action into your macro before each DeleteObject action. If the table
already exists, the Make-Table will overwrite it, which doesn't matter
because you are trashing it anyway. And if it doesn't already exist,
well it will now, and the DeleteObject will proceed without error.

 

[Guest]

Dear Steve,

Thanks, it works.

Willem

Willem,

You can't do this directly. As far as I know, the closest you will get
is to set up a Make-Table Query for each of the tables in question (it
sounds like you might already have them?), and then put an OpenQurery
action into your macro before each DeleteObject action. If the table
already exists, the Make-Table will overwrite it, which doesn't matter
because you are trashing it anyway. And if it doesn't already exist,
well it will now, and the DeleteObject will proceed without error.

 

[Guest]

This worked nicely for me.
Sorry - I forgot where I found it.
I wrote a function in a module like this.
Function chktbl(tablename$)
On Error Resume Next
Dim tbl As TableDef
Dim db As Database
Dim TblExists
Set db = CurrentDb()
TblExists = -1
Set tbl = db.TableDefs(tablename$)
If Err = 3265 Then
TblExists = 0
End If
chktbl = TblExists
End Function

Then in the conition of the macro line to delete the table, I put
chktbl("TEMP")

where the table I wanted to delete was named TEMP.