Confirming a table exists before deleting it

[Guest]

I use a form to create a table called tblImport. If the table already exists
(from previously running the form), I use the DoCmd.DeleteObject acTable to
delete it. If the table does NOT already exist, I get an error message when
the DoCmd is executed. Is there any command that can check if a table
"exists", and then I can skip the delete if it does not.

I need to delete the table if it already exists because when the form
creates the table (it is a link to an excel file), if the table already
exists, it creates a new table and appends a number to the name (tbleImport;
tblImport1 etc.)

I tried the "Where Exists" command, but I am either doing it wrong, or it
is only for subquerries, but I can't get it to work.

I can use an on error resume next but I was hoping for something more elegent.

Thanks for any help.
Gene

 

[Wayne Morgan]

The easiest answer would probably be to just use error handling to trap the
error. You could loop through all of the tabledefs, comparing their name to
the name your looking for, but the error trapping would be quicker.

 

[Nick Coe \(UK\)]

Try wrapping your DoCmd.DeleteObject in DoCmd.SetWarnings
False and DoCmd.SetWarnings True

 

[chris ciotti]

I use a form to create a table called tblImport. If the table already
exists
(from previously running the form), I use the DoCmd.DeleteObject acTable to
delete it. If the table does NOT already exist, I get an error message when
the DoCmd is executed. Is there any command that can check if a table
"exists", and then I can skip the delete if it does not.

Hi Gene -

Access keeps a hidden table of objects (it's one of the MSys* tables) which
you can query. I can't recall the name but if you unhide the system tables
(via Tools -> Options; this might vary by Access version) and have a look you
can wrap a call to DCount in a function. If it returns 1 then the table
exists. I've done this before and it worked but I don't have the code handy
and am relying on memory (which is fragile on Sat morning

I don't know if this is better or worse than looping through the Tabledefs.
Good luck in any event.

--chris

 

[Lance McGonigal]

Hey...how about this:

function droptable()

on error resume next

docmd.runsql "drop table mytable;"

docmd.transfertext blah,blah

exit function

end function

cheers