Overwriting of new table

[GeorgeMar]

I am converting my application from Docmd.RunSQL to
db.Execute. One sql that used to work properly to create
a new table, it would overwrite the table if it exists, by
setting the setWarnings to false.

Now, with db.execute, even though I still have setWarnings
to false, it gives an error "... the table aleady exists.."

What is the best way to indicate an overwrite?

many thanks
george

 

[Allen Browne]

strTable = "Table1"
If Not IsNull(DLookup("ID", "MSysObjects", "(Type = 1) AND (Name = """ &
strTable & """)")) Then
db.Execute "DROP TABLE " & strTable & ";", dbFailOnError
End If

 

[GeorgeMar]

Thank you Allen

I have always been worried about using DROP TABLE. I
don't know what consequence there may be for repetitively
DROPPING and CREATING a new table over time.

regards
george

 

[Allen Browne]

Rather than drop and recreate the same table, just empty it:
db.Execute "DELETE FORM Table1;"

 

[GeorgeMar]

Allen

Normally, that is what I do. I have a situation where I
need to store the results of a GROUP BY query into a table
for future reference. The problem, is that I cannot have
GROUP BY and UPDATE at the same time. However, I can have
a GROUP BY and MAKE TABLE.

Is there a way around that?

regsrds
george

 

[Allen Browne]

Did you try GROUP BY and INSERT?