Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    autonumber not populating (access2000)

    i am having a problem witht he autonumber feature in access2000. i had a DB created in 97 that imported an xls file. the first column of that xls file would be left blank. on import access would asign that blank field an autonumber. thus it was also our primary key (not really relevant though). we have since upgraded to 2000 and the same commands no longer work.

    in access2000 we are having an autonumber problem. i can not see any differences in the commands of the autonumber field but no autonumber is being assigned to the newly imported data. i have created simple test databases with 2 columns. one is ID# and the other NAME. in excel i leave ID# blank and then enter 5 names in the NAME field. I then import this file into access where the ID field properyty is an autonumber and the NAME is considerede text.

    the import produces an error table saying "null value in auto-number field". but if i want access to assign the autonumber itself why wouldn't it be null in excel??? do i have to set a property somewhere for this? it will still do the import to the correct tale just fine except that the ID field remains blank, so only the NAME column has data.

    If i open the table and try to add a new row of data manually, then the autonumber populates itself. however it always starts out at 453054332. it wont even start at 1.

    so my questions are how can i make the autonumber feature apply to imported data? and how can i start the autonumber to begin at 1 and increment correctly? try it yourself and see if an imported excel file will get autonumbers geenerated for it. what good is an autonumber if it doesnt populate. the troubles of access 2000 continue for me. thans alot everyone, this site is awesome.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: autonumber not populating (access2000)

    First of all, if you want to have the autonumber start at a specific number, then maybe you shouldn't be using an autonumber. Autonumbers are best used in situations where you don't even see them; I think of it as an Internal System ID. To start at 1, you need to have an empty table, compact the database, then add records.

    As for importing, I'd just link to the Excel spreadsheet, then use this linked table as the source for an append query to create the records you want in the other table.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumber not populating (access2000)

    I just did a quick check. If you want the autonumber field populated automatically by Access, you can not have that column in your Excel spreadsheet. Leaving the column blank in Excel is not sufficient (as you've seen), it must not be there.

    That's a big autonumber value. Are your autonumbers set to increment (the default) or random?

    Use Compact and Repair to reset your automatic autonumbering to 1 more than the current highest value or to 1 if the table is empty.

Posting Permissions

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