Importing an Excel spreadsheet to an Access database

[rta]

Hello.

I am trying to import an Excel data sheet to an existing table in an Access database and I am getting an error message that all of the data was unable to be appended-the data was lost or deleted because of key violations. In the existing database, I have a field called entry_number, whose data type is auto number (one that is generated by the computer), that I stuck in there so that the database would have exactly one primary key and on the Excel data sheet, there is also a field or column called entry number, however it is not an auto number. I don't know whether or not this is why I am getting the error message, but I have a feeling this is the reason-one number is genearated and the other isn't (as far as the data goes). Is there a way for an Excel column or field to have a computer generated number, like Access allows you to have in tables, whenever a new record is inserted? Another thing that may be useful is that the Excel datasheet and the table in the database have the exact same field names and properties for them.

I hope I am making any sense. If any of you need clarification on this, please let me know. Thank you and I will appreciate any help you can give to me.

RTA

 

[tlbroadbent]

The error is likely due to duplicates in the Entry Number column. The Excel spreadsheet probaly contains Entry Numbers that are the same as those in the table.

I recommend that when importing the Excel spreadsheet, use the import wizard and check Do not import field (Skip) on the Entry Number column. Then when you import the spreadsheet, Access will generate autonumbers for the new rows. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[rta]

You know what, the data is actually not being appended because of a validation rule error. What's this error all about and how might I be able to fix it? Thank you sooooooo much.

RTA

 

[rta]

Okay, I'm a little new to this Tek-Tips thread thing. I realized I posted my own thread, when I really wanted to reply to you.

But anyway. Here it is...

The data is actually not being appended because of a validation rule error. What's this error all about and how might I be able to fix it? I have been able to do some reading on it and have tried ways in which I might be able to fix the problem, but nothing has worked so far. Thank you sooooooo much for any more help you can give to me.

RTA

 

[tlbroadbent]

Validation rule violations can be foreign keys that don't match the primary key in another table, values that are out of range, etc. There is no way that anyone unfamiliar with the database can tell you what the validation error is. You will have to analyze the data and the table. Start with the table in Design mode and look at each column (field) to see what validation rules are set. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin