Appending records to a table from an Excel file

[dhstein]

I generally add records to a table through a program. But I have a large
number of records that I'm trying to add using an Excel table. The records
are being added to two tables that have a relationship. When I use the
program, I add one record to one table and then the corresponding record to
the other table and it works fine. When I try to append to the table from
the Excel file I get the message :

2 records were lost due to key violations

If records were lost either the records you pasted contain primary key
values that already exist in the destination table or they violate
referential integrity .....

So I'm not sure what's going on here. Any help is appreciated.

 

[Arvin Meyer MVP]

The unique index field designated as a primary key has a duplicate value.
Either find another field or otherwise remove the key from that field.

 

[dhstein]

Arvin, Thanks for your reply. I'm not clear about what you're saying. The
data that I'm importing from Excel has a value that's already in the table ?
I checked that already and that's not the case. I came across some other
information that the Access data type and the Excel data type may not match -
which might also cause this problem. So I'm still trying to figure this out.

 

[John W. Vinson]

Arvin, Thanks for your reply. I'm not clear about what you're saying. The
data that I'm importing from Excel has a value that's already in the table ?
I checked that already and that's not the case. I came across some other
information that the Access data type and the Excel data type may not match -
which might also cause this problem. So I'm still trying to figure this out.

The problem is that Access has strong datatypes and Excel DOESN'T.

A cell in a spreadsheet can contain any datatype. A column in a datasheet can
contain a mix of text, dates, numbers... anything goes. And there's no way in
Excel (to my knowledge, I'm no Excel expert) to say "Column D contains nothing
but numbers".

Access must *guess* at the datatype based on the first few rows imported or
linked. If you have a column that contains mostly numeric data and a few cells
containing text, Access will guess (incorrectly) that a numeric datatype will
work, and will throw errors when it hits the cells containing non-numeric
data.

 

[Clif McIrvin]

Access must *guess* at the datatype based on the first few rows
imported or
linked. If you have a column that contains mostly numeric data and a
few cells
containing text, Access will guess (incorrectly) that a numeric
datatype will
work, and will throw errors when it hits the cells containing
non-numeric
data.

And conversely, if Access thinks the column contains text, it will throw
errors when it hits numeric data.

For a summary I put together on the data type issue, see my post a few
minutes ago under the thread #Num Error Linked Excel Table.

You said earlier you had verified that there were no duplicate key
values in the Excel data ... have you verified that there are no
duplicated values within the Excel data itself?

Can you identify precisely which Excel rows are being discarded?

The one other possibility which came to mind is back to what John is
suggesting -- I've not tested this idea, but it seems plausible that a
#Num error or an empty cell in the key value field might be violating a
"required" property and throwing the "duplicate value" error message.

 

[John W. Vinson]

And conversely, if Access thinks the column contains text, it will throw
errors when it hits numeric data.

Not in my experience. After all, "83660" is a perfectly valid and normal text
string.

 

[dhstein]

Arvin, John, Clif

Thanks for trying to help with this. I'm about ready to give up and write a
program that reads the excel file (or a text file) and applies the updates.
It should work since the code works fine to add the records through the form
- so I just need to read from a file. I tested the excel file with one
record and had the same problem - so I don't know what's going on. But it
makes me wonder about something. If there is a relationship between two
tables - does access allow the record in one table without the corresponding
record in the other. In the VBA program - I add both records at the same
time (same computer time anyway - obviously not simultaneously) But when I
do it with the excel file, I'm just trying to add the record to one table at
a time. I don't know if that could be related to this issue. Thanks.

Access must *guess* at the datatype based on the first few rows
imported or
linked. If you have a column that contains mostly numeric data and a
few cells
containing text, Access will guess (incorrectly) that a numeric
datatype will
work, and will throw errors when it hits the cells containing
non-numeric
data.

And conversely, if Access thinks the column contains text, it will throw
errors when it hits numeric data.

For a summary I put together on the data type issue, see my post a few
minutes ago under the thread #Num Error Linked Excel Table.

You said earlier you had verified that there were no duplicate key
values in the Excel data ... have you verified that there are no
duplicated values within the Excel data itself?

Can you identify precisely which Excel rows are being discarded?

The one other possibility which came to mind is back to what John is
suggesting -- I've not tested this idea, but it seems plausible that a
#Num error or an empty cell in the key value field might be violating a
"required" property and throwing the "duplicate value" error message.

 

[John W. Vinson]

Arvin, John, Clif

Thanks for trying to help with this. I'm about ready to give up and write a
program that reads the excel file (or a text file) and applies the updates.
It should work since the code works fine to add the records through the form
- so I just need to read from a file. I tested the excel file with one
record and had the same problem - so I don't know what's going on. But it
makes me wonder about something. If there is a relationship between two
tables - does access allow the record in one table without the corresponding
record in the other. In the VBA program - I add both records at the same
time (same computer time anyway - obviously not simultaneously) But when I
do it with the excel file, I'm just trying to add the record to one table at
a time. I don't know if that could be related to this issue. Thanks.

That is almost certainly the issue!

Indeed, that's what referential integrity is designed to do: to prevent
addition of a "child" record which has no valid "parent".

The error said that two records were not added due to key violations, right?
That suggests that you're adding records to the "many" side of an enforced one
to many relationship, and that two (only two, the rest of the records would
have gone in fine) rows in the spreadsheet had a value in the linking field
which does not occur in the main table.

 

[dhstein]

Thanks John. So now I have some more questions. When I do this in a
program, I update one table, then the other . So why does that work? Next
question, is can I update one table first - the one with the parent record ?
And which is the parent record the one on the many side or the the other one?
Or must I do this from a program ? Thanks.

 

[dhstein]

John,
I just reread your response - I think I understand better now. I'll
check for mismatched records and see if that's what's going on. Thanks for
your help on this.

 

[John W. Vinson]

Thanks John. So now I have some more questions. When I do this in a
program, I update one table, then the other . So why does that work? Next
question, is can I update one table first - the one with the parent record ?
And which is the parent record the one on the many side or the the other one?
Or must I do this from a program ? Thanks.

Perhaps when you've done it before the data was clean and there just weren't
any invalid records. It's hard to say.

Yes, you should update the "ONE" side table - the parent - first, and then
update the "many" side. You can update them concurrently in code if you use
"Inconsistant Updates" but in this case (if you have the choice) it shouldn't
be necessary.

Not sure what your last question means - you ARE using a program!

 

[Clif McIrvin]

Not in my experience. After all, "83660" is a perfectly valid and
normal text
string.

It's a fine point, but if " '83660 " (text characters) exist in a text
column Access will process them just fine ... but if the number " 83660
" (whether integer or floating) exists in a cell within an otherwise
text column Access will throw a #Num error ... at least in my
experience.

 

[dhstein]

John

I gave up on importing from Excel - too many problems - besides the ones
I mentioned I also got a "subscript out of range" message. In any event, I
wrote a program to read from a linked text file and it's working now. But my
question is - I'm not using "Inconsistent Updates" - at least I don't think I
am - and the program works fine. Both tables get updated with the new
values. My program updates the parent table with the record and then adds 2
child records in the other table. So where did I specify Inconsistent
updates - or did I just get lucky? Thanks.

David

 

[Clif McIrvin]

David - There is no requirement for parent records (the "one" side) to
have child records (the "many" side) so you can add parent records all
day long without related child records and never throw an error; but try
to add one child record that does not have a related parent record and
you have violated referential integrity -- so as long as your code adds
the parent record first you will never have an error; nor will you ever
need to specify "inconsistent updates".

Perhaps this quote from the A2003 installed help will clear things up
for you:

I entered " referential integrity " in the help search box; from the
topic:

About relationships in an Access database (MDB)

comes this quote:
Referential integrity

Referential integrity is a system of rules that Microsoft Access uses to
ensure that relationships between records in related tables are valid,
and that you don't accidentally delete or change related data. You can
set referential integrity when all of the following conditions are met:

a.. The matching field from the primary table is a primary key or has
a unique index.
b.. The related fields have the same data type. There are two
exceptions. An AutoNumber field can be related to a Number field with a
FieldSize property setting of Long Integer, and an AutoNumber field with
a FieldSize property setting of Replication ID can be related to a
Number field with a FieldSize property setting of Replication ID.
c.. Both tables belong to the same Microsoft Access database. If the
tables are linked tables, they must be tables in Microsoft Access
format, and you must open the database in which they are stored to set
referential integrity. Referential integrity can't be enforced for
linked tables from databases in other formats.
The following rules apply when you use referential integrity:

a.. You can't enter a value in the foreign key field of the related
table that doesn't exist in the primary key of the primary table.
However, you can enter a Null value in the foreign key, specifying that
the records are unrelated. For example, you can't have an order that is
assigned to a customer that doesn't exist, but you can have an order
that is assigned to no one by entering a Null value in the CustomerID
field.
b.. You can't delete a record from a primary table if matching records
exist in a related table. For example, you can't delete an employee
record from the Employees table if there are orders assigned to the
employee in the Orders table.
c.. You can't change a primary key value in the primary table, if that
record has related records. For example, you can't change an employee's
ID in the Employees table if there are orders assigned to that employee
in the Orders table.
Cascading updates and deletes

HTH!

 

[John W. Vinson]

John

I gave up on importing from Excel - too many problems - besides the ones
I mentioned I also got a "subscript out of range" message. In any event, I
wrote a program to read from a linked text file and it's working now. But my
question is - I'm not using "Inconsistent Updates" - at least I don't think I
am - and the program works fine. Both tables get updated with the new
values. My program updates the parent table with the record and then adds 2
child records in the other table. So where did I specify Inconsistent
updates - or did I just get lucky? Thanks.

You got exactly the results I would expect. With the program you're adding the
records to the parent table, and THEN afterwards adding records to the child
table. That sequence will not trigger any errors; you never have an attempt to
add an "orphan" record.

My guess is that the import from Excel may have entered the records in the
wrong order.

Or... probably more likely, give that there were only two error records -
there was a problem with the contents of the data.

 

[dhstein]

Clif, John,

Thanks for all your help with this problem. Now I think I understand
what was happening.

Regards,

David

 

[Clif McIrvin]

Clif, John,

Thanks for all your help with this problem. Now I think I
understand
what was happening.

Regards,

David

You're Welcome!