Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    48

    Unanswered: Autonumber problem in Access database

    I exported data from a table in an Access database into Excel. After manipulating the data, the data was imported into a modified version of the original database. The data contained the original autonumbers assigned to each record. I was assuming that when records were added to this table in the modifed database that the next autonumber assigned would be numerically consecutive. It wasn't even close!

    How do I get the autonumber in the modifed database to pick up where the imported data autonumbers left off?

    I hope this is making sense to someone.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I'll give you the standard answer that I see quite a bit on this site. You should not be using AutoNumber if you want the values to be in consecutive order. I believe the code bank has alternate ways to come up with consecutive numbers.

    But to answer your question, you should try compact your database. The Autonumber should find the highest number then continue at that value plus 1. This method also works if you want to reset the Autonumber to 1 for a given table after deleting the records.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are reimporting the data, albeit slightly modified, shouldn't you be UPDATE(E)ing the data, rather than adding to it?

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Further info

    "The access web" - Dev Ashish has a clear explanation of the Autonumber field and why not to ever use it as a key field...

    http://www.mvps.org/access/general/gen0025.htm

    PS The site is also an excellent resource for access informaiton.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by garethdart
    "The access web" - Dev Ashish has a clear explanation of the Autonumber field and why not to ever use it as a key field...
    erm thats not what Dev actually says is it...

    Autonumber is simply a way to create a unique identifier for each record. It should NEVER be treated as a meaningful piece of data.
    that not the same as 'never use it as a key field'. Its perfectly acceptable for the purpose it is designed for... to provide an quick and simple method of making a row unique and therefore is often a good candidate for primary key (in fact its excellent for that purpose, and unless you have a natural key to be used in preference, in which case you wouldn't use an autonumber column in the first place).

    The problem is when people try to give meaning to an autonumber column OUTSIDE the system for things like GRN, order No. that is when you need to devise your own autonumber strategy. A totally different proposition

    BTW agree with the site reference.. its is very very helpful, and so far Ive found nothing but good stuff under Dev's moniker

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Autonumber

    "The problem is when people try to give meaning to an autonumber column OUTSIDE the system for things like GRN, order No. that is when you need to devise your own autonumber strategy."

    <Yeah this is really what I was alluding to, just got the impression from the original question that Elizabeth1 might have been doing just this?

    Seen many issues when people use autonumbers for 'order numbers' and such like.

    Think I very much confused the issue by COMPLETELY inapropriate use of the word 'key'!
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

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