Delete a table created by a Query

[hellboy_ga]

Hi

I'm running a Make Table Query and I want to delete the table (created
by this query) after I extract the data. This is because I call the
requery function and it gives me an error saying "the table is already
in use by another process". Then it points to the command that I gave
to delete the table at the end of "After Update" for the combo box. The
command that I used was

DoCmd.DeleteObject acTable, "Temp"

and it doesn't work. I also tried creating a macro but again, they
intefere...

Please help

thanks

 

[hellboy_ga]

ANYONE?

 

[Kou Vang]

Sounds like a Temporary QueryDef would work best, plus its faster than the
DoCmd, without all the annoying Warnings. Try this:

Dim Db as Database
Dim Qd as Dao.QueryDef

Set Db = CurrentDb
Set Qd= Db.CreateQueryDef(" ","Delete * From TableName")

Qd.Execute

Qd.close
Set Qd = Nothing
Db.close
Set DB = Nothing

Hope it works! Good Luck!

 

[hellboy_ga]

thankx a bunch...it works...the error still pops up saying "the table
will be deleted before the query is performed" and when I click ok, it
continues....Don't think anything can be done about that...right?

thanks again

 

[bob]

wrong

put this before: docmd.setwarning false


put this after: docmd.setwarnings true

 

[RD]

Is there a reason you can't extract the data from a regular select query thereby
not creating the table in the first place?

 

[Tim Ferguson]

thankx a bunch...it works...the error still pops up saying "the table
will be deleted before the query is performed" and when I click ok, it
continues....Don't think anything can be done about that...right?

If you are using a MakeTable query, then you actually need to remove the
old temp table rather than empty it: for that you would need a DROP TABLE
command not a DELETE FROM.

On the other hand, emptying the table is probably quicker and safer as
long as the other query is an INSERT rather than a SELECT INTO (i.e.
append query rather than make table).

Finally, SetWarnings is a terribly dangerous method, because sooner or
later the warnings get left off and then you delete all your customer
data by accident. It's far better to use the .Execute method since that
gives you a trappable error too.

Try something like this:

set db = CurrentDB()

' remove the old table
jetSQL = "DROP TABLE MyTempTable"
db.Execute jetSQL, dbFailOnError

' run the maketable query
set qdf = QueryDefs("MyMakeTableQuery")
qdf.Execute dbFailOnError

' and check it all
debug.print DCount("*", "MyTempTable")

A better solution, though, is to create a brand new mbd to hold the temp
table each time and delete it when the applicatioin closes. Recurrent
emptying and loading tables leads to bloated files; dropping and creating
objects bloats them even faster; and both increase the risk of file
corruption. Take regular backups!

Hope that helps


Tim F

 

[hellboy_ga]

thank you very much, it solved my problem..