• autonumber not populating (access2000)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » autonumber not populating (access2000)

    Author
    Topic
    #373751

    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.

    Viewing 1 reply thread
    Author
    Replies
    • #601783

      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.

    • #601809

      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.

    Viewing 1 reply thread
    Reply To: autonumber not populating (access2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: