Reset autonumber

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan - 01 Dec 2005 23:41 GMT

I'm using office 2003 and have tried to reset the autonumber in a table by
appending a new start number into the table as directed in help, but it is
not working. Specifically, I have created a new table with a field named the
same as the autonumber field in the table I wish to affect. Then I have
executed an append query to place the value in the new table to the table I
wish to affect. This does not change the value for the autonumber field in
the next new record. (Null values are permitted in all of the fields in the
table. What am I doing wrong?

Reply to this Message

Allen Browne - 02 Dec 2005 02:14 GMT

After you append the record, what do you get in your new row?

Did you compact the database after the import? That would reset the
AutoNumber back again.

In Access 2000 and later, here's another way to alter MyID so it starts from
1000:

Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyID COUNTER (1000,1);"
CurrentProject.Connection.Execute strSql

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I'm using office 2003 and have tried to reset the autonumber in a table by
> appending a new start number into the table as directed in help, but it is
[quoted text clipped - 8 lines]
> the
> table. What am I doing wrong?

Reply to this Message

Dan - 02 Dec 2005 16:16 GMT

Thanks Allen, I realize that the autonumber can be reset by compacting the
data base, but I wanted to do it through an event procedure. Your program
worked well. Dan.

> After you append the record, what do you get in your new row?
>
[quoted text clipped - 20 lines]
> > the
> > table. What am I doing wrong?

Reply to this Message