I am working on a access "contact list" that connects to a local SQL server database
There are over 2912 rows of data in the database
The primary key is called Contact_Id
Contact_Id is a Auto number/identity field
When a new contact is inserted into the database, the Contact_Id gets incremented
But, when an old contact gets deleted, it causes gaps in the field Contact_Id
The only solution I see, is droping the Contact_Id field, creating a new Contact_Id field, and put an identity on it, and creating a nightly batch job that performs the previous steps
Are there any other methods to take care of the Contact_Id gaps in the database?
I would appreciate any help
The gap isn't that big of a deal. If you go around changing the primary key because of a deleted record, you are effectively negating all of the positive reasons to use a relational database.
When you change the key field, you have just broken all of the relationships. Of course, you could do a cascade update I suppose, but doesn't that seem like an awful lot of trouble just for a spiffy looking key column?
I urge you to just leave it be, SO fewer headaches that way.
I guess I could create a new UID for the table that increments as a new contact is entered and leave the new UID alone, and sort the Contact_Id field nightly
The table at this point does not have any relationships, but in the future it might
We are using the Contact_Id as a row count for the table
[QUOTE]Originally posted by df801303
Thanks for the suggestions
It is a neat query, but I need to modify the actual Contact_ID in the database [/QUOTE
If I understand you correctly, you will renumber (actually give existing contacts new ID numbers) EACH time (or nightly) a record is being deleted???
If you do not do that EACH time (but for example nighly), then sometime during the day, the last number would not give the correct number of records, then this method is of little value... So you have to make the update for each deletion, or mark records for deletion to be done in a batch before the nightly renumbering routine... But that would give you a uselessly correct count...
(I strongly agree with Teddy, DON'T do this...)
If you absolutely have to keep editing that field, I think you should create another autonumber field as the primary key, perhaps turning the Contact_ID field into a standard number field, using vba to increment it for new rows, still demanding only unique values of course. Would work until you start building relationships to other tables. Those relationships will then have to be set to the new autonumber primary key, not to the Contact_ID field. Could avoid trouble that way. But there are other concerns also, depending on the future plans for the development of the database.
Originally posted by df801303
I will create a new UID for the table that increments as a new contact is entered and leave the new UID alone, and sort the Contact_Id field nightly until relationships are added to the table
But still; it it NOT a problem that the Contact_ID is changing for existing contacts? I assume that it is not being used for reference / lookups and do not get printed on reports or other? Because printed info would quickly (nightly ;-) ) become misleading... You loose the convenience of being able to look up contacts based on the contact number, for example.