Auto-incrementing field

[dmctiernan04]

Hi All,
I'll give a little background on what I'm looking to achieve. I use a 3D design package called Solidworks. I created a VBA macro that runs within Solidworks which allows me to edit custom metadata for the parts that I design, such as project number, customer, part description... that sort of thing. I am now implementing a part numbering system so I need a way to track what numbers are in use, etc. I would like to create this part number database use Access. I was recommended not to use the Autonumber for incrementing the numbering in order to get the next available number as this will be prone to changes if a record is deleted, giving a broken number series.

From this I have two questions:
1) Is there a way that I can auto-increment another field?
2) If the answer is "yes" to question 1, Is there a way to get it to auto-increment the field without opening access, as I will be transferring the metadata to the database from my existing macro (using ADO). There will also be 6 other engineers using this database.

Thanks in advance,
Declan.

p.s. - I hope my question is clear enough, if not let me know and I will try to explain better.

 

[CraigDolphin]

Is there any particular reason why the number series has to be unbroken? I would just use the autonumber myself...

Consider...if you have parts
1,2,3,4,5,6,7

Then you make part #3 obsolete, delete it, whatever.
If this were an autonumber field the next part number assigned would be 8
1,2,4,5,6,7,8
To maintain an unbroken number series, you'd need to rename all the part numbers after 3 to to fill in the gap.
Which would give you
1,2,4->3,5->4,6->5,7->6 and the next would be 7.

Then, you'd need to update all the information in your other applications that use this numbering system to match these edits.

That's a lot of work to keep an arbitrary number system 'unbroken'. Is that what you really want?

 

[dmctiernan04]

Hi CraigDolphin,
thanks for the reply. the main reason for not using an autonumber is that if the database is copacted then the autonumber is reset (so I have been told). I haven't done much with Access so I don't know if this is true or not. Also if someone started to create a record and then cancelled, the autonumber increments again even though the previous number wasn't actually used.

Any other suggestions?

Declan.

 

[neileg]

the main reason for not using an autonumber is that if the database is copacted then the autonumber is reset (so I have been told). I haven't done much with Access so I don't know if this is true or not.

It's only true if you delete all the data and compact an empty database!

Also if someone started to create a record and then cancelled, the autonumber increments again even though the previous number wasn't actually used.

Partly true. If there's only a single user creating records and the record is abandoned, then the autonumber won't increment. If a user starts to create a record and then another user starts another new record, this second record will increment the autonumber and if the first record is abandoned this will create a gap.

Any other suggestions?

As Craig said, deleting a record will create a gap unless you take positive action to renumber.

 

[CraigDolphin]

....what Neil said. Again, why would a gap in the sequence be such a bad thing?

 

[Simon_MT]

to me the real value of autonumbering is sequencing on the last records. Most PKs I use involve a recognisable piece to information rather than a number. I use abbreviated codes as OOC and OOB are Oil on Canvas and Oil on Board. I know from the Table anything starting with "O" means Oil or "A" Acryllic. This table does have Autonumbering field but is not used anywhere else. I would keep the AutoNumbering but if you want to do a sort on your Part numbers, sensible coding structures can be useful as well.


Simon

 

[Simon_MT]

to me the real value of autonumbering is sequencing on the last records. Most PKs I use involve a recognisable piece to information rather than a number. I use abbreviated codes as OOC and OOB are Oil on Canvas and Oil on Board. I know from the Table anything starting with "O" means Oil or "A" Acryllic. This table does have Autonumbering field but is not used anywhere else. I would keep the AutoNumbering but if you want to do a sort on your Part numbers, sensible coding structures can be useful as well.


Simon

 

[aikea]

You can have a separated field in your database like "Serial" for example to record the a human readable serial code for your data.

The ID and the PK are only for computers.