472,353 Members | 1,490 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

DROP TABLE syntax - use with Select?

Can the DROP TABLE statement be used with a select or where statement?

DROP TABLE SELECT *
FROM tblTablesImported
WHERE Import_ID Not In
(SELECT [Table_ID] FROM tblTablesInternal);

Or do I have to supply a parameter like:

DROP TABLE Sheet1

Thanks in advance.
Nov 13 '05 #1
14 8932
Deko,
So, you only want to dump particular tables? As in, drop only the tables
that are not internal tables? Hmmm. Dunno how to write a DROP statement
that only drops some tables and not others without a way to group them
through membership in a tablespace or some such. And . . . my particular
habit is to build temporary tables that get rows added to them, work done on
the rows, then left alone until the next run. That way if something goes
awry I can at least look at the rows in the temp table to see if I can
discern what happened and also maybe correct the work that didn't happen the
right way. To do what you want I'd tend to write some VBA that used an
ADODB.COMMAND object and a loop that iterated through a list of tables I
wanted dropped, generating my DROP statement on the fly with each iteration
and executed the generated command. Not purely SQL, but good enough.

"deko" <de**@hotmail.com> wrote in message
news:b2*****************@newssvr13.news.prodigy.co m...
Can the DROP TABLE statement be used with a select or where statement?

DROP TABLE SELECT *
FROM tblTablesImported
WHERE Import_ID Not In
(SELECT [Table_ID] FROM tblTablesInternal);

Or do I have to supply a parameter like:

DROP TABLE Sheet1

Thanks in advance.

Nov 13 '05 #2
huh what? You mean you have a table (tblTablesImported), and you want
to drop some of them through code?

Ummm... How about opening a forwardonly recordset of tables to drop

SELECT *
FROM tblTablesImported
WHERE Import_ID Not In
(SELECT [Table_ID] FROM tblTablesInternal)

and then looping through the resulting recordset and doing something
like

strSQL = "DROP TABLE [" & rs.Fields("TableName") & "]"
currentdb.Execute strSQL, dbFailOnError

or some such thing...

Nov 13 '05 #3
> So, you only want to dump particular tables? As in, drop only the tables
that are not internal tables? Hmmm. Dunno how to write a DROP statement
that only drops some tables and not others without a way to group them
through membership in a tablespace or some such. And . . . my particular
habit is to build temporary tables that get rows added to them, work done on the rows, then left alone until the next run. That way if something goes
awry I can at least look at the rows in the temp table to see if I can
discern what happened and also maybe correct the work that didn't happen the right way. To do what you want I'd tend to write some VBA that used an
ADODB.COMMAND object and a loop that iterated through a list of tables I
wanted dropped, generating my DROP statement on the fly with each iteration and executed the generated command. Not purely SQL, but good enough.


TIMTOWTDI, for sure. looping through a recordset works. it wd be nice if I
cd do it with ddl. but I'm not sure how to combine select and where
statements with DROP or CREATE.
Nov 13 '05 #4
Deko,
So, you only want to dump particular tables? As in, drop only the tables
that are not internal tables? Hmmm. Dunno how to write a DROP statement
that only drops some tables and not others without a way to group them
through membership in a tablespace or some such. And . . . my particular
habit is to build temporary tables that get rows added to them, work done on
the rows, then left alone until the next run. That way if something goes
awry I can at least look at the rows in the temp table to see if I can
discern what happened and also maybe correct the work that didn't happen the
right way. To do what you want I'd tend to write some VBA that used an
ADODB.COMMAND object and a loop that iterated through a list of tables I
wanted dropped, generating my DROP statement on the fly with each iteration
and executed the generated command. Not purely SQL, but good enough.

"deko" <de**@hotmail.com> wrote in message
news:b2*****************@newssvr13.news.prodigy.co m...
Can the DROP TABLE statement be used with a select or where statement?

DROP TABLE SELECT *
FROM tblTablesImported
WHERE Import_ID Not In
(SELECT [Table_ID] FROM tblTablesInternal);

Or do I have to supply a parameter like:

DROP TABLE Sheet1

Thanks in advance.

Nov 13 '05 #5
> So, you only want to dump particular tables? As in, drop only the tables
that are not internal tables? Hmmm. Dunno how to write a DROP statement
that only drops some tables and not others without a way to group them
through membership in a tablespace or some such. And . . . my particular
habit is to build temporary tables that get rows added to them, work done on the rows, then left alone until the next run. That way if something goes
awry I can at least look at the rows in the temp table to see if I can
discern what happened and also maybe correct the work that didn't happen the right way. To do what you want I'd tend to write some VBA that used an
ADODB.COMMAND object and a loop that iterated through a list of tables I
wanted dropped, generating my DROP statement on the fly with each iteration and executed the generated command. Not purely SQL, but good enough.


