Append Query Errors

K

kayabob

I routinely have new data in a table that I will update to an existing table.
Certain fields in the existing table are required, so of course if data
from the new table is missing, the Append Query will fail. I would love it if
the error message would give me some information as to which field is missing
the data, so that I can trouble shoot just that one field. As it is, I may
have 20 fields out of 50 that are required, so tracking the problem can be a
mess. This is an example of the error message that i get:

Microsoft Access set (0) fields to Null due to a type conversion failure,
and it didnt add (0) fields to the table due to key violation errors, and (0)
errors due to lock violations, and (1) record due to validation rules
violations.

Of course the problem may be due to a data type mismatch too, but in any
case, the tracking of the field with the problem is the issue I'd like to
solve. Do you have any great ideas of how I can locate the problem fields.
I'm open to any alternate procedure!

Thanks
 
S

Sharkbyte

I would actually move farther back in the process, to when you are gathering
the new data, and add controls at this point to insure you are capturing all
of the required data, in the required formats.

Troubleshoot the data when it enters the database, not once you are trying
to manipulate it.

Sharkbyte
 
G

George Nicholson

I'm open to any alternate procedure!

I sometimes create a set of queries that check for problems. The idea being
I only want to see "bad" records returned. If the none of these queries
returns any records then everything is ok and the append can proceed.

This might require quite a few queries, depending on how your data is set
up. Rather than running each of these queries separately, you could set
these up so that each query only returns an ID num and a description of the
error that query is looking for. Create a UNION that joins these 2 fields
from all your check queries. Then you only need to run the 1 Union query to
get a printable list of all errors that you can work from while making any
needed corrections:

#150 Query1: Validation rule broken: Record contains Null(s) in required
field(s)
#163 Query1: Validation rule broken: Record contains Null(s) in required
field(s)
#180 Query2: Key violation: Unexpected value in field [DepartmentName]
#205 Query3: Key violation: Unexpected value in field [ActivityName]
#163 Query6: Type Mismatch: Record has non-Numeric value where numeric is
required
etc., etc.


HTH,
 
Top