Re: Records will not append to another table due to Key violations
- From: "BruceM" <bamoob@xxxxxxxxxxxxxxxxxx>
- Date: Tue, 18 Jul 2006 08:00:53 -0400
Perhaps you have tried every suggestion, but there is no way for anybody
else to know that unless you mention it. I was thinking specifically about
the suggestion to try creating a *new* table with all text fields plus an
autonumber field. Even though your table may be exactly that, something
seems to have changed, so starting over could help. Also, what was the
result of checking the Indexed property? Can you create a query using the
data (this was also asked)? I don't follow the suggestion to test with an
empty string as the criteria, but back when the question was asked it would
have helped if you had said "Yes, I can create a query, but what do you mean
by testing each field?" Or maybe "No, I can't create a query." Any luck
with exporting as text, then importing?
It may help to create a new, blank database. From there, click File > Get
External Data > Import. Navigate to your current database, and select
everything you need from every tab. It should be clear enough when you get
to that dialog box.
I have been wondering from the start of this thread about the purpose and
structure of your database. In particular I am curious about your wish (as
I understand it) to combine several smaller tables into one big table. I
can say that if you append one table to another you will be adding new
records, not adding fields to existing records. I'm mentioning this as a
comment only. I do not understand your situation well enough to know if it
applies in your case.
"faxylady" <faxylady@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2382AB09-091F-4ABB-89C8-D3C7919CC8FB@xxxxxxxxxxxxxxxx
I don't quite understand what you mean by "try every suggestion." I
already
have, I think. Unless you mean that I should check the properties of all
the
text fields--I haven't done that yet. Perhaps, I could send you a copy by
CD
of this database. I would be willing to pay, but not at the moment. I am
in
financial difficulty right now. Over 2000 fax nos are not being accessed
due
to these difficulties. One of the MVPs mentioned something about strings
earlier. I do not know how to follow up on that. Please explain. Thanks
for your help.
"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" <faxylady@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BD7EC046-9F9A-4D67-B944-547D6445A740@xxxxxxxxxxxxxxxx
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" <faxylady@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D7D1D100-AE2B-4C91-9E70-4FE14357EDAF@xxxxxxxxxxxxxxxx
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" <faxylady@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:805F574D-47AB-40E3-B2F5-B77774C64837@xxxxxxxxxxxxxxxx
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" <faxylady@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2FAB9547-0FAB-4ACC-837D-698622399237@xxxxxxxxxxxxxxxx
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" <faxylady@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:455B5465-11D8-4740-B401-DB3BD4EF3EEB@xxxxxxxxxxxxxxxx
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.
.
- Prev by Date: Re: Many to Many (AGAIN!)
- Next by Date: Re: Office Space Rentals
- Previous by thread: Re: Many to Many (AGAIN!)
- Next by thread: Re: Records will not append to another table due to Key violations
- Index(es):
Relevant Pages
|