TIMTOWTDI, for sure. looping through a recordset works. it wd be nice if I
cd do it with ddl. but I'm not sure how to combine select and where
statements with DROP or CREATE.
Nov 13 '05 #6
deko wrote:
TIMTOWTDI, for sure. looping through a recordset works. it wd be nice if I
cd do it with ddl. but I'm not sure how to combine select and where
statements with DROP or CREATE.


Access supports only one SQL statement at a time in queries.

--
This sig left intentionally blank
Nov 13 '05 #7
Deko,
Kinda sorta like this:

Create a string variable to hold the SQL statements you need.
Create a recordset object which will return a list of tables to drop.
Create a command object which will execute the DROP statement.
Set the value of the string variable to a SELECT statement which will return
a list of tables to drop.
Open the recordset object
Test for .BOF and .EOF because the only time this is true is when no records
are returned.
If Some Records Are Returned Then
Move to the First Record
Do
Set the value of the string variable to a DROP statement that
includes the table name listed
in the first record of your recordset object.
Have the command object execute the DROP statement you just
concantenated together.
Move to the Next Record in the recordset object.
While Not .EOF

This basic algorythm is rather useful for any number of tasks where a set of
tables or a set of rows in a table need to be modified and there is no clean
way to do the work just using SQL.

"deko" <de**@hotmail.com> wrote in message
news:5q*****************@newssvr13.news.prodigy.co m...

TIMTOWTDI, for sure. looping through a recordset works. it wd be nice if
I
cd do it with ddl. but I'm not sure how to combine select and where
statements with DROP or CREATE.

Nov 13 '05 #8
> Create a string variable to hold the SQL statements you need.
***yup
Create a recordset object which will return a list of tables to drop. ***yup Create a command object which will execute the DROP statement. ***command object?? Set the value of the string variable to a SELECT statement which will return a list of tables to drop. ***ok Open the recordset object
Test for .BOF and .EOF because the only time this is true is when no records are returned. ***understood If Some Records Are Returned Then
Move to the First Record
Do
Set the value of the string variable to a DROP statement that
includes the table name listed
in the first record of your recordset object.
Have the command object execute the DROP statement you just
concantenated together.
Move to the Next Record in the recordset object.
While Not .EOF ***no sweat This basic algorythm is rather useful for any number of tasks where a set of tables or a set of rows in a table need to be modified and there is no clean way to do the work just using SQL.

***10-4

I think I understand everything you said except "command object"

Here's what I came up with:

Set rst = db.OpenRecordset("qryImexDropExternal")
Do While Not rst.EOF
tdfs.Delete rst!ExternalTable
rst.MoveNext
Loop

I test for records before creating the rst.

I omitted the code that creates the objects, but you get the idea. The
challenge was getting qryImexDropExternal to return the correct records.
AFAIK, I cannot use "DROP TABLE ..." with any parameters like "(SELECT
[ExternalTable] FROM qryImexDropInternal)". But the rst works fine.
Nov 13 '05 #9
Deko,
Well . . . yah. In DAO (Data Access Objects) and Jet I don't remember such
a creature as a Command object. But . . . I've been using the ADODB library
as my primary means of interacting with an Access database for a few years
and silly me, figured you were too. Ok, in your VB Editor Window under
Tools is a References menu option. This brings up a dialog box that has
some stuff checked off at the top of the list. Typically, the Access type
library is selected, VB is selected, the Office type library is often
selected, and a type library for interacting with the Jet database engine is
selected. It's usually either DAO or ADO (Access Data Objects). To use
Command objects you will need to include ADODB in your project by clicking
on its checkbox in that dialog box. I hope I am explaining myself well.

"deko" <de**@hotmail.com> wrote in message
news:2t*****************@newssvr13.news.prodigy.co m...
Create a command object which will execute the DROP statement.

***command object??

Nov 13 '05 #10
Deko,
Right, but for example "strSQL = 'DROP TABLE ' & ExternalTable & ';" as a VB
statement would result in strSQL equalling a DROP TABLE statement with a
table name concantenated into it. Then you can use an ADODB.Command object
to execute the SQL statement in strSQL.

"deko" <de**@hotmail.com> wrote in message
news:2t*****************@newssvr13.news.prodigy.co m...
AFAIK, I cannot use "DROP TABLE ..." with any parameters like "(SELECT
[ExternalTable] FROM qryImexDropInternal)". But the rst works fine.

