INSERT INTO Query Error

[lupis]

I am creating a query to find unmatched rows between a local access table and an online MS SQL Server table based on the PK field ID. This is working without any flaws. My problems start when I run a append query with INSERT INTO based on the result of the former. I get the following error:

"Database set 0 field(s) to null due to a type conversion failure and it didnt add 1 record(s) to the table due to key violations, 0 record(s)due to lock violations, and 0 record(s) due to validations rule violations...."
I have enclosed an screen dump of the error

The strange thing is that query fires normally after a while!! My other append and update queries dont have this problem, I have also a append query going in the oppisite direction, from the online SQL table to the local Access table, and this error does not show up in that case.

I have two tables for the export to the online tables, one is the parent table and a child table, I see to it that the parent table gets upploaded before the child table, I do this in a macro, could it be a problem with the parent table not being able to uploaded before the child table event fires? Is it poosible to have som time delay in between? if this could be the issue?

thanks
-Sohail

[jimatqsi]

Why not just add a join to the parent table in your append so if the key is not in the parent table an attempt to put it in the child table cannot be made.

Jim

[lupis]

So you think the error generated could be due the parent table not getting uppdate before the child table action event fires?

[NeoPa]

Have you checked to see which item is failing. It will either be a key that is included twice in the query, or a key that already exists in the destination table.

The SQL may be helpful too. It's hard to work blind, and the SQL is likely to be required to get an understanding of what's wrong.

[lupis]

Hi NeoPa

"It will either be a key that is included twice in the query, or a key that already exists in the destination table."

I create first a query to see what records are in the local table and not on the remote table, I then use this query as a basis for the actual INSERT INTO query for the remote table, so a PK key does not exist previously for that record. The PK is autoincremented, inexed and does not accept duplicates.

-Sohail

[NeoPa]

That may be true, but we are not only talking of PKs here. Any index could cause this problem. In fact one has. Without question. That's what the message is telling you.

[lupis]

So this would still commmit to the local Access table and not the remote SQL Server table?

[NeoPa]

I don't understand the question. The record that fails would go nowhere.

As I suggested in post #4 you need to determine which record is failing. From there you may find you understand why it does so and can then decide how you think it should be working and make the relevant changes.

[lupis]

@NeoPa
Well, the record does go somewhere,it is actually saved locally but not remotely, and I am just handling two records, one for the parent table, and after that the child table so it has a valid FK of the parent table.

[lupis]

@NeoPa
Well, I got it solved finally, it was not a timing issue as I suspected. I got some good advice not to include the PK in my append query, thats what was causing the problem.

-Sohail

[NeoPa]

I'm pleased you resolved your problem. Obviously that would have been hard for anyone here to know in the absence of the SQL I suggested you showed in my post #4. Frankly, if that's the issue then I'm surprised you got any records to add at all, but it's hard to know what's really going on from a distance, without the information to work from.

[jimatqsi]

Yes, sounds like the PK is an auto-incrementing numeric, otherwise leaving it out of the append would fail completely. But I didn't realize you could specify that value sometimes.

Jim

[NeoPa]

I'm pretty sure you can't Jim. Sometimes we don't get all the information posted, for various reasons.