DROP TABLE syntax - use with Select?

[deko]

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

DROP TABLE SELECT *
FROM tblTablesImport ed
WHERE Import_ID Not In
(SELECT [Table_ID] FROM tblTablesIntern al);

Or do I have to supply a parameter like:

DROP TABLE Sheet1

Thanks in advance.

[Alan Webb]

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.c om> wrote in message
news:b2******** *********@newss vr13.news.prodi gy.com...

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

DROP TABLE SELECT *
FROM tblTablesImport ed
WHERE Import_ID Not In
(SELECT [Table_ID] FROM tblTablesIntern al);

Or do I have to supply a parameter like:

DROP TABLE Sheet1

Thanks in advance.

[pietlinden]

huh what? You mean you have a table (tblTablesImpor ted), and you want
to drop some of them through code?

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

SELECT *
FROM tblTablesImport ed
WHERE Import_ID Not In
(SELECT [Table_ID] FROM tblTablesIntern al)

and then looping through the resulting recordset and doing something
like

strSQL = "DROP TABLE [" & rs.Fields("Tabl eName") & "]"
currentdb.Execu te strSQL, dbFailOnError

or some such thing...

[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.

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.

[Alan Webb]

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.c om> wrote in message
news:b2******** *********@newss vr13.news.prodi gy.com...

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

DROP TABLE SELECT *
FROM tblTablesImport ed
WHERE Import_ID Not In
(SELECT [Table_ID] FROM tblTablesIntern al);

Or do I have to supply a parameter like:

DROP TABLE Sheet1

Thanks in advance.

[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.

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.

[Trevor Best]

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

[Alan Webb]

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.c om> wrote in message
news:5q******** *********@newss vr13.news.prodi gy.com...

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.

[deko]

> 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.OpenRecordse t("qryImexDropE xternal")
Do While Not rst.EOF
tdfs.Delete rst!ExternalTab le
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 qryImexDropExte rnal to return the correct records.
AFAIK, I cannot use "DROP TABLE ..." with any parameters like "(SELECT
[ExternalTable] FROM qryImexDropInte rnal)". But the rst works fine.

[Alan Webb]

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.c om> wrote in message
news:2t******** *********@newss vr13.news.prodi gy.com...

Create a command object which will execute the DROP statement.

***command object??