DROP TABLE syntax - use with Select?

[deko]

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.

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

[pietlinden]

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

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

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

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

[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.com> wrote in message
news:2t*****************@newssvr13.news.prodigy.co m...

Create a command object which will execute the DROP statement.

***command object??

[Alan Webb]

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.

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

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

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"

[Alan Webb]

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.

[Alan Webb]

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"