Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2006
    Posts
    4

    Unanswered: need help with autonumber when importing

    I have a database that a client of mine has started to create. He wants to be able to import data from an excel spreadsheet into a table in Access. I created a macro that works fine but now I am having an issue with importing additional data.

    When I try to import additional spreadsheets it puts the data in the excel spreadsheet at the top of the table and it doesn't autonumber them.

    I can't do an append query either because then he would be linking every spreadsheet and he would have too many spreadsheets linked in the database.

    His excel sheets consist of Agency ID numbers, first name, last name, SSN, etc. He also has a column in the table called order number and internal ID. Which I duplicated in the spreadsheet. He wants the internal ID to autonumber. When I first use the macro to import the spreadsheet into Access it numbers the rows accordingly (after I fill the excel sheet using the series option). However, when I try to import an additional spreadsheet (which I just made up)I have to fill in the Internal ID number manually and consecutively according to the last record in access.

    The sample spreadsheet he sent me has 2079 records which imported fine with the macro and then I made a second spreadsheet with two rows with information in them and if I don't put in the numbers 2080 and 2081 it imports them at the top of the table without any number in the internal ID. I hope this is making sense.

    Can anybody help me make it so that it automatically puts a number (consecutively)in the internal ID field when I use the macro to import another spreadsheet.

    Thanks in advance

    Tracy
    Last edited by ambercubby; 08-27-06 at 23:24.

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I hope this is making sense
    ... clear a s mud!

    Access doesn't let you assign values to an Autonumber field - that would defeat the purpose of the Autonumber option. You mentioned that new data does not get an ID - that indicates the field is not setup as an Autonumber type.

    You also mentioned that the new data goes to the top of the table. Don't forget that in a DB, the "order" of the data is irrelevant, so this itself is not a real issue.

    To use Autonumber, you'll have to set the field up as "Autonumber" and don't try to assign values to it (Access will kick up an error if you do). As you add records to the table (regardless of how you add them) the Autonumber field will do just that: automatically number the records for you. If you want to assign the numbers your self (through macro, code, query or in the spreadsheet) then you can not use an "Autonumber" field and will have to make sure you add the numbers manually.

    To answer your last question, open the table in design mode, add a field and set it to "Autonumber" data type.

    tc

  3. #3
    Join Date
    Aug 2006
    Posts
    4
    When I go to the order details table in design view it says that field is autonumber. If I do not assign a value to the Internal ID field in the spreadsheet it comes up with an import error saying that the fields had a null value but if I number the internal ID field manually in the spreadsheet it imports just fine. I don't want to manually input numbers into the internal ID field in the spreadsheet, I want access to do that for me but it won't let me.

    Tracy

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    It should look like the attached image. If it looks like that and doesn't work, then something is wrong with your Access.

    tc
    Attached Thumbnails Attached Thumbnails Sprite19.jpg  

  5. #5
    Join Date
    Aug 2006
    Posts
    4
    It does look like that except it doesn't have the asterick. So now my question is this, he created the tables in Access 2003 and I am using Access 2000 does that make a difference?

    Thanks,

    Tracy

  6. #6
    Join Date
    Aug 2006
    Posts
    4
    Never Mind, I got it. I needed to delete the column in excel entitled internal ID. Thanks for the help.

    Tracy

  7. #7
    Join Date
    Apr 2008
    Posts
    2
    Well, I've just spent about 5 hours (not an exaggeration) trying everything I can think of to get through this same problem. And yes, I deleted the ID column in the spreadsheet.

    I got the "Null value in an auto-number field" error when exporting from xls. I've also tried importing from csv. Neither will go in. I've also tried changing the Access ID column to just a number field.

    I've tried including the ID column in the document I'm importing from (xls and csv) and I've tried it without the column. I've tried working with the Advanced settings in the "Import Specification" dialog (using the "skip" option, changing the column to Indexed, etc.) all to no avail.

    I have no idea what else to try. The REAL kicker is I'm importing another csv (or xls) table into another Access table in the same database as the offending one. The second table also has an ID column set to AutoNumber but in this one the import lets me insert my own numbers from the imported file without any complaints, as long as there aren't any existing IDs with the same value.

    I would VERY much appreciate any help suggestions.

    Thanks in advance.

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    You'll have to give some details.

    But, before pulling out anymore hair, try creating a new, blank table with no field specs and putting the data there. Assuming that works, assign the specific data types one or two at a time and see if it coughs up any errors (imported data is notorious for trying to pass a date in an invalid format, for example - the built in importing functions will convert recognizable dates to DateTime values, and the rest to Strings - which will fail if the field data type is Date).

    Every time I've had issue importing, it was a numeric field or date field in which some (or sometimes all) of the data did not convert correctly. This has been the case even when moving from one DB architecture to another (SQL has a "BigInt" that Access doesn't understand, for example).

    Generally, it's not the autonumber field. When pasting data from Excel, Access must have a column in the set for the autonumber field but will ignore any data in it and autonumber instead.

    good luck,
    tc
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The problem here is that you cannot insert explicit values into an autonumber field (it would be that automated if you could, think about it!).

    You either have to change the datatype to a simple numeric so you can add your wn values, or exclude the column from your import and Access will generate the sequential autnumber for you.
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2008
    Posts
    2

    Not what I thought it was...

    Well after another couple of hours trying different things on the import (for a total of about 6 hours on this prob!), I was re-analyzing what I was importing and noted that one of the fields was blank (empty string) where the Access column was expecting a currency data type.

    I was recieving an error for a while for trying to put in data in the ID column, but I fixed that. However, even after fixing that I was still recieving an error for the data type problem - out of my frustration I just didn't bother to actually read the error at that point...

    Lesson learned: don't just assume the data going in is correct! And make sure data types match!

    Thanks for the help, though, guys.

  11. #11
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    No problem - glad you got it working.

    Don't forget that the error messages Access pops up are hit or miss for actually identifying the problem at all - too many times the message in the error has nothing to do with the actual problem!

    tc
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yup! Never assume anything!
    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

  13. #13
    Join Date
    Nov 2011
    Posts
    3

    Excel to Access import errors

    When i try to import from Excel to Access I get "Null value in an auto-number field" and no import
    My first field in access is an auto number index field. I've tgried deleting that column in excel, blanking it, formatting it different ways, all to no avail.
    Please help.
    Chet Rogers

  14. #14
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    woa - this goes back a few years!

    How are you importing?

    Can you copy the data from Excel and "Paste Append" in the table? If you do this, make sure you include a blank column where the autonumber data will be. This will yield a "paste errors" table for any rows that don.t convert.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  15. #15
    Join Date
    Nov 2011
    Posts
    3
    I was trying to Access/Import/from Excel. I just tried the copy/paste append, skipping the auto number field and I finally got it imported after i included the first row with field names.
    Thanks for your help. I just spent hours of frustration before i;you jumped in!

Posting Permissions

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