How to reset the autonumeric field values to start over again ?

Autonumber dataype is specifically used to make records unique.

If users aren not happy that when you 1 , 2, 3 (delete 4) and the next one is 5.... Thats the way it is.

You cannot get 4 again.

Typically developers hide this ID field but allow it to work unless it is useful for users as a reference.

If you want to take your existing data and start again ... then you can copy table and structure only.... copy and paste from orig table to the new one WITHOUT ID autonumber field

and numbering starts again at 1... BUT IF THERE IS ANY OTHER RECORD IN ANY OTHER TABLE ... that stall has ID numbers from beofre.... they will be broken ... as the number they had (5) will now be something else

R

The Idea is just to mantain the structure so, the data actually must be deleted, that´s why the new user needs to start with 1 on each autonumeric field.

Considering that there are alot of tables, is there any way to "reset" them in only one step? or using VB macro/module ?

Thanks again

i dont follow the new user having to start with 1.. when 1 has goner.. thats it committed to the db..;

Maybe an example.. screenshtos etc.. would be useful in case i am misintrepreting your use especially as we are usign the term delete

R

Hi dimensionav,

If you want to do it manually once, you can delete the column - save the table & then add the field again

Good Luck!

Gary

OK LSM.

But I feel there are varying degrees of answer to this question and at beginner level .. I would say Autonumber is a great start before you start generating your own unique keys and understanding the concepts of VBA and how to do this effectively.

I fully and completely support your viewpoint.

Creating commercial databases I would not rely on a single autonumber field for uniquness when 'I' am creating systems.

Thanks for your input and clarification it is honestly appreciated.

R

LSMConsulting:

Which would be a good practice of uniqueness of data instead of Autonumber fields?

BTW: your solution worked, that´s exactly what I was looking for. Thank you very much.

"with at least one other index defined as Unique to guarantee that a particular "row" of data is unique. "

In many cases, that's just not possible.

mx

LSM:

"The Autonumber has absolutely no data value." Agreed.

"At best, an Autonumber field should be used as a surrogate key, with at least one other index defined as Unique to guarantee that a particular "row" of data is unique. Otherwise, I could have 2 or more identical records, each "uniquely" defined merely by having a different Autonumber value."

<soapbox mode>
Well I could equally have two or more records that are identical in all respects except for a differing autonumber value and a differing value in the other index.  For the vast majority of cases an autonumber field is perfectly valid as a unique index, but the main thing about them (and any other primary key) is that users need never see the values. They are only indended to be used to link related tables together via primary key / foreigh key relationships.

Any time someone asks how to reset an autonumber the reg flags start to wave: they are assigning a meaning to the field, in which case there should be really another field in the table that will have that meaningful data. Leave the autonumber to do it's (hidden behind the scenes) job: making records unique.
</soapbox mode>

dimensionav:  So you should not care about the autonumber values. They only have to be (and will be) unique. That's one reason why you can choose "Random" as the increment mode.

roycasella:
"Creating commercial databases I would not rely on a single autonumber field for uniquness when 'I' am creating systems."

Why would you thing that your method of creating an unique index value is necessarily better that an autonumber field which was designed to just that one job: provid a unique value?

Everyone my find this recent thread interesting:

https://www.experts-exchange.com/Microsoft/Development/MS_Access/Q-23890697-What-is-a-primary-key.html#a22933519

mx

I think this pretty much sums it up for Access ... per Luke Chung, FMS President

http://www.fmsinc.com/free/newtips/primarykey.asp

Note in particular the part under:

"Only Use One Numeric Field as the Primary Key"

mx

"Only Use One Numeric Field as the Primary Key"

Which is exactly what an Autonumber field is.

Exactly.

mx

Thanks all of you Guys

Yeah I agree..

As per my original coment.. varying degrese of answer

and  I supported the use of autonumber.. but commercially typically an autonumber field will not be the reference a customer wants to supply to their customers or their suppliers.

hence autonumber is used... hidden in background.. and other field is used for the customers ref... a more meaning ref that they wish to display to others

There are many view on this..