Nov 13 '05 #11
> Well . . . yah. In DAO (Data Access Objects) and Jet I don't remember
such
a creature as a Command object. But . . . I've been using the ADODB library as my primary means of interacting with an Access database for a few years
and silly me, figured you were too.
Actually, I've been avoiding ADO like the plague. I see no reason to use
it - DAO works just fine.
Ok, in your VB Editor Window under
Tools is a References menu option. This brings up a dialog box that has
some stuff checked off at the top of the list. Typically, the Access type
library is selected, VB is selected, the Office type library is often
selected, and a type library for interacting with the Jet database engine is selected. It's usually either DAO or ADO (Access Data Objects). To use
Command objects you will need to include ADODB in your project by clicking
on its checkbox in that dialog box. I hope I am explaining myself well.


I'm pretty sure that reference is checked by default. In the references
window it's called "Microsoft AxtiveX DataObjects 2.5 Library". Here's a
listing of what references I have in my mdb (unfortunately the names don't
match what's in the References window):

?ReferenceInfo
Reference: VBA
Location: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
Reference: Access
Location: C:\Program Files\Microsoft Office\OFFICE11\MSACC.OLB
Reference: stdole
Location: C:\WINDOWS\System32\stdole2.tlb
Reference: DAO
Location: C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
Reference: ADODB
Location: C:\Program Files\Common Files\System\ado\msado25.tlb
Reference: Office
Location: C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL
Reference: Outlook
Location: C:\Program Files\Microsoft Office\OFFICE11\msoutl.olb
Reference: SHDocVw
Location: C:\WINDOWS\System32\shdocvw.dll
Reference: Word
Location: C:\Program Files\Microsoft Office\OFFICE11\MSWORD.OLB
Reference: Excel
Location: C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE

I'm not sure I need OLE Automation (stdole) or even ADODB. Perhaps I'll try
unchecking them and see what happens, if I ever get around to it...

Nov 13 '05 #12
> Right, but for example "strSQL = 'DROP TABLE ' & ExternalTable & ';" as a
VB
statement would result in strSQL equalling a DROP TABLE statement with a
table name concantenated into it. Then you can use an ADODB.Command object to execute the SQL statement in strSQL.


From MSDN:

The Command object represents an SQL statement, stored procedure, or any
other command that can be processed by the data source. The Command object
is similar to a DAO temporary QueryDef object, including a Parameters
collection that can accept input and output parameters. You can execute a
command string on a Connection object (by using the Execute method) or pass
a query string as part of opening a Recordset object (as the Source
property), without explicitly creating a Command object. The Command object
is most useful when you want to define query parameters, or execute a stored
procedure that returns output parameters.

well la de da...

what's wrong with:

Dim db as DAO.Database
Set db = CurrentDb
db.Execute "qryName"
Nov 13 '05 #13
Deko,
Then I remembered that a DAO QueryDef can be used similar to an
ADODB.Command object. It's been too long since I worked with DAO for me to
remember the specifics but you should be able to use a DAO QueryDef in a
similar fashion and my algorythm will still work.

"deko" <de**@hotmail.com> wrote in message
news:nx*****************@newssvr13.news.prodigy.co m...
Actually, I've been avoiding ADO like the plague. I see no reason to use
it - DAO works just fine.

Nov 13 '05 #14
Deko,
Nothing. The algorhythm should work the same regardless of whether you use
DAO or ADO. Details are different, of course, but that's what they pay us
programmers for.

well la de da...

what's wrong with:

Dim db as DAO.Database
Set db = CurrentDb
db.Execute "qryName"

Nov 13 '05 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: 73blazer | last post by:
We are migrating a customer from Version 7.1 FP3, to Version 8.2 (8.1 FP8). For the most part, things are faster, but there is one query that is...
1
by: deko | last post by:
Can the DROP TABLE statement be used with a select or where statement? DROP TABLE SELECT * FROM tblTablesImported WHERE Import_ID Not In...
2
by: Eric Dan | last post by:
Hi, Even tough I was able to implement what I want in a weird and non efficient way, I would like to get an opinion what is the right way to...
3
by: pmud | last post by:
Hi, I have 2 drop down lists on an asp.Net page. The 1st contains alphabets. When the user selects an alphabet frm the first list, the second...
1
by: Dragan Matic | last post by:
if exists (select * from sysobjects where id = object_id(N'.') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table . GO For instance, this...
7
by: callawayglfr | last post by:
I am building a database in access where I have a drop down box that relates to a text box, that part I have working but when someone selects...
4
by: Antonio | last post by:
Can somebody please tell me how to bind a drop-down (states) to a table programmatically? I have the connection information in the procedure and I...
5
by: sreemati | last post by:
Hi I am working on SQL SERVER 200 and I am trying to drop the default constraints set in few tables. I tired to follow the instructions given in...
11
by: tokcy | last post by:
Hi everyone, I am new in php and ajax, i am facing the prob while i click on element of first drop down then in second dropdown all element showl...
1
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.