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
--
Brevity is the soul of wit.

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.

"Dave F" wrote:

> 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
> --
> Brevity is the soul of wit.

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

--
Brevity is the soul of wit.


"Dave F" wrote:

> 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
> --
> 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
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.

"Dave F" wrote:

> 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
>
> --
> Brevity is the soul of wit.
>
>
> "Dave F" wrote:
>
> > 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
> > --
> > Brevity is the soul of wit.

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.
--
Brevity is the soul of wit.


"Klatuu" wrote:

> 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.
>
> "Dave F" wrote:
>
> > 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
> >
> > --
> > Brevity is the soul of wit.
> >
> >
> > "Dave F" wrote:
> >
> > > 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
> > > --
> > > Brevity is the soul of wit.