|
Subject:
|
Append Query not appending
|
|
Posted By:
|
Smythe
|
Post Date:
|
5/29/2006 7:55:51 AM
|
Good Morning guys,
Hope all you Americans are enjoying your Memorial Day..
Well anyway my problem that's arised is the following.
I'm using Access 2003, and with my append query I'm writing it's not exactly appending.
What I'm doing is taking information from a table called Service Hours, and trying to append it to a linked table with the exact same information in it. So I know it's got nothing to do with the information on either table not matching up.
This is the SQL statement I have for the table.
INSERT INTO [Service Hours2] ( ServiceRecordID, EmployeeID, [Date], [Travel Start], [Plant In], [Plant Out], [Travel End] )
SELECT [Service Hours].ServiceRecordID, [Service Hours].EmployeeID, [Service Hours].Date, [Service Hours].[Travel Start], [Service Hours].[Plant In], [Service Hours].[Plant Out], [Service Hours].[Travel End]
FROM [Service Hours]
And what it says is..
It cannot append all the records it's set 0 field(s) to Null due to a type conversion failure and didn't add 1 record(s) to the table due to key violations, 0 record(s) due to lock violations and 0 record(s) due to validation rule violations.
Now before ya ask..yes all the data types are the same, there shouldn't be any difference between any of the information. None of the records I'm trying to append are autonumbers or a Primary Key so I don't understand where exactly things are going wrong. So if anyone can help out that'd be great.
Thanks in advance.
|
|
Reply By:
|
Smythe
|
Reply Date:
|
5/29/2006 2:39:34 PM
|
Guess I should also add that we're trying to do it so that when you update you're only putting in the newest piece of data..
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
5/30/2006 6:47:35 AM
|
I'll start with the obvious:
Are any of the fields in the reciving table autonumber? It appears not.
Are they then number fields receiving the ID fields from the other table?
It sounds like you have only 1 record that is causing you problems here. Are is your test just with one record?
I would look for that one record that is causing problems. It sounds like you have created relationships off of this appended to table to the other tables (which is really necessary except for data integrity) and that it is one of those relationships causing problems. Try removing referential integrity from one of the relationships at a time, and that will narrow down the column causing trouble.
It sounds like a little detective work finding the abberant record. The append query seems to be working but for that one record.
Does this help?
mmcdonal
|
|
Reply By:
|
Smythe
|
Reply Date:
|
5/30/2006 7:40:46 AM
|
Hopefully I'll give it a try and post if it works.
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
5/30/2006 7:55:57 AM
|
Sorry I fat fingered this thing.
"Are is your test just with one record?" = Is your test with just one record?
"(which is really necessary except for data integrity)" = (which is NOT really necessary except for data integrity) But that is not true here since you can create integrity with your follow up queries.
mmcdonal
|
|
Reply By:
|
Smythe
|
Reply Date:
|
5/30/2006 7:59:41 AM
|
well I'm testing with 1-3 records..
removed the referential integrity from the tables, and still getting the same error..
all the data being passed is the same data that is in the other table so I know it's nothing on that end.
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
5/30/2006 8:26:50 AM
|
Well according to the error message, there is only one record that is offensive. Make sure there is no duplicate data in the key columns? Also, compare the table designs side by side to make sure you didn't miss anything.
mmcdonal
|
|
Reply By:
|
Smythe
|
Reply Date:
|
5/30/2006 8:33:35 AM
|
Got it...
just had to remove a relationship to a different table..
thanks for the idea involving relationships seems that was the key there to getting this to work :)
|
|
Reply By:
|
DeMoNN
|
Reply Date:
|
10/10/2006 1:16:59 PM
|
deffinately something to do with relationships.
i got rid of enforce integrity and it saves now
but that makes me have to manually delete all the records instead of using integrity's delete
:(
I was here but now I'm gone.
I leave this messege to be carried on.
Whoever knew me, knew me well.
Whoever didn't, go to hell.
|
