Access Database Append Query- Dealing with Common Errors When Executin

1
Access Database Append Query- Dealing with Common
Errors When Executing Them
2
As a follow up on a recent blog post about
Microsoft Access database Append Queries, heres
a sub-note article that we will analyseand
investigatewhen errors can happen relating to a
failure of appending records in
MicrosoftAccess   A quick recap An Access Append
Query is used when a user amends a few records to
an existing table, typically from different
sources. Append query chooses new records from
different sources of data and copies them to the
table in database. It is useful in joining
numerous records at once and it also enables the
user to refine the selection with specific
criteria. Users can evaluate the selection before
replicating it to the existing table.  
3
Issues with Appending Records Access typically
shows a dialog box when the append query is run,
expressing the possible explanations for its
failure. Errors might be due to the mismatch of
field data types or key violations. Apart from
that, Lock and Validation Rule violations could
also be the reasons.   Lets discuss each issue
individually and how we can solve these errors.
  Type Conversion Failure This is the most
widely recognised error a user-experiences when
appending and it happens due to the field
data-type mismatch. Access commonly encounters
issues if the data is not properly formatted or
whenever there are missing field types. For
instance, if a user tries to import data in a
Numeric field such as date or age, and the data
contains records like Unknown, then Access
might display it as type conversion error. The
problem could also arise if the date is not in
the local regions standardised format (namely USA
versus UK/EU dates).
4
Key Violation If you are trying to append data to
fields that are part of the tables primary key,
you need to check the destination table first to
know if the primary key or any index has the No
Duplicates property set to the value Yes.
After that, inspect the data you are appending to
ensure it does not violate the destination
tables rules.   Lock Violation If the
destination table is being used by another user
or is open in Design view, this might result in
record locks. This scenario prevents the query
from appending records. Make sure no one is
accessing the database at the moment, so you can
proceed without any issue.   Validation Rule
Violation A fields Validation Rule can be set by
accessing the Fields tab and selecting Validation
Rule from the Field Validation group. If the rule
is violated, the append query will display an
error. You must also examine the destination
table for the given text fields and check
5
whether they have set No to Allow Zero
Length. Fields whoseRequired property is set
to Yes should not have a Null value.   In
case you often encounter all sorts of errors
while working with Microsoft Access databases, it
would be practical for you to invest in a
mdb/accdb tool to fix and avoid incidents of
potential data loss.
6
Contact us
Ben Beitler
ben_at_accessdatabasetutorial.com
(44) 7881
502400
United Kingdom
London
https//www.accessdatabasetutorial.com/