Re-Settin a counter

[159159]

Hi .... in VBA how would u re-set a counter having deleted a number of tables .... ???? so basically u i want the counter to start back from 1 again !!

 

[BattleDroid42]

Create a new identical table with an AutoNumber field and append the records to it.

 

[159159]

ok i dont want to be creatin a new table, cause the DB i am designing is actually for those who are not computer lit ... therefore was i delete all the records from the tables, i will still use the same table, but the counter has not been re-setted, therefore i need to know how to re-sit the counter, but simply pressin one button ... u get me !! ???

 

[BattleDroid42]

You cannot reset an AutoNumber field with the press of a button. However, you can use DAO to program a process that will automate the process I mentioned:

1) Create a new temporary table with an AutoNumber field.
2) Dump the old records to the temp table.
3) Delete the old table.
4) Rename the temp table with the same name as the old table.

Here's some code to get you started:

Dim dbs As DAO.Database
Dim fld As DAO.Field
Dim tdf As DAO.TableDef

Set tdf = dbs.CreateTableDef("TEMP&quot
Set fld = tdf.CreateField("ID", dbLong)
fld.Attributes = fld.Attributes + dbAutoIncrField
tdf.Fields.Append fld
Set fld = tdf.CreateField("Field1", dbText, 15)
tdf.Fields.Append fld
Set fld = tdf.CreateField("Field2", dbText, 15)
tdf.Fields.Append fld
dbs.TableDefs.Append tdf

 

[Culby]

Here is how I did this without writing code...
1) Create a delete query to delete all records in "Table A".
2) Create an append query to add in the new data to "Table A".
3) Create a Copy of "Table A" with no records. I'll call this "Table B". Both tables should have the autonumber field as well as all the other fields in your table.
4) Create a macro to run the delete query, then have the macro copy the structure only of "Table B" to "Table A" using the COPY OBJECT action (this resets the autonumber field at 1), then have the macro run the append query to add the new data to "Table A" which will have the autonumbers starting at 1.