Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2009
    Posts
    5

    Post Unanswered: How do i close the gap in autonumbers?

    I have a gap between my records-->
    1 2 3 4 5 6 7 9 (record 8 has been deleted )how do i close the gap - the key field is an autonumber


    Thanks!

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    EDIT: Double post, sorry.
    Last edited by nckdryr; 10-02-09 at 20:02.
    Me.Geek = True

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums

    Quote Originally Posted by DaveDog89
    OK i have a simple table with 2 columns one of them being an autonumber field. at thetiem i had 9 total records in it i deleted record number 8 and now have a gap between record 7 and record 9 is there a way to fix that?
    Um, don't autonumber?

    Generally I don't use autonumbers except as primary keys. I think I'm afraid to ask, but why would you want to renumber your fields after you delete a record? It sounds like you might have a deeper, more troubling problem.

    Cheers!
    Me.Geek = True

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As someone, John Vinson, I think, said, Autonumbers are unfit for human consumption! If it's something to be seen/used by the end users, it shouldn't be an Autonumber.

    And truth be told, what legitimate use do you have for such a number, which will change for a given record, as records are deleted? The relative position of a record, within the recordset, can easily be determined! A more detailed account of what you're trying to do will help us to help you.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Although an autonumber field SHOULD be in every data table (this is to prevent problems on the form and having to issue save commands along with other potential problems), the autonumber field should not be used as a "record counter" for using/identifying a specific numbering type system. If you want to do this, you should create an integer type field and update it in code since you can then manipulate missing gaps in numbers. Think of an autonumber field as MSAccess's internal way of managing your records and keeping them problem-free when deletions/additions happen but you should never try to update/change the autonumber field directly! (Access will complain and if you do succeed in updating it, you'll have potential recordset problems.)

    The autonumber field is often designated as the primary key due to it's uniqueness and MSAccess's prevention of duplicate values on this type of field. But you can also create a primary key instead using a combination of one or more other fields on which you want indexing and prevention of duplicates to happen on. If you do establish a different field(s) as the primary key, you also want to make sure the autonumber field's Unique value property is then set to "Yes (No Duplicates)" so you can then establish relationships on your tables to this field if it's used to relate 2 tables.

    It's good practice to have an autonumber field type in every data table regardless of how it's used or not used.
    Last edited by pkstormy; 10-03-09 at 00:05.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Missingling is bang on
    autonumbers are there to provide an easy mechanism to make sure a record is unique. they should not be rely on in the outside world. you have no control over them.

    In short the problem isn't the tool (Access) its the way its being used.

    if you want guarantee that a number is in a specific sequence then you will have to write your own bit of code to do that, or persuade your users that the sequence has no mening, most normal human beings will accept that once the reason for the gaps is explained. Accountants and tax 'people' however are really really keen on numbers in sequence missing numbers cause them to panic and presume the system is on the fiddle.

    so the option is yours
    either write your own bit of vba to get the next avaialble number in sequence
    OR
    persuade the users that they don't need numbers in sequence.
    OR
    when deleting do a soft delete (ie set a flag which shows the row is no longer current), and then report that row where required indicating its been deleted.

    Unlike PKStormy I don't think that an autonumber should be present in all or most tables, an autonumber is a surrogate key and is use when there isn't a better candidate key from the actual data that row represents, or where the candidate key data changes leading to performance problems
    Google
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by healdem
    Unlike PKStormy I don't think that an autonumber should be present in all or most tables, an autonumber is a surrogate key and is use when there isn't a better candidate key from the actual data that row represents, or where the candidate key data changes leading to performance problems
    I only mention that autonumbers SHOULD be present in any DATA tables is to prevent problems (in certain versions of MSAccess) with updating the data on the form and requiring the docmd.save command. I've also seen weird things happen to data tables without an autonumber. It seems to me that you'd want an autonumber field on data type tables anyway, regardless if it's the primary key or not. I found this to be especially beneficial when working with certain backend servers. Depending on the structure, I may use this field to link other relational tables (as in the google example, AddressID).

    If it's a lookup type table, I don't always use autonumber fields.
    Last edited by pkstormy; 10-03-09 at 23:42.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Oct 2009
    Posts
    5

    Thanks to all

    I didn't think there was a way but i had to check the main reason i was asking was the powers to be saw it and wanted it fixed. Normally they would not see the auto number since it would be "hidden" in the form when entering data.

    I do agree that you do not need the auto number in every table as long as you are able to provide the unique identifier, if not then auto number is a nice little way to make it work.



    Pease out
    DaveDog89

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    An autonumber (Identity, surrogate key) can be in every table...but should never be used
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Dogma! lol... I use Autonumbers all the time as primary keys... and never had a problem!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm with ST. Try creating a "DATA" table without an autonumber field and then design a data entry form. If you're using anything less than MSAccess 2007, you'll eventually encounter some kind of problem or need additional coding somewhere. (especially if you start dealing with relational tables and subforms.)

    For a SIMPLE creation of a autonumber field (that maintains itself), I have to wonder you wouldn't want to create one (IN YOUR DATA tables) and risk possible problems. For me, I'd rather create the autonumber field versus troubleshoot a problem for not having one. SQL Server plays much nicer with them as well.

    Some of the Unix type database systems I've worked with have always had an "internal" autonumber type field on their tables (although typically hidden.)
    Last edited by pkstormy; 10-11-09 at 05:10.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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