Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Unanswered: Database Question:

    Database Question:

    Info:
    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

    Problem:
    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

    Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    WOAH WOAH WOAH!!

    DO NOT CHANGE THE KEY FIELD. EVER.

    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.

  3. #3
    Join Date
    Apr 2004
    Posts
    4
    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

    Would there be an easier way?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Rowcount is much easier left to a query.

    If you just need that number, you could drop a textbox onto a form with =DCount("yourField", "yourTable")

    There's other more crazy ways to do it, but that's the simplest.

  5. #5
    Join Date
    Nov 2003
    Posts
    267
    Search MS knowledge base for "QrySmp00.exe" it has a sample Access query that will show you how to get a sequential number to each row for display purposes only

    S-

  6. #6
    Join Date
    Apr 2004
    Posts
    4
    Thanks for the suggestions

    It is a neat query, but I need to modify the actual Contact_ID in the database

  7. #7
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    [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...

    Suggestion/alternative:
    (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.

    Daniel.

  8. #8
    Join Date
    Apr 2004
    Posts
    4
    Thanks,
    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

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by df801303
    ...sort the Contact_Id field nightly until relationships are added to the table
    Then what?


  10. #10
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Originally posted by df801303
    Thanks,
    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.

    D.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •