Figuring out what field is causing key violation on append

[JN]

I have a new version of a program. It has pretty much the same table
design, except for a couple of extra fields and a few fields got an
increased in size.

I want to just copy my data from my old table into the table in the new DB
(SQL 2000 back end) however when I do I get the error:

Microsoft Office Access can't append all the records in the append query
Microsof Office set......and did not add XX records to the table due to key
violations, .......(all other than key violations is 0)

This table has about 75 fields. A lot of them in the previous version were
set to Required = No and other minor changes. It would take for every to
find the right combination so is there a way when I run an append query to
see what field(s) is causing the problem?

 

[KARL DEWEY]

I would start by letting it append those that it will and then run an unmatch
query to see what was not appended.
Use input table left join to new table with criteria for new table join
field as 'Is Null'.

First look for nulls.
Then duplicate records.

 

[JN]

The problem is NO records append. Even when I just try to append a single
record it fails with the error show.

It is definetly not a restriction on adding records because I can copy a
record from the table to a new record (just changing the PK). It has to be
that one of the fields has some type of restriction that I just can seem to
find like no Nulls or Required or something I am just missing in the 75
fields.

 

[Jeanette Cunningham]

Hi JN,
You can use a process of elimination.
First try the query with just one field in the append query.
If that is successful, add one more field to the query.

You can also use Debug.Print to put the query string into the immediate
window.
By examining the string, you can often get an idea of what is wrong.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

Hi JN,
You can use a process of elimination.
First try the query with just one field in the append query.
If that is successful, add one more field to the query.

You can also use Debug.Print to put the query string into the immediate
window.
By examining the string, you can often get an idea of what is wrong.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[JN]

I figured out what was wrong. The vendor for some reason changed a lot of
the fields to not allow nulls any longer. What a PIA because there are
currently about 20 fields that are optional now. They appear to put "" in
the field in the program now.

OK, so I guess my most difficult choice is create an append query that has:

=IIF(IsNull([MyField1]), "", [MyField1])

as the source. But this is for 20-30 fields so is there some way to
autoconvert nulls to "" for text types?

 

[Jeanette Cunningham]

You can use Nz([MyField1],"")
Nz converts nulls to whatever you put as the second argument.
Check VBA help on the Nz function.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I figured out what was wrong. The vendor for some reason changed a lot of
the fields to not allow nulls any longer. What a PIA because there are
currently about 20 fields that are optional now. They appear to put "" in
the field in the program now.

OK, so I guess my most difficult choice is create an append query that
has:

=IIF(IsNull([MyField1]), "", [MyField1])

as the source. But this is for 20-30 fields so is there some way to
autoconvert nulls to "" for text types?

Hi JN,
You can use a process of elimination.
First try the query with just one field in the append query.
If that is successful, add one more field to the query.

You can also use Debug.Print to put the query string into the immediate
window.
By examining the string, you can often get an idea of what is wrong.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia