append query errors

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave F - 22 Aug 2006 14:13 GMT

I'm running an append query to append data from one table to another.

These two tables have the exact same structure, data types, etc. However,
when I run the append query, about 5% of the records to be appended are not
appended. Access gives me a message that these records cannot be appended,
however, it does not create an error table as one would get if one merely
copied and pasted.

Copying and pasting is not an option as I am trying to append over 100,000
records while using a computer with limited memory. Is there a way to force
an append query to kick out its errors into an error table, such as that
which is created when a copy and paste operation generates errors?

Hope this is clear, thanks.

Dave

Signature

Brevity is the soul of wit.

Reply to this Message

Klatuu - 22 Aug 2006 14:29 GMT

The short answer is no.
What kind of errors are you getting? It may be you can use a select query
to determine which rows will cause the error and, perhaps, fix them before
you run the append query.

> I'm running an append query to append data from one table to another.
>
[quoted text clipped - 12 lines]
>
> Dave

Reply to this Message

Dave F - 22 Aug 2006 14:31 GMT

Here is the text of the error message I'm getting:

"Microsoft Access can't append all the records in the append query.

Microsoft Access set 0 field(s) to Null due to a type conversion failure,
and it didn't add 6565 record(s) to the table due to key violations, 0
record(s) due to lock violations, and 0 record(s) due to validation rule
violations."

So the issue seems to be "key violations" however as I say below, the
tables' structures are exactly the same.

Any clues here?

Thanks,

Dave

Signature

Brevity is the soul of wit.

> I'm running an append query to append data from one table to another.
>
[quoted text clipped - 12 lines]
>
> Dave

Reply to this Message

Klatuu - 22 Aug 2006 14:54 GMT

It does not matter that the table structures are identical. At least one of
your fields in the destination table is requiring unique values. The source
table contains records where the value of that field is the same as a record
already in the destination table.

You need to evaluate the situation and determine if, in fact, the values
must be unique. If it is not necessary for the values to be unique, then you
can remove that requirement from the field or fields; otherwise, you will
have to determine how to deal with the non unique records.

> Here is the text of the error message I'm getting:
>
[quoted text clipped - 30 lines]
> >
> > Dave

Reply to this Message

Dave F - 22 Aug 2006 14:57 GMT

Well I solved this problem by modifying the query to explicitly link a given
column in the first table to its corresponding column in the table I am
appending to. I ran the query after re-designing it in this manner, and no
errors were generated.

So, I'm not sure I understand why that would resolve the issue but it did.

Signature

Brevity is the soul of wit.

> It does not matter that the table structures are identical. At least one of
> your fields in the destination table is requiring unique values. The source
[quoted text clipped - 40 lines]
> > >
> > > Dave

Reply to this Message