Equivalent of DROP TABLE IF EXIST?

[Mike Walsh]

I am fairly new to access but have used MySQL for a while. The problem I
currently trying to solve makes use of a temporary table via a SELECT INTO
statement. Because I don't know if the table I am selecting into exists or
not, I'd like to drop it and have it recreated when the SELECT INTO runs.

In MySQL I would you the DROP TABLE IF EXISTS statement but Access doesn't
support that construct. Does anyone have an SQL solution for this? My
searches have returned lots of VB or C# solutions but that isn't an option
for me, I need to do it with an SQL statement.

Thanks,

Mike

 

[Jerry Whittle]

A couple of thoughts.

Instead of dropping the table, why not just DELETE * FROM to empty the
table. If it isn't there, handle the error. If it's a linked table, you can
readily drop it; however, you can empty it of records.

You could see if the table is there first. Something like below lists all
local tables (Type of 1). You could modify it to find your particular table.

SELECT "Table" AS

, MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=1))
ORDER BY "Table", MSysObjects.Name;

 

[Bob Barrows [MVP]]

I am fairly new to access but have used MySQL for a while. The
problem I currently trying to solve makes use of a temporary table
via a SELECT INTO statement. Because I don't know if the table I am
selecting into exists or not, I'd like to drop it and have it
recreated when the SELECT INTO runs.
In MySQL I would you the DROP TABLE IF EXISTS statement but Access
doesn't support that construct. Does anyone have an SQL solution for
this? My searches have returned lots of VB or C# solutions but that
isn't an option for me, I need to do it with an SQL statement.

As you can tell from Jerry's response, what you ask is not possible in a
single sql statement in Jet. You will need to do this via VBA in order to
implement either of Jerry;s suggestions, because two steps will need to be
done either way.
Using a permanent table:
1. delete existing rows
2. insert new rows (insert ... select...)

Using a temporary table
1. drop the table, handling the error if it doesn't exist
2. run your select into statement

 

[Mike Walsh]

[ ... snipped ... ]

As you can tell from Jerry's response, what you ask is not possible in a
single sql statement in Jet. You will need to do this via VBA in order to
implement either of Jerry;s suggestions, because two steps will need to be
done either way.
Using a permanent table:
1. delete existing rows
2. insert new rows (insert ... select...)

Using a temporary table
1. drop the table, handling the error if it doesn't exist
2. run your select into statement
--

[ ... snipped ... ]

A lilttle more detail on my problem, The application I am using has an
embedded Access database. VBA is not an option. At run time the
application reads a configuration file. The configuration file has the
ability to execute 4 SQL statements which the user can specify in the
configuration file.

I have to use a temporary table, the database tables and schema can't be
changed.

Is there anyway to suppress an SQL error when a SQL statement executes? If
so, I could simply drop the table and ignore that error if the table doesn't
exist.

Thanks,

Mike

 

[Bob Barrows [MVP]]

[ ... snipped ... ]

As you can tell from Jerry's response, what you ask is not possible
in a single sql statement in Jet. You will need to do this via VBA
in order to implement either of Jerry;s suggestions, because two
steps will need to be done either way.
Using a permanent table:
1. delete existing rows
2. insert new rows (insert ... select...)

Using a temporary table
1. drop the table, handling the error if it doesn't exist
2. run your select into statement
--

[ ... snipped ... ]

A lilttle more detail on my problem, The application I am using has
an embedded Access database. VBA is not an option. At run time the
application reads a configuration file. The configuration file has
the ability to execute 4 SQL statements which the user can specify in
the configuration file.

With no flow control? Bummer. It sounds as if you are stuck ...

I have to use a temporary table, the database tables and schema can't
be changed.

Wait a minute: the act of creating your temporary table changes the database
schema ... these requirements seem to be a tad contradictory.

How about using a second database, with links to the database that "can't be
changed"? Put your work table in that database and move on to the next
problem
Or does the application you are using prevent the use of multiple darabase
files ... ?

Is there anyway to suppress an SQL error when a SQL statement
executes? If so, I could simply drop the table and ignore that error
if the table doesn't exist.

Only if you're using a programming language such as VBA ... sorry.

 

[Mike Walsh]

[ ... snipped ... ]

Only if you're using a programming language such as VBA ... sorry.

[ ... snipped ... ]

I appreciate all of the advice - my software vendor made a change to the
configuration file format to allow suppression of errors so the problem is
solved in VBA within the application as was suggested in this thread.

My config file now supports a construct like this (the Error attribute is
new):

<sql1 Error="ignore">SQL statement goes here</sql1>

Thanks for the advice. Once I outlined the problem to our vendor, they were
able to make a change and issue me a patch. Problem solved.

Mike