Records will not append to another table due to Key violations and

You could put = "" in the criteria row for a field in the query design grid,
then see if any records are returned when you run the query. I think this
is what Allen meant.

"faxylady" wrote in message
...
How do I check for zero length strings in the source data? This was
suggested by Allen Browne. Thanks. I am going to try exporting and
importing shortly.

"BruceM" wrote:

Some information is missing from your description, I think. A couple of
things: Check the field's Indexed property jsut to be sure there isn't
something that disallows duplicates. Also, try every suggestion, and
describe the results. Did you try the table with all text fields, as
Allen
suggested? I know you said your table is all text fields, but why not
give
it a shot?
One more thought: Export the data as a text file, then use File Get
External Data Import to pull the data into a new table.
"faxylady" wrote in message
...
Sorry, the Make-Table query did not work. I still cannot append nearly
half
the records to the other table due to validation rule violations or key
violations. No validation rules or autonumbers are in either table.

"Allen Browne" wrote:

Try using a Make Table query to get the data into a new table in
Access.
Alternatively, create a table with all Text fields, plus one
AutoNumber
field as the last fields, and use an Append query to populate this
table.

Once you have the data in this temporary table, you can query, it,
examine
it, typecast it, clean it up, and so on, until you sort out all the
issues.
Then you can append it to your real table once all the issues are
solved.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"faxylady" wrote in message
news The source data originally was automatically transferred from a
Cardscan
file
into Outlook. From Outlook, it was exported into Access. I have
checked
all
the fields in the source table and the table being appended to and
cannot
see
any required fields or fields with validation rules.

The reason this is important is because some of the tables I am
trying
to
append have 8-900 records and are only allowing 2-300 to append to
the
larger
table. Thanks for your continued help.

"Allen Browne" wrote:

Lower pane of table design adequately checks for Required (assuming
there
is
no Validation Rule on the fields or on the table itself.)

What is the source data? Can you create a query that uses it? If
so,
you
could test each field that could be interpreted as text (i.e. each
field
that left-aligns when you view the query output). The criteria
would
be
an
empty string, i.e.:
""

"faxylady" wrote in message
...
As far as I know the Required property in all fields in both
tables
is
set
to
no. Perhaps there is something further I need to examine that I
am
not
aware
of rather than just the lower portion of design view? How do I
check
for
zero-length-strings inthe source data? Thanks for your help.

"Allen Browne" wrote:

Did you examine the Required property of the fields?

Did you check for zero-length-strings in the source data?

"faxylady" wrote in message
...
All the fields are text data types, there are no primary keys
in
either
table. I did review your article. Thanks.

"Allen Browne" wrote:

See:
Why can't I append some records? Trouble-shooting imports
at:
http://allenbrowne.com/casu-19.html

"faxylady" wrote in
message
...
I have several tables I am trying to append to a
comprehensive
table
called
the BIGTable. I am having trouble with my append query
giving
me
an
error
message saying it could not append x amount of records due
to
Key
violations
and validation rule violations. None of the fields in
either
table
are
indexed or have validation rules. Some of the tables have
fewer
fields
that
the larger table but that has not stopped other tables from
appending.

Please explain the problem and how to fix it. Thanks.

My 3rd reply in this thread explained what criteria to use to check for
zero-length strings.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"faxylady" wrote in message
...
How do I check for zero length strings in the source data? There are no
required fields in either table. Thanks.

At this point, I want to say thanks to Allen and all the MVPs and others who
have assisted me in this matter. I have learned a lot and appreciate all
your time and efforts. It is time now to close this thread. Your help has
been wonderful.

"Allen Browne" wrote:

Something else is wrong, then.

Sorry: I'm out of suggestions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"faxylady" wrote in message
...
Sorry, the Make-Table query did not work. I still cannot append nearly
half
the records to the other table due to validation rule violations or key
violations. No validation rules or autonumbers are in either table.

"Allen Browne" wrote:

Try using a Make Table query to get the data into a new table in Access.
Alternatively, create a table with all Text fields, plus one AutoNumber
field as the last fields, and use an Append query to populate this table.

Once you have the data in this temporary table, you can query, it,
examine
it, typecast it, clean it up, and so on, until you sort out all the
issues.
Then you can append it to your real table once all the issues are solved.

"faxylady" wrote in message
news The source data originally was automatically transferred from a
Cardscan
file
into Outlook. From Outlook, it was exported into Access. I have
checked
all
the fields in the source table and the table being appended to and
cannot
see
any required fields or fields with validation rules.

The reason this is important is because some of the tables I am trying
to
append have 8-900 records and are only allowing 2-300 to append to the
larger
table. Thanks for your continued help.

"Allen Browne" wrote:

Lower pane of table design adequately checks for Required (assuming
there
is
no Validation Rule on the fields or on the table itself.)

What is the source data? Can you create a query that uses it? If so,
you
could test each field that could be interpreted as text (i.e. each
field
that left-aligns when you view the query output). The criteria would
be
an
empty string, i.e.:
""

"faxylady" wrote in message
...
As far as I know the Required property in all fields in both tables
is
set
to
no. Perhaps there is something further I need to examine that I am
not
aware
of rather than just the lower portion of design view? How do I
check
for
zero-length-strings inthe source data? Thanks for your help.

"Allen Browne" wrote:

Did you examine the Required property of the fields?

Did you check for zero-length-strings in the source data?

"faxylady" wrote in message
...
All the fields are text data types, there are no primary keys in
either
table. I did review your article. Thanks.

"Allen Browne" wrote:

See:
Why can't I append some records? Trouble-shooting imports
at:
http://allenbrowne.com/casu-19.html

"faxylady" wrote in message
...
I have several tables I am trying to append to a comprehensive
table
called
the BIGTable. I am having trouble with my append query giving
me
an
error
message saying it could not append x amount of records due to
Key
violations
and validation rule violations. None of the fields in either
table
are
indexed or have validation rules. Some of the tables have
fewer
fields
that
the larger table but that has not stopped other tables from
appending.

Please explain the problem and how to fix it. Thanks.