Menu
I have a table in an Access database with an autonumber field.
When I delete all the records from the table, the autonumber remembers the last number.
Does Access have something similar to SQL Server's TRUNCATE TABLE MyTbl?
If not, how to start with 1 after I delete the table's records?
593
To remove all data from an existing table, use the SQL TRUNCATE TABLE order. You can also use the DROP TABLE command to delete an entire table. But Truncate will remove the entire table structure from the database, and you will need to recreate the table if you want to store any data.
There are five types of query in Access. They are: Select queries • Action queries • Parameter queries • Crosstab queries • SQL queries.
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.
TRUNCATE is faster than DELETE , as it doesn't scan every record before removing it. TRUNCATE TABLE locks the whole table to remove data from a table; thus, this command also uses less transaction space than DELETE .
Access SQL does not have anything like TRUNCATE TABLE.
You can use an ADO connection to execute a DDL statement which resets the autonumber field's "seed" value. So you could do this with VBA code, and not have to use compact & repair to reset the autonumber.
This example code first deletes all rows from my tblFoo table, and then resets the seed value for the id autonumber field.
Dim strSql As String
strSql = "DELETE FROM tblFoo;"
CurrentProject.Connection.Execute strSql
strSql = "ALTER TABLE tblFoo ALTER COLUMN id COUNTER (1, 1);"
CurrentProject.Connection.Execute strSql
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
