Resetting Autonumber after deleting all records

[sumdumgai]

I've searched and searched but cannot find solution to my problem. Don't even know of it is possible. If after deleting all records in a table, how can I reset the Autonumber to 1 for the 'ID' column that Access automatically creates?
I've tried "ALTER mytable COLUMN ID COUNTER (1,1);"
I've tried 'DROP'ing the ID column which Access will not allow me to do.
Compacting the database only happens after I close it which won't work for me. Any help would be much appreciated. Thanks.

 

[Trevor G]

It will reset on compacting, so if you are closing the database before you repopulate the table then it will work, you can always do a manual compact.

 

[Sketchin]

It will reset only if every record is deleted? I never knew you could do that.

 

[Trevor G]

Yes if all records are deleted from the table when compacting it resets the AutoNumber to 1. Plus if you add formatting it will add that as well. In the table and in design view you would select the AutoNumber Field and in the format property below you could do something like:

"Hello "#

Which then gives each record the word Hello and a space using the # symbol then numbers the records

 

[sumdumgai]

I did not explain properly. The database is open. I delete all the records. Now I want to reset the ID column Autonumber without exiting the database and compacting. Can you reset ID Autonumber to '1' after deleting all records and while the database is open? Thanks.

 

[boblarson]

But if you care what the number is for the autonumber, then you likely are not using it the way it should be used.

 

[Trevor G]

I did not explain properly. The database is open. I delete all the records. Now I want to reset the ID column Autonumber without exiting the database and compacting. Can you reset ID Autonumber to '1' after deleting all records and while the database is open? Thanks.

Microsoft website has the following suggestion:

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

 

[sumdumgai]

The Microsoft kb explains how to do it manually. I want to use VBA. There will be half a million records imported daily, and if I keep using the next higher Autonumber in the ID column with each import, that number could get pretty large.

 

[Trevor G]

Then it would seem best to delete the table and recreate it through VBA.

Docmd.DeleteObject acTable, <tablename>

What are the field names and datatypes? Are there many?

Sample code for creating a Table from scratch shown below, but you might need DAO as well.

Sub CreateTable()

Dim dbs As Database, tbl As TableDef, fld As Field

Set dbs = CurrentDb

Set tbl = dbs.CreateTableDef("Test")
Set fld = tbl.CreateField("test1", dbText)

tbl.Fields.Append fld
dbs.TableDefs.Append tbl
dbs.TableDefs.Refresh

End Sub

 

[boblarson]

The Microsoft kb explains how to do it manually. I want to use VBA. There will be half a million records imported daily, and if I keep using the next higher Autonumber in the ID column with each import, that number could get pretty large.

So, why have an autonumber for those anyway if you are just going to purge them daily?

 

[Trevor G]

I agree with Bob, but I am sure you have a reason. Or do you?

 

[jdraw]

If you are adding and deleting 500,000 records every day, you might want to set up a procedure to do Compact/Repair and reset the autonumber. Some one may have a batch file to do this, or compact on close. Just seems with that kind of traffic, you'll want to remove any bloat whenever you can.

 

[sumdumgai]

Thanks. I'm not exactly an Access expert so bear with me please. I don't want to delete and recreate the table because there may be relationships or queries created with this table down the road, and then wouldn't I have to restore those relationships (or maybe even queries)? If I create the table as an unindexed table, will that cause performance issues when searching and matching other fields?

 

[boblarson]

Thanks. I'm not exactly an Access expert so bear with me please. I don't want to delete and recreate the table because there may be relationships or queries created with this table down the road, and then wouldn't I have to restore those relationships (or maybe even queries)? If I create the table as an unindexed table, will that cause performance issues when searching and matching other fields?

You can index on fields within the data but no need to include an autonumber if you aren't really going to use it.

 

[sumdumgai]

Thank you. So, without sacrificing performance, I can initially create the table without creating a primary (Column ID with autonumber) index but I'll index those other fields (some with duplicate values) that may be searched or sorted. That right?

 

[boblarson]

Thank you. So, without sacrificing performance,

Indexing on a number which has no association with the data isn't really going to help in the first place.

I can initially create the table without creating a primary (Column ID with autonumber) index but I'll index those other fields (some with duplicate values) that may be searched or sorted. That right?

Yes, just set their property as Indexed (duplicates allowed).

 

[sumdumgai]

Resolved. Thanks.