Autonumber fields in MS Access

[Guest]

Hello,

Can anyone tell me how to programatically reset the autonumber count in a
table in Access? I want to run a delete query to remove all rows, then
append rows and have the autonumber field begin counting at 1. I don't want
to make a new table with a new autonumber field, because I don't know how to
do that in a macro or a module (how to make a new table, define the fields,
create a new autonumber field). I have tried the method in the help file of
appending to the autonumber field and it doesn't work. The autonumber field
just picks up where it was before appending.

Thank you for your help.

 

[Guest]

You can set the field back to counter after you delete the date

Delete statement
docmd.RunSQL "Alter table TableName ALTER COLUMN FieldName counter"
Insert Statemet

 

[Guest]

Thank you so much. I appreciate it. When I tried to run I inserted the
table and field names like this:
DoCmd.RunSQL "Alter table Table1, ALTER COLUMN test counter"

And I got the following error message:

Syntax Error in Alter Table statement.

Is it an issue with enclosing the table and field names in quotes, or was I
supposed to do something with the counter at the end?

I really appreciate your help.

 

[Guest]

Ofer,

Thank you so much. I removed the comma that I had inserted and it works
great! Are there any references to what is available in the RUNSQL function,
or is it just a matter of knowing SQL?

Thank you so much again for your help. I really appreciate it.

 

[Guest]

Your welcome, glad it worked
In the RunSQL (as given by it's name) we run Action SQL such as
Create, Insert , Delete , Update , Alter

 

[Marshall Barton]

Can anyone tell me how to programatically reset the autonumber count in a
table in Access? I want to run a delete query to remove all rows, then
append rows and have the autonumber field begin counting at 1. I don't want
to make a new table with a new autonumber field, because I don't know how to
do that in a macro or a module (how to make a new table, define the fields,
create a new autonumber field). I have tried the method in the help file of
appending to the autonumber field and it doesn't work. The autonumber field
just picks up where it was before appending.

Another way to reset an auto number after deleting records
is to Compact the database.

OTOH, you should not care what values are in an autonumber
field. If you do care about the values, you should create
them yourself instead of relying on a feature that was
intended for an entirely different purpose.

 

[RobFMS]

Here's everything you need to know about autonumbers...

Autonumber field in Table
http://www.mvps.org/access/general/gen0006.htm

Use Autonumbers properly
http://www.mvps.org/access/general/gen0025.htm

Have Autonumber field start from a value different from 1
http://www.mvps.org/access/tables/tbl0005.htm

Creating an AutoNumber field from code
http://www.mvps.org/access/tables/tbl0016.htm

HTH

--
Rob Mastrostefano

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com