- database solutions and downloads for microsoft access

SQL Delete in Microsoft Access

The DELETE Operand in SQL for MS ACCESS:

DELETE is one of the simpler SQL commands, and can be used to delete rows (or records) permanently from a table. It's very useful if you're trying to purge old data from your records. If you want to archive your records, however, you're better off SELECTing records and saving them to a new table first (which you'll archive), and then DELETEing the same records.

Syntax for the DELETE command

DELETE is always found as DELETE FROM, not just by itself. Syntax is generally:

DELETE FROM table WHERE conditional

For instance, you can delete all the records in your table where your records have been inactive for more than three years - this is a good way to purge old customers who you're certain aren't coming back, or, depending on how your tables are set up, all old customer orders by all customers.

DELETE FROM Customers 
WHERE LastPurchaseDate < January1,2002

This does not just eliminate one customer record; it eliminates every record that matches your condition.

DELETE FROM can be used in conjunction with a wide variety of other SQL statements.

If you don't include your WHERE statement, DELETE will delete all the information in your table, leaving you with a blank table.


This command results in all the data in table Customers being deleted. If you don't want to do this accidentally, make sure you stick the WHERE in!

A Different Kind of Delete: Truncate

If you want to eliminate all the data in a table and use it as a template, you can also do this with the command TRUNCATE. TRUNCATE clears all rows without deleting data. Its syntax is:


Using this command, you can copy SQL database tables and clear the data quickly for a new years' use; or you can clear all your tables and use a complete database as a template for other people.