 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / February 2005DROP 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. 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. > Can the DROP TABLE statement be used with a select or where statement? > [quoted text clipped - 8 lines] > > Thanks in advance. > 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 [quoted text clipped - 8 lines] > 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. > 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.  SignatureThis sig left intentionally blank 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. > 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. > 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 [quoted text clipped - 6 lines] > 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. 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. >> Create a command object which will execute the DROP statement. > ***command object?? > 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 [quoted text clipped - 4 lines] > 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, 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. > Actually, I've been avoiding ADO like the plague. I see no reason to use > it - DAO works just fine. 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. > AFAIK, I cannot use "DROP TABLE ..." with any parameters like "(SELECT > [ExternalTable] FROM qryImexDropInternal)". But the rst works fine. > 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" 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... > [quoted text clipped - 3 lines] > Set db = CurrentDb > db.Execute "qryName" |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.