MS put it in there.. everyone uses it for uniqueness and connecting tbales togehter.. I DO also. BUt I think we can all agree it is not alwasy the best reference for a customer

hence LSM making the comment of a differnet field which I support for qutoations, Purchase order requests etc...

Thanks for all your comments guys.

R

<Leave the autonumber to do it's (hidden behind the scenes) job: making records unique.>

I disagree. The job of an AutoNumber is not to insure uniqueness of data, but rather to provide a simple pointer to a unique subset of data (i.e. a Record). If your data is stored such that the ONLY way to insure uniqueness is through inclusion of an AutoNumber field, then I'd suggest that your data isn't being stored properly.

<Why would you thing that your method of creating an unique index value is necessarily better that an autonumber field which was designed to just that one job: provid a unique value?>

It's better because it allows the database engine to guarantee actual unique data. The mere inclusion of an arbitrary value - i.e. a randomly generated AutoNumber field - does NOT guarantee that the specific row of data to which that AN is related is unique. The ONLY way to to this is to form a key that uniquely identifies a particular row of data.

Think of an Address table for US cities, and consider a city named Greenwood. A State can have more than one City named Greenwood. Each of those cities could have a Street named Main Street and each of those Streets could have an address of 200. If I stopped there, and decided to make my table with the Primary Key of the AutoNumber field and NO OTHER unique indexes, depending entirely on the AN to guarantee each row were unique, then when I did a search for "State='SC' AND City='Greenwood' AND Street='Main' AND HouseNumber='200'", I might return 3 or 4 records. Which of those records should I use? If I'm depending SOLELY on the AN field to make this data unique, then my enduser is presented with those 3 or 4 records, and they must then make a further determination as to which row of data to work with. You could expose the AN to the user at this point, but would it really help? I doubt it.

A better choice would be to include those fields (plus at least one other - the US Zipcode)  in a unique index consisting of State+City+Street+HouseNumber+Zip. Using those fields as the Unique key you could be reasonably assured of a unique record ... this would be your "natural key". Of course, it'd be foolish to add 5 fields to every table which needed to be related to the Address table so instead you add an AN field and use that as the surrogate key. This surrogate key has absolutely nothing to do with guaranteeing that each address is unique - your State+City+Street+HouseNumber+Zip key has that responsibility - but you can get a handle on a specific record using the PK field, and you can use the PK field to relate that specific row to other tables. This is the function of a surrogate key.

<In many cases, that's just not possible.>

But why not? Unless we're talking about trivial data which is of no importance to the overall "data quality" (i.e. just a simple listing, say of phone numbers or a mailing list), then each row of data in a database table should be unique, and that uniqueness should be derived from a combination of one or more fields from the actual data, not from an arbitrary Autonumber value. If you cannot uniquely define a row of data, then what happens when your user opens a filtered report/form, expecting to see only one row, and is returned instead 2+ - with the only difference being the AN field (which should be hidden from view)? How do they know which row they're working with, if those multiple rows are identical except for the AN field?

<Note in particular the part under:

"Only Use One Numeric Field as the Primary Key">

I agree entirely. However, Luke is referring to TAGGING a record - which is what a surrogate key (i.e. Autonumber field) does. I think perhaps you're missing the point. I include an AutoNumber AN field in EVERY table I build, and I set is as my Primary Key. But I also always include a separate Unique Index that is made up of 1 or more Fields in that table. That separate index is what uniquely defines my data, not the AN field. I use the AN field when relating my tables, or when I need to get a handle on the data (i.e. when opening a recordset, etc), but it's never used for any other purpose. I don't allow users to search on it, it's not exposed on Forms/Reports etc etc ... simply because it has no other function, and has no value in regards to data. It does not uniquely identify anything, but it does point to a unique subset of data.

So: I fully agree with using an AutoNumber field as the Primary Key, but I do not agree with using an AutoNumber field to guarantee that a specific row of data is unique. As I've said before, if the ONLY reason your row is unique is due to the AN field, then you've almost certainly got some data issues. I could see where some systems would store virtually identical data - a logging application, or something of that nature - but even with those, you'd want some form of timestamp/sort/order field, which would then be used (in combination with other fields) to uniquely identify your data.

