Null value in an auto-number field

[mharkin]

Hi, I am trying to import data from an excel 2007 spreadsheet to access 2007 table. Everything seems to work fine apart from one ERROR: my ID field is set up as a auto number and its the primary key. When i import the data the Error message states “Null value in an auto-number field”

Can anyone please help me with this???


Any help would be very much appreciated!
Thanks
Mark

[JoeM]

Are you trying to import something into the Auto Number field?
Sometimes, I find if it imports better to make the AutoNumber field the last field in the table, so all the other fields import in order and don't get confused for the Auto Number field.

[mharkin]

Thanks for your reply,

on the spreadsheet I have the ID column blank, I cannot move the field because it is a Primary field and is linked to several other tables

Do you have any other suggestions why it wont import correctly>??

Thanks

[JoeM]

You cannot/should not try to import anything into the Autonumber field, Access will automatically assign it upon import.
If the column is blank in your Excel file, why can't you remove it (if its blank, its not holding anything of value)?
As long as your Access table has the Autonumber field in it, it will automatically be populated when you import from Excel.

[mharkin]

I have done as you advised and deleted the blank column from the spreadsheet, however it still did not import all the data. and It did not generate a error table? any thoughts why this may be??

Mark

[JoeM]

Do you have field names at the top of your Excel range you are importing?
Do they exactly match the field names in Access?
Are you importing an whole sheet, or just a named range?

[mharkin]

Yes I do have field names and they match the fields in my access table and I am importing a whole sheet

[JoeM]

It makes no sense to me. I do stuff like this all the time without any issues.
Does the Excel you are trying to import have just one sheet?
How exactly are you trying to import the Excel file into Access (what methodology are you using)?

[mharkin]

Yes the spreadsheet only has one sheet.
I have found a simple solution to my problem after all this time, I have imported the spreadsheet and got it to create a new table rather than import to an existing table.
I then copy and pasted the data from one table to the next.

[JoeM]

That is one workaround. Perhaps one of the fields in your table is not formatted the same, and that is causing issues.
One of the problems with importing Excel files is you don't get to specify the format of the fields coming in. Access looks at the first ten records, and tries to "guess" what the format is. So if you have some fields which, in looking, at the first ten records could be interpretted different ways, it can cause problems (common with some alphanumeric fields). Sometimes Nullls or blank values can wreak some havoc too.

It would be interesting to compare the new table format you imported to the old table format that it would not import to. Note the differences, and maybe you can make changes so you can import it directly.

Regarding your workaround, another option is to use an Append Query to write the records from one table to another.