Resetting Autonumber Field In Code

[jaydwest]

I know. I Know. But is it possible to reset an Autonumber Field in Code. Here's what I have tried.

1) Delete all records in an existing Table.

Now I would like to change the attributes of the IDField, so

The current Field Attributes are dbAutoIncrField. I want to reset it to dbFixedField to clear the AutoNumber Attribute

fldIDX.Attributes = dbFixedField

But it doesn't like this. Error is Invalid Operation.

I ASSUME THIS MEANS I CANNOT CHANGE THE ATTRIBUTE. OH WELL.

If anyone has any insight into how to Reset an Autonumber Field without repairing and compacting the database, I woull really enjoy seeing how you did it.

 

[ghudson]

Search around for your question has been asked, discussed, argued and answered before. I even posted the solution in a thread somewhere on this forum. Search...Search...Search...

 

[jaydwest]

Thanks,

I did and could not anything like what I'm trying to do.

I reviewed the suggested ways to reset an Autonumber field and already knew about them. In fact I already have a routine that resets an Autonumber but it is not very pretty.

What I was looking for was a function that can reset an Autonumber in a table with no data by resetting field attributes. If you can refer me to an article that discusses this or if you know it is not possible, I would appreciate the help.

Thanks,

 

[ghudson]

How hard did you look [search]?!?!

Reset AutoNumber

 

[jaydwest]

Thanks,

I did see your posting. However you show how to create a field as an autonumber.

In an existing tabledef with an an existing AutoNumber field, can you reset the AutoNumber, without deleting the Field and creating it again.

My experience has been that when you delete a field that is used in queries, MS Access drops the Joins in the Queries. So I specifically do not want to delete the field and recreate it.

The question is can you reset an existing AutoNumber without in place in a tabledef?

Thanks

 

[Jon K]

In an existing tabledef with an an existing AutoNumber field, can you reset the AutoNumber, .........

I don't think you can reset the AutoNumber. Once a number is generated for a record, it can't be changed.
.

 

[ghudson]

can you reset the AutoNumber, without deleting the Field and creating it again.

No, you can not.

 

[dpleslie]

I figured out a neat trick. I tried using ALTER TABLE <table> ALTER COLUMN <column> approach, but I couldn't do it because the table was linked, and Access wouldn't allow me to alter the properties of a linked table. However, I happened to discover that if I try to insert a new record in code and force the current max value of the counter value, the record will not insert because it is a duplicate value, but it will update the counter so that the next time a record is inserted, the counter will be correct.

For example:

Currentdb.Execute "INSERT INTO tblCustomers (lngCustomerID) VALUES (" & DMax("[lngCustomerID]","tblCustomers") & ");"

This insert fails because there is already a record with the value DMax("[lngCustomerID]","tblCustomers"), but Access updates the counter value so that the next record will get the next integer as its value.

 

[plog]

I use this code:

CurrentDb.Execute "ALTER TABLE YourTableNameHere ALTER COLUMN FieldNameHere COUNTER(1,1)"

Change YourTableNameHere to your table's name, and FieldNameHere to the field's name.

 

[mwiley101]

I use this code:

CurrentDb.Execute "ALTER TABLE YourTableNameHere ALTER COLUMN FieldNameHere COUNTER(1,1)"

Change YourTableNameHere to your table's name, and FieldNameHere to the field's name.

Outstanding - this is great!

 

[smig]

I figured out a neat trick. I tried using ALTER TABLE <table> ALTER COLUMN <column> approach, but I couldn't do it because the table was linked, and Access wouldn't allow me to alter the properties of a linked table. However, I happened to discover that if I try to insert a new record in code and force the current max value of the counter value, the record will not insert because it is a duplicate value, but it will update the counter so that the next time a record is inserted, the counter will be correct.

For example:

Currentdb.Execute "INSERT INTO tblCustomers (lngCustomerID) VALUES (" & DMax("[lngCustomerID]","tblCustomers") & ");"

This insert fails because there is already a record with the value DMax("[lngCustomerID]","tblCustomers"), but Access updates the counter value so that the next record will get the next integer as its value.

This will work, but you need to be carefull
You can even delete the first records of the table and restart the counter to 1, but it's risky as no more records will be inserted once you get to an existing value