LSMConsulting:

What would you use as a unique data in case on you need to register users for online ecommerce?
email would be a good option? or a combination of email and some number like "Client No.", which could be generated by some algorithm ?

Thanks

<Why would you thing that your method of creating an unique index value is necessarily better that an autonumber field which was designed to just that one job: provid a unique value?>It's better because it allows the database engine to guarantee actual unique data. The mere inclusion of an arbitrary value - i.e. a randomly generated AutoNumber field - does NOT guarantee that the specific row of data to which that AN is related is unique. The ONLY way to to this is to form a key that uniquely identifies a particular row of data.

<maximum strength soapbox mode>

The purpose of an Autonumber (or Idenitiy in SQL Server Land) field is not to make a row of data unique. It is to identify a specific row of data. It has nothing at all to do with the data contained in the row, you can indeed have 8 million rows of data that have exactly the same values except for the value of the Autonumber field. The presence of the otherwise identical data is not the fault of the Autonumber field. It is indeed the fault of the database designer.

The Autonumber allows one to refer to a specific row of data and nothing else. Seems like you are talking about something quite different: the creation of compound indexes to guard against the storage of identical data in tables. This is a desirable goal, but your method is not the only way to do this. For the purposes of linking related tables together the autonumber field is simple, efficient and reliable. For the purposes of guaranteeing the uniqueness of the data that is being stored, the Autonumber has nothing to offer, and it was never supposed to do so. It is always up to the designer of the database to devise a way to do that.

Nor is the Autonumber indended to substitute for a 'customer reference number' or any such human usable information. It should always be invisible to users because it has no meaning.

</maximum strength soapbox mode> :-)

LambertHeenan:

I don't agree with this statement:

<For the vast majority of cases an autonumber field is perfectly valid as a unique index,>

However, I think we're both beating the same drum. I maintain that the AN field is a valid surrogate index, but not a valid Unique Index. It works perfectly fine as a Primary Key, as I've stated above, but it has no validity in terms of data uniqueness. My point is that merely including an arbitrary number to insure data uniqueness would indicate problems with the data storage structure, as you've said in your response, and is the fault of the designer, not the database (again, as you've said).

<you can indeed have 8 million rows of data that have exactly the same values except for the value of the Autonumber field. The presence of the otherwise identical data is not the fault of the Autonumber field. It is indeed the fault of the database designer. >

Exactly - that's what I was saying, perhaps poorly. The simple inclusion of an Autonumber field does not automatically make those 8 million rows unique from a data standpoint.

<Nor is the Autonumber indended to substitute for a 'customer reference number' or any such human usable information. It should always be invisible to users because it has no meaning.>

Agreed. Outside of a developer's utility, I've never seen the need to expose AutoNumber fields to the user.

We don't have soapboxes down South, but I could stand on a whiskey keg I guess ... :}

<What would you use as a unique data in case on you need to register users for online ecommerce?
email would be a good option? or a combination of email and some number like "Client No.", which could be generated by some algorithm ?>

AFAIK, an email address is unique, period. You could use this as your Unique Index. Still, I'd use a surrogate key (i.e. AutoNumber field) as my Primary Key for table linking and such, since a user could certainly change their Email address.

"If I stopped there, and decided to make my table with the Primary Key of the AutoNumber field and NO OTHER unique indexes, depending entirely on the AN to guarantee each row were unique, then when I did a search for "State='SC' AND City='Greenwood' AND Street='Main' AND HouseNumber='200'", I might return 3 or 4 records."

<respectfully> ...
Not really the case.  These 'uniqueness' checks can all be done via the UI, saving the need for additional multi field indexes to attempt to guarantee uniqueness of the data across all records.

"but I do not agree with using an AutoNumber field to guarantee that a specific row of data is unique."

I have *never* said that in any statement that I have made regarding the AN - nor have I implied that the AN would guarantee uniqueness of data across records.  In fact, on the contrary.  Previous discussion have implied that a PK should be made up of N number of fields via an Index - which can be guaranteed to be unique.

mx