Error Message for Append Query

[Guest]

The solution to my last problem was to use an Append Query. This has worked
for most of the tables I wanted to add to my BIGTable. However, several
tables that I want to Append gave me the following error message:

Microsoft Access can't append all the records in the Append Query.
Microsoft Access set 0 fields to Null due to a type conversion failure, and
it didn't add 0 records to the table due to Key Violations, 0 records due to
Lock violations,a nd 245 records due to Validation Rule violations. Do you
want to run the Action Query anyway? To ignore the errors and run the query
anyway, click Yes.

When I clicked yes, only 31 records were appended to the BIGTable.

Please explain the type conversion failure, key violations, lock violations
and validation rule violations. None of the fields have validation rules.

Keep in mind the original tables were imported from Winfax into Outlook,
then from Outlook into Access.

 

[Guest]

A type conversion error would be something like trying to put text such as
"ABC" into a number or date field. Access just inserts nulls or blanks
instead. Not your problem here.

A Lock violation would be where another database user has certain records
locked up. Not your problem here and pretty rare anyway.

You had no key violations so that's not your immediate problem either. If
you had a primary key or unique indexes and the append query tried to insert
duplicates, you'd see something larger than 0 there. Also if you were trying
to insert records into a child table but did not have a matching record in
the parent table, that could cause the error. For example you were inserting
records into an Orders table, but there was no such Customer. For this to
happen you would need the relationship defined with Referiential Integrity on
in the Relationship Window. That's a good thing by the way.

That brings us to Validation Rules. You may have a validation rule for a
field in the BIGTable table that says something like a date field must be
greater than today's date ( >Date() ) and you are trying to insert
yesterday's date. You could also be trying in insert null values into a
required field. Check each field in the table for such rules.

 

[Guest]

I'm having the same problem and while I don't know how to fix it, this is
what I think is happening. If you have lookup fields in your database file,
they are storing a number value; and the file your appending from probably
has text values. I was trying to append an excel file I had imported into
access. I had to change all the text cells to the appropriate lookup number
so all the records would import. This worked, and with the replace function
it was fairly easy to do, but I know there should have been a better way.

Laura

 

[John Vinson]

I'm having the same problem and while I don't know how to fix it, this is
what I think is happening. If you have lookup fields in your database file,
they are storing a number value; and the file your appending from probably
has text values. I was trying to append an excel file I had imported into
access. I had to change all the text cells to the appropriate lookup number
so all the records would import. This worked, and with the replace function
it was fairly easy to do, but I know there should have been a better way.

That's one of the many, many disadvantages of Lookup fields, and a
good example of why they are so confusing. It looks perfectly
reasonable to import a Text value into the lookup field - you *SEE*
text in it. But that's not what's actually there!

John W. Vinson[MVP]

 

[Guest]

Ok, so the reason I have the look up fields is because I have all these
relational databases...so what is your recommendation for the better design
solution? My database design is still in it's infancy, so if I can make
adjustments early on it would be better.

Thanks
Laura

 

[Guest]

Lookup tables are good. It's Microsoft's implementation of them at table
level that is bad. Very bad.

If you have a list of something like Divisions in a COMPANY_DICISIONS table;
create a relationship between this table and Employees; create combo boxes or
subforms to link these tables, that's a good thing. You have control and will
see the actual foriegn key data in the table instead of what looks like the
data.

In other words, do it the hard and right way and NOT Microsoft's.

 

[John Vinson]

Ok, so the reason I have the look up fields is because I have all these
relational databases...so what is your recommendation for the better design
solution? My database design is still in it's infancy, so if I can make
adjustments early on it would be better.

As Jerry says, it's not the relational tables ("lookup tables") that
are the problem; it's Microsoft's choosing to put them into the Table.
In my opinion Tables should be data repositories, and should be kept
"under the hood".

You can and certainly should use Combo Boxes, Listboxes, and all the
other appropriate tools for data entry and editing - using these
lookup tables - but you should do so on Forms. It is NOT necessary to
define a Lookup Field in a table in order to do so.

John W. Vinson[MVP]

 

[Guest]

This is faxylady again. Glad to know more than just myself is having
problems. I went back to my BIGTable and checked each field. None has any
validation rules. Keep in mind the table being appended was originally a
Winfax dBase III file that was imported into Outlook, then from Outlook into
Access. I wondering if some of the problem could be there.

 

[Guest]

Sorry I've seem to taken over your thread and will repost my issue under a
new topic. As to your issue, the only thing I can think to check is make
sure the fields in your winfax file are the same 'data type' as to the table
your appending to.

 

[Guest]

Thank you for your response and input. However, if someone sees this and can
help, please do. My problem of not being able to append is still there.
There are no lookup fields in these tables. None of the fields in the
BIGTable have validation rules. These smaller tables have been imported from
Winfax as dBase III files into Outlook, then into Access. Please help.

 

[John Vinson]

Thank you for your response and input. However, if someone sees this and can
help, please do. My problem of not being able to append is still there.
There are no lookup fields in these tables. None of the fields in the
BIGTable have validation rules. These smaller tables have been imported from
Winfax as dBase III files into Outlook, then into Access. Please help

Do you have any Relationships defined? Are ANY of the fields marked
Required in the field properties? These can also give validation rule
errors (if you try to add a record that is NULL for a required field
or which breaks the relationship rule).

John W. Vinson[MVP]