Dropped table still found

[Your name]

I drop a table, and try to re-create it later. I get a message saying the
(dropped)table is still there.

I have tried both (obviously not at the same time):

DoCmd.RunSQL "Drop Table xxx"
DoCmd.DeleteObject acTable, "xxx"


I cannot delete the rows and re-populate because when I rebuild the table,
the number of columns varies.

It is like I need a "commit", or need to refresh a catalog or something.

Any suggestions?

Thanks in advance.

 

[Allen Browne]

Firstlly, uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html
Then compact the database to get rid of this junk:
Tools | Database Utilities | Compact

You should now be able to DROP the table, and have Access know that is is
gone. However, if the RunSQL code fails, and you have set SetWarnings to No,
you will not receive a message if it was not deleted (e.g. because a form is
using the data at the time.) To solve that, use:
dbEngine(0)(0).Execute "DROP TABLE xxx;", dbFailOnError

If you are dropping the table and then using a Make Table query to recreate
it again, there's a better way to do it. Instead of destroying and creating
it again, just empty it, and use an Append query (not Make Table) to
populate it. This kind of thing:

Dim db as DAO.Database
Dim strSql as String

Set db = CurrentDb()
strSql = "DELETE FROM Table1;"
db.Execute strSql, dbFailOnError

strSql = "INSERT INTO ...
db.Execute strSql, dbFailOnError

Set db = Nothing