is there TRUNCATE in Access?

Question

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?

Chenault

You don't mean "after I delete the table" -- you mean "after I delete all the records in the table". If you deleted the actual table, you would definitely get a new autonumber value. – Oldline

Answer 1

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

Lemieux

Answer 2

There's no truncate in MS Access as far as I know but you can resolve the auto-number issue remembering the last number after delete. There's a way in MS Access to start with 1 after deleting records with auto-number field. If you're using ms-access-2007 you can find the Compact and Repair Database feature by selecting Manage, located under the Office Button. You can also have your database compact and repair itself every time it is closed, by activating the Compact on Close option. If you would like to set this option, you can find it in the Access Options for the Current Database.

Hopehopeful

How is this related to truncate functionality? – Conductivity

Answer 3

Or without compact and repair:

Empty your table:

DELETE FROM MyTable

Alter autoincrementfield (like 'ID') to Integer:

ALTER TABLE MyTable ALTER Column ID INT;

Alter the field back to AUTOINCREMENT:

ALTER TABLE MyTable ALTER Column ID AUTOINCREMENT;

Milkandwater

Answer 4

In MS_Access Database, "Truncate is not supported". So we can delete the all records first by "Delete" query.

After delete the records, we can alter the "ID" column starts from 1 by using "Alter" query.

Query:

  "Delete * from (Your Table Name); 
  Alter Table (Your Table Name) Alter Column (Your Column Id) Counter(1,1);"

Using this two queries in single execution we can delete all records and reset the column id starts from 1.

Stoa

There is no additional information whatsoever in this answer compared to the answer by HansUp. What was the point of posting this answer? – Bissextile

I am not seeing the answer of HansUp. Because there is no tick mark on any answer. So only add my answer in my point of few. – Stoa

Answer 5

took 1 second to find the solution at:

http://www.webmasterworld.com/databases_sql_mysql/3227574.htm

The Compact and Repair process will reset all empty table auto-increment counters to zero. So, delete all the records then:

Tools -> Database Utilities -> Compact and Repair Database

Compartmentalize

As of office 365, mine doesn't anymore. :( – Manion

Answer 6

There is another way that doesn't require altering the table or compacting and repairing. It will work better for you if you have a distributed database. As of 2000, Access will accept an insert value into an autonumber field and start incrementing there. Do it in a query or in code like this.

CurrentProject.Connection.Execute "DELETE FROM Mytbl"
CurrentProject.Connection.Execute "INSERT INTO Mytbl(ID) VALUES(0)"
CurrentProject.Connection.Execute "DELETE FROM Mytbl"

Note: Put adExecuteNoRecords after the execute command for better performance. e.g. CurrentProject.Connection.Execute sql, , adExecuteNoRecords

Manion

Answer 7

Delete all the records and then compact the database.

Corbitt

Answer 8

I also the same problem before, what I did was I go back to the table in modify mode. Then I deleted the field name with autonumber and I inserted again with the same name. The result of the autonumber when I run in view mode was back to the number 1 the first Entry.

Chorea

Worked for me, I guess the simplest and shortest route is to delete and recreate the autonumber field. – Conformal

Answer 9

Or without Compact and Repair when using VBA: Say your table is 'MyTable'. Make a copy of that table WITHOUT data and save it for example as 'MyTable_Template'. When you want to Truncate the table just 'DROP' the table en copy 'MyTable_Template' to 'MyTable'. Now the autoincrementfield of your 'new' table 'MyTable' will start at 1 again.

Milkandwater