Re: Figuring out what field is causing key violation on append

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


"JN" <me@xxxxxxxx> wrote in message
news:eY9xC3dlJHA.3760@xxxxxxxxxxxxxxxxxxxxxxx
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" <nnn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:efT2ZE$kJHA.2064@xxxxxxxxxxxxxxxxxxxxxxx
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" <me@xxxxxxxx> wrote in message
news:uPJw7s9kJHA.1248@xxxxxxxxxxxxxxxxxxxxxxx
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.



"KARL DEWEY" <KARLDEWEY@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:911B1DD7-1D62-4FA1-B33F-A255867F5638@xxxxxxxxxxxxxxxx
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.

--
KARL DEWEY
Build a little - Test a little


"JN" wrote:

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?

















.

Relevant Pages

  • Re: Replacing Nulls with zeros
    ... The Nulls are created in my query because there are no values for certain ... entry for Project REWORK so when I run this crosstab query it places a null ... Dave Hargis, Microsoft Access MVP ...
    (microsoft.public.access.queries)
  • Re: Running select and append queries to find data
    ... I'm not sure how you expect users to select/filter records and why they ... would append and keep duplicates. ... > the select query that hopefully will run earlier in the macro. ... >> MS Access MVP ...
    (microsoft.public.access.tablesdbdesign)
  • Re: you do not have the necessary permissions...
    ... there is some problem with the append query you are having an issue with. ... >>MS Access MVP ... >>Access Security: www.ltcomputerdesigns.com/Security.htm ...
    (microsoft.public.access.security)
  • Re: adding information/keeping history of user name and time stamp
    ... Try changing that query to something like this: ... This assumes that you have a control on your form that has FL.ID as it's ... >i have just created a simple append query.... ... >> MS Access MVP ...
    (microsoft.public.access.tablesdbdesign)
  • Re: limited number of db in a project?
    ... MS Access MVP ... It seems like if I run a query with a large data result the error occures ... the query I can't see that there is anything specvial about the append ... "Duane Hookom" wrote: ...
    (microsoft.public.access.queries)