force the record number as the key field number

[Guest]

In Access 2000 format is there any way to make Access automatically enter the
sequential record number in a key field? Using the autonumber function
leaves numbering gaps if a record(s) is deleted.

 

[Arvin Meyer [MVP]]

In Access 2000 format is there any way to make Access automatically enter the
sequential record number in a key field? Using the autonumber function
leaves numbering gaps if a record(s) is deleted.

Most numbering systems will leave a gap if a record is deleted. In a
relational database, you cannot change key numbers without changing all the
related foreign keys as well. This is an excesize in futility. A sequential
number is meaningless anyway. What happens in a paper system if a
prenumbered form is destroyed? They key exists for the sole purpose of
relating data. It needs to have no other meaning. If you need another
sequential system, by all means, create one. Just be emotionally prepared
for the inevitable gaps as well.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[John Vinson]

In Access 2000 format is there any way to make Access automatically enter the
sequential record number in a key field? Using the autonumber function
leaves numbering gaps if a record(s) is deleted.

Ok, hypothetical case here. You implement a Custom Counter (see
http://groups.google.com, use Advanced Search to search the
microsoft.public.access.* groups with this as a search term).

You enter 31225 records over time.

Some busybody points out that record #3 is in error and should never
have been entered. You delete it.

Now do you want to renumber 31222 records, in this table? In every
table to which this table is related? On every copy of every report
you've ever printed out? In every bit of human grey-matter where a
record number is stored? Off of every Post-It note where a record
number was written down?

Record numbers are NOT suitable for keys.

John W. Vinson[MVP]

 

[Guest]

I understand your points. Good info. Can it be done if the field is not a
key field?