Records will not append to another table due to Key violations and

[faxylady]

I have several tables I am trying to append to a comprehensive table called
the BIGTable. I am having trouble with my append query giving me an error
message saying it could not append x amount of records due to Key violations
and validation rule violations. None of the fields in either table are
indexed or have validation rules. Some of the tables have fewer fields that
the larger table but that has not stopped other tables from appending.

Please explain the problem and how to fix it. Thanks.

 

[Jeff Boyce]

Key violations are just that ... something about the data you are trying to
append represents a "duplicate" key. What key(s) do you have in the
"appended-to" table?

Validation error messages can happen because the data you are trying to
append does not match the data type in your "appended-to" table. Can you
confirm that you aren't trying to append text to a numeric field, for
example?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Allen Browne]

See:
Why can't I append some records? Trouble-shooting imports
at:
http://allenbrowne.com/casu-19.html

 

[faxylady]

Yes. All the data types in both tables are text.

 

[faxylady]

All the fields are text data types, there are no primary keys in either
table. I did review your article. Thanks.

 

[Allen Browne]

Did you examine the Required property of the fields?

Did you check for zero-length-strings in the source data?

 

[faxylady]

As far as I know the Required property in all fields in both tables is set to
no. Perhaps there is something further I need to examine that I am not aware
of rather than just the lower portion of design view? How do I check for
zero-length-strings inthe source data? Thanks for your help.

 

[Allen Browne]

Lower pane of table design adequately checks for Required (assuming there is
no Validation Rule on the fields or on the table itself.)

What is the source data? Can you create a query that uses it? If so, you
could test each field that could be interpreted as text (i.e. each field
that left-aligns when you view the query output). The criteria would be an
empty string, i.e.:
""

 

[Jeff Boyce]

To follow on Allen's suggestions, if a field you are trying to append
contains a zero-length string, and the field you are appending to does NOT
allow zls, that would trigger a validation error.

One approach might be to start leaving out fields in the append and see when
the error stops. I'd probably do as Allen suggests and inspect the
properties of the tables first.

You are, of course, working with backups in place, right?!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[faxylady]

The source data originally was automatically transferred from a Cardscan file
into Outlook. From Outlook, it was exported into Access. I have checked all
the fields in the source table and the table being appended to and cannot see
any required fields or fields with validation rules.

The reason this is important is because some of the tables I am trying to
append have 8-900 records and are only allowing 2-300 to append to the larger
table. Thanks for your continued help.

 

[Allen Browne]

Try using a Make Table query to get the data into a new table in Access.
Alternatively, create a table with all Text fields, plus one AutoNumber
field as the last fields, and use an Append query to populate this table.

Once you have the data in this temporary table, you can query, it, examine
it, typecast it, clean it up, and so on, until you sort out all the issues.
Then you can append it to your real table once all the issues are solved.

 

[faxylady]

I will try this and get back to you. Thanks for all your help.

 

[faxylady]

Sorry, the Make-Table query did not work. I still cannot append nearly half
the records to the other table due to validation rule violations or key
violations. No validation rules or autonumbers are in either table.

 

[Allen Browne]

Something else is wrong, then.

Sorry: I'm out of suggestions.

 

[BruceM]

Some information is missing from your description, I think. A couple of
things: Check the field's Indexed property jsut to be sure there isn't
something that disallows duplicates. Also, try every suggestion, and
describe the results. Did you try the table with all text fields, as Allen
suggested? I know you said your table is all text fields, but why not give
it a shot?
One more thought: Export the data as a text file, then use File > Get
External Data > Import to pull the data into a new table.

 

[John Vinson]

Sorry, the Make-Table query did not work. I still cannot append nearly half
the records to the other table due to validation rule violations or key
violations.

Let's clarify this:

You're running a MakeTable query.
That MakeTable query is giving VALIDATION RULE VIOLATIONS!???

I have no idea *how* that could possibly be happening, but - again, I
know you've done this and apologize for asking again - could you post
the SQL view of the MakeTable query which is generating these errors?

John W. Vinson[MVP]

 

[faxylady]

I don't quite understand what you mean by "try every suggestion." I already
have, I think. Unless you mean that I should check the properties of all the
text fields--I haven't done that yet. Perhaps, I could send you a copy by CD
of this database. I would be willing to pay, but not at the moment. I am in
financial difficulty right now. Over 2000 fax nos are not being accessed due
to these difficulties. One of the MVPs mentioned something about strings
earlier. I do not know how to follow up on that. Please explain. Thanks
for your help.

 

[BruceM]

Perhaps you have tried every suggestion, but there is no way for anybody
else to know that unless you mention it. I was thinking specifically about
the suggestion to try creating a *new* table with all text fields plus an
autonumber field. Even though your table may be exactly that, something
seems to have changed, so starting over could help. Also, what was the
result of checking the Indexed property? Can you create a query using the
data (this was also asked)? I don't follow the suggestion to test with an
empty string as the criteria, but back when the question was asked it would
have helped if you had said "Yes, I can create a query, but what do you mean
by testing each field?" Or maybe "No, I can't create a query." Any luck
with exporting as text, then importing?
It may help to create a new, blank database. From there, click File > Get
External Data > Import. Navigate to your current database, and select
everything you need from every tab. It should be clear enough when you get
to that dialog box.
I have been wondering from the start of this thread about the purpose and
structure of your database. In particular I am curious about your wish (as
I understand it) to combine several smaller tables into one big table. I
can say that if you append one table to another you will be adding new
records, not adding fields to existing records. I'm mentioning this as a
comment only. I do not understand your situation well enough to know if it
applies in your case.

 

[faxylady]

How do I check for zero length strings in the source data? There are no
required fields in either table. Thanks.

 

[faxylady]

How do I check for zero length strings in the source data? This was
suggested by Allen Browne. Thanks. I am going to try exporting and
importing shortly.