can't append.

  • Thread starter Thread starter js
  • Start date Start date
J

js

Hi, I want to insert set of record into a table, bot got the error:
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 10000 record(s) to the table due to key violation, 0
record(s) due to lock violations, and 0 record(s) due to validation rue
violations.
how to find it out what field cause problem, difficult to me, because the
table about 30 fields. Please advice...
 
js said:
Hi, I want to insert set of record into a table, bot got the error:
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 10000 record(s) to the table due to key violation, 0
record(s) due to lock violations, and 0 record(s) due to validation rue
violations.
how to find it out what field cause problem, difficult to me, because the
table about 30 fields. Please advice...
Click to expand...

Missing some info:
To ignore the error(s) and run the query, click Yes.

Question: If I click Yes, what will happen? Thanks.
 
js said:
Missing some info:
To ignore the error(s) and run the query, click Yes.

Question: If I click Yes, what will happen? Thanks.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A "key violation" means one of: if there is a Primary Key, then there
are records that have duplicate values in the PK's column(s); if there
is a Unique index, then there are duplicate values in that index's
column(s).

If you click Yes, the records that don't violate the keys will be saved;
the records w/ violations will not be saved.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmf7ToechKqOuFEgEQKwqwCfYfRKTHV40lJ8ZbVd8jywW1p77vAAnR2o
bWED2+sIyuGsLCdlctEd92mS
=FZON
-----END PGP SIGNATURE-----
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can run the Find Duplicate Records wizard. It's under the Query's
tab. Click the New button & select the "Find Duplicates Query Wizard"
item in the selection list. Then follow the instructions. You have to
know the names of the columns that are producing the duplicates. These
will be the Primary Key and/or Unique index columns.

After the query runs it will show a list of duplicates. You will have
to decide how to make each record unique, or, if you want to delete
redundant records.

Search the Microsoft Knowledge Base (under Access) for possible
solutions. Also, search Google.com Groups (Advanced Search) under the
newsgroup comp.databases.ms-access for possible solutions. Search for
"Duplicates" and "Removing Duplicates."
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmgm/oechKqOuFEgEQL81gCdFpHZrTa0jt0aoPBNhSYsQY75w9oAnREX
BYZNPwen6RfdQcFNsIdxd8J7
=nTQs
-----END PGP SIGNATURE-----
 
Thanks MGFoster,
I found key violation records.

new problem:
Microsoft Access set 51 field(s) to Null due to a type conversion failure,
and it didn't add 0 record(s) to the table due the key violations, 0
record(s) due to lock violations, and 0 record(s) due to validation rule
violations.
Do you want o run the action query anyway?

how to find out which fields cause problem? Thanks...
 
js said:
Thanks MGFoster,
I found key violation records.

new problem:
Microsoft Access set 51 field(s) to Null due to a type conversion failure,
Click to expand...
how to find out which fields cause problem? Thanks...
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Compare the original source to the imported table. When the cell
(field) is empty where you expected a value, and the value is in the
original data, then that is where the type conversion failure occurred.

If you're importing data from a text file set up an import specification
that defines each column's data type. Use the File > Get External Data
Import wizard. When the Wizard dialog box appears click the Advanced
Click to expand...
button to get to the column data type definitions.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmkZ9IechKqOuFEgEQI7egCcCBF2UnmAoUgSzf6N1J+aKbdWanAAoJKt
F1hIds3HsgRRzYtbvuWPWEuP
=Ujjl
-----END PGP SIGNATURE-----
 
Thanks MGFoster,
Compare the original source to the imported table. When the cell
(field) is empty where you expected a value, and the value is in the
original data, then that is where the type conversion failure occurred.
Click to expand...

Still hard to find it out, the imported table has lots of fields and the
original source set is about 30000 records. Is it a quick way to indicate
where are the problem.
 
Back
Top