Table redesign help.. retain autonumber?

[Fred Boer]

Hello:

I am considering a redesign of my tables. Currently I use an Autonumber
field called Book_ID, to identify records in my library table (Tbl_Library).
Now, this number has no meaning to the users of the application, so the
possiblity of gaps isn't a problem. However, it *is* used to create barcodes
which are put in the books for use in the library circulation system.

Everything works fine. However, I am a little concerned about the use of an
Autonumber field, since the numbers *are* visible to users (on the barcode
labels), and, more importantly, the possiblity that exists that the numbers
might get very long, or even go negative. A long number might not fit on the
labels, for example.

So, I've created a version of the application in which I don't use
Autonumber for Book_ID, but, instead, replace the Autonumber field with a
simple number field, and populate the field using a DMax expression as the
default value for the Book_ID field. (Kudos to Roger Carlson for his helpful
example at
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
!)

My question is: is there any reason to keep the old Autonumber Book_ID
field? Or is it really superfluous given the redesign?

Thanks!

Fred Boer

 

[Van T. Dinh]

You don't need to use the AutoNumber Field in this case. Make sure you set
the custom-number-seq. Book_ID as the PK in the new database.

If you want to import Records from the old database to the new database and
you have related Records, e.g. Loans Records, that use the AutoNumber Field
for the relationship, you will need to take care to relate the Records
correctly in the new database.

 

[Fred Boer]

Thanks, Van!

Fred

You don't need to use the AutoNumber Field in this case. Make sure you set
the custom-number-seq. Book_ID as the PK in the new database.

If you want to import Records from the old database to the new database and
you have related Records, e.g. Loans Records, that use the AutoNumber Field
for the relationship, you will need to take care to relate the Records
correctly in the new database.

--
HTH
Van T. Dinh
MVP (Access)





http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

 

[Jeff Boyce]

Fred

You probably already considered this, but if your Autonumber (PK) field is
used as a foreign key in any other tables (one-one, one-many), you'll need
to keep the value. Or you could decide to dump it, after updating all
"child" tables with the new ID.

 

[Fred Boer]

Hello, Jeff!

Yes, I have recreated all of the relationships to refer to the new BookID
number. I couldn't *think* of a good reason to keep the old Autonumber
field, but over the years I've learned to ask first rather than regret it
later.. <g>

Thanks a lot!

Fred