What is the best way to delete all the data in a table using code?

I know how to open the recordset and use a while not rec.EOF and delete everything row by row, but is there a way to select all the records and delete en-masse? (like the manual way)

Thanks

I figured it out, create a delete query and then invoke it....

There are a couple of ways of doing this depending on where your DBMS is. You can create a local delete query for local tables and then use DoCmd.OpenQuery (after using DoCmd.SetWarnings False, of course), or you can open a recordset on a remote DBMS using "DELETE FROM MyRemoteTable etc" with rs.Open. It looks like you found your own solution though.

mmcdonal

Look it up at: http://wrox.books24x7.com

You can use the DELETE statement to delete records from table and if you want to delete all records and set the primary to again starting from zero then use the "Truncate Table <table name>" command.



(*_*)

Numan
--------------------------------------------------
It is not important what you get But important is how you got it

I know you can use that with a SQL DBMS, but I didn't think Access supports it. Do you have any code to invoke this with a Jet DBMS?

This is a good solution unless a column in your table is a foreign key in another table.

mmcdonal

Look it up at: http://wrox.books24x7.com

My favorite way to delete a table is:
DoCmd.RunSQL "delete * from tablename"