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.
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.
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.
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.
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.
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!
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.
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.
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!