AutoNumber Data Types 1

[mguwc]

Hi All -

I am at the last stages of this database. In my Child Data table, I have the primary key (which links practically everything) as a AutoNumber - random data type. Is there any way to change this or to change the number length. As it is now, the number can appear as a positive or negative and is approx 8 digits long.

Any suggestions?

 

[willir]

mguwc

I guess the first thing is do you have a lot of data entered already? Does it matter to you that the numbers are random and +/-'ve? Lastly, are expecting numerous users to use your database on the network?

Please rview MichaelRed's FAQ on autonumber.

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=184

And please understand that the autonumber is purely a way of uniquely identifying a record. I often hide my autonumber from the user - it really is not important to them.

...Moving on

It is possible to use autonumber as a sequential serial number. This works fine for smaller database, stand alone databases and on tables that are rarely changed.

The sequential autonumber does NOT work well for tables that have a lot of activity on the network - for example, OrderID and OrdeDetailID for an order entry system shared by 20 users. Why? Access assigns the autonumber as soon as you start entering data for a new record. Want to try corrupting your database? Say you have an Order Entry system as above where you use a sequential autonumber for the OrderID. Start entering an order. Note the OrderID number is assigned as soon as you start entering the order - don't update the record just yet. Now go to another computer - start entering a new order. Chances are high with a sequential autonumber, both orders will be assigned the same OrderID. With a randomly assigned OrderID, the chances are very, very low that both records will be assigned the same OrderID.

Still want to use a sequential number? This gets tough with a lot of records, and I would only consider moving forward if there were very few records.

You can create a new field and make it an sequential autonumber in the. Don't make it the primary key. Upon committing the change, if successful, Access will generate sequential numbers for all records in the table. Then modify your reports and forms to accommodate the new field. Do you have any relations that use the primary key as a foreign key in another table. Hmmm. You have to assign these the same sequential number as a FK by a) creating a new field, and b) assigning the new number perhaps by using a query that capitolizes on the old randomly generated autonumber. Run integrity checks to ensure you have not missed anything. Create your relations and enforce referential integrity between the affected tables.

Now you can make the new field the primary number. Then delete the old autonumber in all affected tables, and update your reports and forms.

Personally, I would just ignore it, or hide it. The fact that the number is negative or positive, and not sequential is basically "cosmetic". the main purpose of an autonumber is to uniquely identify the record.

Richard