Key Violations in Append Query (Office 2000, Win X

Topic

New Reply

I am trying to run an append query, and I am getting a key violation message for all the records I am attempting to append.

I did a search and have thoroughly read post 224225 (and I think I understand it) but I am still getting the error message.

I am trying to append data from T_Results to T_ResultDetails.
I want to append:
[T_Results.NewID] to [T_ResultDetails.SurveyID]
[T_Results.Wages] to [T_ResultDetails.Rating]

My table T_ResultDetails has five fields and there is currently no data in it.
[DetailID] is an autonumber primary key for the table.
[SurveyID] is a number field, and is linked one-to-many with referential integrity to [T_Results.NewID]. In T_Results, the field [NewID] is an autonumber primary key.
[Section] is a text field not linked to anything.
[Item] is a text field linked one-to-many with referential integrity to another table’s text primary key. I have [T_ResultDetails.Item] set with a default of the appropriate item primary key (in this case, 1A).
[Rating] is a text field – as is [T_Results.Wages] which I am attempting to append to it.
In T_ResultDetails, only the primary key field is set for no duplicates, although [SurveyID] is indexed with duplicates okay.

I have tried deleting table relationships (and when that didn’t help I linked them back up), I added the default value [Item] so it wouldn’t create blank fields, I have checked my data types and unless I’m missig something they appear to be compatible, I compacted the database, and I am now fresh out of ideas.

(in case anyone wonders why I’m trying to append data from one table to another that is linked to it, it’s because I’m trying to move most but not all of the data from a table that was set up as a flat-file for easy data-entry by volunteers into a relational format that I can actually query without turning myself inside out . Most of the tables and queries in this database were imported from one I built three years ago which worked fine. I have made only minor changes, and I can’t see that any of them would affect what I am trying to do here).

Can anyone suggest something else I can try?

Thank you,
-cynthia

Reply | Quote

Replies

One possibility that comes to mind is that you are trying to append a value to the [DetailID] field – that will always cause a failure. Otherwise I would suspect the referential integrity links – did you remove all of them, or just one at a time?

Reply | Quote

I have only the two fields in my append query – [NewID] and [Wages] going into [SurveyID] and [Rating] respectively (I have in the past made the error of trying to append into an autonumber field. Oops! but not this time).

On the relationships – there are only two at the table level in this database. I first deleted the one between T_Labels (which links to [T_ResultDetails.Item] ) and T_ResultDetails, then when that didn’t help I also deleted the one between T_ResultDetails and T_Results. That didn’t help either, so then I added that one back in (and at that point set the default in [T_ResultDetails.Item] to “1A”) and then added back the one between T_Labels and T_ResultDetails.

I was leery, after reading the post mentioned in my original post on this thread, of leaving them off because I didn’t want to violate the referential integrity when I turned them back on. But I did want to see if it would work with them off – it didn’t.

I have also tried this with substituting various other fields in the query for [Wages], as I’ll have to append about fifteen of them once I get this thing working. But none of them work.

I should perhaps mention that the original database was created in Access97 and I imported the tables and queries into Access2000 – but it also didn’t work when I created a totally fresh query in Access2000 (I did NOT try creating totally fresh tables but I had earlier today appended data from five different tables to T_Results without difficulty – have not yet successfully appended anything to T_ResultDetails)

-cynthia

Reply | Quote

Can you post a cutdown version of your database.

Reply | Quote

Looks like you were posting that request just about the same time I was posting the (albeit weird) explanation of having fixed it. Wendell’s explanation to my fix seems plausible to me. If your curiousity is totally whetted and you would like to examine a cutdown version of the database, I’ll be happy to make one and post – but if you are on to other things now that this dilemma appears to be solved we’ll let it go. Just let me know.

-cynthia

Reply | Quote

Let’s let it go, what’s the point of chasing aliens ……………..

Reply | Quote

Agreed!

-c

Reply | Quote

Agreed!

-c

Reply | Quote

Let’s let it go, what’s the point of chasing aliens ……………..

Reply | Quote

Looks like you were posting that request just about the same time I was posting the (albeit weird) explanation of having fixed it. Wendell’s explanation to my fix seems plausible to me. If your curiousity is totally whetted and you would like to examine a cutdown version of the database, I’ll be happy to make one and post – but if you are on to other things now that this dilemma appears to be solved we’ll let it go. Just let me know.

-cynthia

Reply | Quote

Can you post a cutdown version of your database.

Reply | Quote

I have only the two fields in my append query – [NewID] and [Wages] going into [SurveyID] and [Rating] respectively (I have in the past made the error of trying to append into an autonumber field. Oops! but not this time).

On the relationships – there are only two at the table level in this database. I first deleted the one between T_Labels (which links to [T_ResultDetails.Item] ) and T_ResultDetails, then when that didn’t help I also deleted the one between T_ResultDetails and T_Results. That didn’t help either, so then I added that one back in (and at that point set the default in [T_ResultDetails.Item] to “1A”) and then added back the one between T_Labels and T_ResultDetails.

I was leery, after reading the post mentioned in my original post on this thread, of leaving them off because I didn’t want to violate the referential integrity when I turned them back on. But I did want to see if it would work with them off – it didn’t.

I have also tried this with substituting various other fields in the query for [Wages], as I’ll have to append about fifteen of them once I get this thing working. But none of them work.

I should perhaps mention that the original database was created in Access97 and I imported the tables and queries into Access2000 – but it also didn’t work when I created a totally fresh query in Access2000 (I did NOT try creating totally fresh tables but I had earlier today appended data from five different tables to T_Results without difficulty – have not yet successfully appended anything to T_ResultDetails)

-cynthia

Reply | Quote

I can see no logic to this – but it works now, and here is what I did:
I made a backup copy of the database (and I tried to run the query in the backup – it didn’t work).
Then in the original database I renamed the two tables in question and copied them, giving each copy the original name. So now, I have a table called T_Results which is an exact copy (cut and paste) of what is now T_ResultsOriginal.
Then I made a new query. Just like the first one. I ran it. It worked!

Then I relinked the relationships between the new tables (I had to delete the relationships when I renamed the tables). No problems there, either.

I compared the two queries and they appear to be identical (there is only one table and only two fields in them – can I really be missing something?). I ran the query on two more fields (by substituting them for [Wages] in the query). It still worked. Then, I ran the original append query (using a different field since I had already appended [Wages]) and IT WORKED TOO. I had previously tried it with at least three different fields.

So why did it work with exact copies of the tables but wouldn’t work with the original tables? I have a feeling there is not a logical answer to this – but at least now I can get on with the project.

-cynthia

Reply | Quote

One possibility is that there is a corrupt index on your original table that causes things to choke when you try to run the append. Creating a new table that is a copy recreated the index, and then things worked. That’s about the only logical explanation I can come up with. Otherwise it’s probably s!

Reply | Quote

One possibility is that there is a corrupt index on your original table that causes things to choke when you try to run the append. Creating a new table that is a copy recreated the index, and then things worked. That’s about the only logical explanation I can come up with. Otherwise it’s probably s!

Reply | Quote

I can see no logic to this – but it works now, and here is what I did:
I made a backup copy of the database (and I tried to run the query in the backup – it didn’t work).
Then in the original database I renamed the two tables in question and copied them, giving each copy the original name. So now, I have a table called T_Results which is an exact copy (cut and paste) of what is now T_ResultsOriginal.
Then I made a new query. Just like the first one. I ran it. It worked!

Then I relinked the relationships between the new tables (I had to delete the relationships when I renamed the tables). No problems there, either.

I compared the two queries and they appear to be identical (there is only one table and only two fields in them – can I really be missing something?). I ran the query on two more fields (by substituting them for [Wages] in the query). It still worked. Then, I ran the original append query (using a different field since I had already appended [Wages]) and IT WORKED TOO. I had previously tried it with at least three different fields.

So why did it work with exact copies of the tables but wouldn’t work with the original tables? I have a feeling there is not a logical answer to this – but at least now I can get on with the project.

-cynthia

Reply | Quote