Can't delete a table

[Vlad Chapranov]

What's wrong with this scenario?
In Form1_Load I open Access database db which may contain or not 2 queries Temp1 and Temp2 and Table TempTable, then in Command1_Click I delete all 3 objects (Temp1, Temp2, TempTable) if they exist with db.QueryDefs.Delete("Temp1"), db.QueryDefs.Delete("Temp2") and db.TableDefs.Delete("TempTable")otherwise 'On Error Resume Next'. After that I create and execute MakeTable query Temp1 with 'SELECT ... INTO' statement, which creates TempTable table. Second query is an append query which appends some records into the TempTable with 'INSERT INTO' statement. It works only with first click, with second it doesn't delete TempTable object, but appends some records into existing table. It looks like TempTable was not released after first action and it was not deleted. If I do not open it explicitly, how can I close it to allow deleting?
Thank you
Vlad

[Ravi Kiran]

Sorry, i couldn't help posting this:

Why do you want to write such "complicated" english sentences? Dont you think people can get confused?!

Just re-read this sentence of yours:
"In Form1_Load I open Access database db which may contain or not 2 queries Temp1 and Temp2 and Table TempTable, then in Command1_Click I delete all 3 objects (Temp1, Temp2, TempTable) if they exist with db.QueryDefs.Delete("Temp1"), db.QueryDefs.Delete("Temp2") and db.TableDefs.Delete("TempTable")otherwise 'On Error Resume Next'. "..

My opinion. No heart fellings please:-)
Ravi Kiran

[Ravi Kiran]

I would guess the Culprit is "On Error resume next".

When you have this line, after every line of execution, which you suspect can cause the problem,
you should check for Err.No > 0.

What could be happening is while second time around,
the lines db.QueryDefs.Delete could be raising some error.
and you may not be checking for this. So the prog
flow goes thru and the line select ..INto happens to come out with success.

You can also use "On Error goto .." and after asserting that the errors are ok use "Resume Next" in the error handler.

For example, under certain conditions Err 3021 ( no current record) may be ok, but other errors may not. So, you trap errors with

on error goto ErrCheck
..
ErrCheck:
if Err.Number = 3021 then
tmprecset.addnew ' just an example
resume next
else
' report to higher authorities!
end if




So put some code like this and see what error is raised. It gives better idea as to what is wrong.
Ravi Kiran

[Vlad Chapranov]

I've got fixed that. Table was not deleted second time because it was not visible after first deletion and creation again. I used db.TableDefs.Refresh metod before Delete method. Now it works fine.
Thank you.
Vlad