Truncate table in Access 2010

Richard Comito
Richard Comito used Ask the Experts™
on
I need to truncate a table in Access 2010.  is this possible.  I am going to insert a new list of data and I want my ID's to start at 1 and not the last id that was in the table.

Thanks,

Gabicus
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
First method:

if you delete ALL the records in the table, then do a Compact and Repair, the AN will reset to zero.  However, you must delete ALL the records.  

mx
Database Architect / Application Developer
Top Expert 2007
Commented:
Another method.

1) Backup your MDB
2) Delete the Auto Number Field from your table
3) From the Access menu ... >>Tools>>Database Utilities>>Compact and Repair
4) Open the table in design view
5) Add a new Auto Number -  with the same name as the original Auto Number - and save the table design.
Done.

See this link for complete details:

http://support.microsoft.com/kb/812718

I have some vba code methods if you are interested also ...?

mx
Most Valuable Expert 2014

Commented:
http://allenbrowne.com/func-adox.html
Has some snazzy code for doing this using ADOX.

If it's a one-off, then I do it the way mx has suggested.

A third way, if the table does not participate in any relationships is copy-and-paste the table with the option for Structure Only.
Function DeleteAllAndResetAutoNum(strTable As String) As Boolean
    'Purpose:   Delete all records from the table, and reset the AutoNumber using ADOX.
    '           Also illustrates how to find the AutoNumber field.
    'Argument:  Name of the table to reset.
    'Return:    True if sucessful.
    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim col As ADOX.Column
    Dim strSql As String

    'Delete all records.
    strSql = "DELETE FROM [" & strTable & "];"
    CurrentProject.Connection.Execute strSql

    'Find and reset the AutoNum field.
    cat.ActiveConnection = CurrentProject.Connection
    Set tbl = cat.Tables(strTable)
    For Each col In tbl.Columns
        If col.Properties("Autoincrement") Then
            col.Properties("Seed") = 1
            DeleteAllAndResetAutoNum = True
        End If
    Next
End Function

Open in new window

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
OK ... here is the VBA code method:

** VBA Code method - Example**
Place this code in a standard module. Do not name the module the same as the Function below.

Public Function mResetAutoNumber(lStartVal As Long, lIncrement As Long) As String
   'Example: Set the Starting Value and Increment for a table named Table1ANTest and an AutoNumber field called AutoNum
    Dim sSQL As String
    sSQL = "ALTER TABLE [Table1ANTest] ALTER COLUMN [AutoNum] COUNTER (" & lStartVal & ", " & lIncrement & ");"
    CurrentDb.Execute sSQL
    mResetAutoNumber = "Auto Number has been re-numbered"
End Function

The first number (lStartVal) is the starting value, and the second (lIncrement) is the increment.
The example above will start the Auto Number at 1000 and increment by 50.

To run the code one time, open the VBA Immediate Window, and type
this line (including the question mark, followed by Enter:

?Function mResetAutoNumber(<YourStartValueNumber>, <YourIncrementNumber>)
Example:
?Function mResetAutoNumber(500,3)

You can run this against an empty existing table, or to modify the *next higher* Auto Number value and increment.
It will *not* change any existing auto number values.

This works for A2K and later.
Richard ComitoDirector of IT

Author

Commented:
DatabaseMX,

Thanks for this.  it was all I needed.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial