If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Equivalent of DROP TABLE IF EXIST?
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 -- Mike Walsh - mike underscore walsh at mindsping dot com |
#2
|
|||
|
|||
Equivalent of DROP TABLE IF EXIST?
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 [Table], MSysObjects.Name, MSysObjects.Type FROM MSysObjects WHERE (((MSysObjects.Type)=1)) ORDER BY "Table", MSysObjects.Name; -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Mike Walsh" wrote: 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 -- Mike Walsh - mike underscore walsh at mindsping dot com |
#3
|
|||
|
|||
Equivalent of DROP TABLE IF EXIST?
Mike Walsh wrote:
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 -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#4
|
|||
|
|||
Equivalent of DROP TABLE IF EXIST?
"Bob Barrows [MVP]" wrote in message ... [ ... 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 |
#5
|
|||
|
|||
Equivalent of DROP TABLE IF EXIST?
Mike Walsh wrote:
"Bob Barrows [MVP]" wrote in message ... [ ... 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. -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#6
|
|||
|
|||
Equivalent of DROP TABLE IF EXIST?
"Bob Barrows [MVP]" wrote in message [ ... snipped ... ] 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. [ ... 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 |
Thread Tools | |
Display Modes | |
|
|