Truncating a table in a database empties the table of all records. The TRUNCATE operation actually DROPS the table and then recreates it. This is much faster than an operation that deletes all the records from the table (even if the end result is similar). MySQL has a TRUNCATE function, and you can simply run SQL as follows: TRUNCATE tblName;
In this tutorial:
Required knowledge:
1. There is no TRUNCATE in Access
To delete records from an Access table, you can manually delete them, or you can create a DELETE query.
If your Primary Key is an AutoNumber field, the auto-number sequence will not be reset but will continue as expected.
2. Reset AutoNumber
If you want to delete all records from an Access table and you do want to reset an AutoNumber field in that table, follow these steps:
- Select the Database Tools menu tab.
- Left-click the Compact and Repair Database button
If the database is small, the process is so fast that you may not see anything happen (there is no progress bar). If your table is open, the process closes it.
3. Next steps
See the VBA to truncate or delete Access database tables tutorial.
References:
- Stack Overflow, i., Xymon, M. and Ponselvan, L. (2011) Is there TRUNCATE in Access?, Stack Overflow. Available at: https://stackoverflow.com/questions/7230605/is-there-truncate-in-access/9940914#9940914 (Accessed: 19 June 2023).
- MySQL 8.0 Reference Manual . (no date) MySQL 8.0 Reference Manual :: 13.1.37 truncate table statement. Available at: https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html (Accessed: 20 June 2023).