Executing INSERT INTO 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?

I got this response on of the forums: "Perhaps a timing problem. The "parent" record gets added later or it gets "cached" on the SQL server and not "posted" until a bit later."

Is there a macro o vba code I could initiate to paus after the first query for a predifined amount of time? I am able to fire both the queries at a later time. I also see to it that the parent table is uploaded first and the the child has a FK for the parent table, so it cant be a issue with reference keys, because the record is saved locally without any problem

thanks
-Sohail

 

[vbaInet]

1. Check that on Insert the new Primary Keys are not being repeated.
2. Check the VALIDATION RULES you put on the fields in the table. Think about a scenario when it could be violated when inserting a record and compare it with the data you want to insert.
3. Check the DEFAULT VALUE property of the fields that you have this property set. If You have a Default Value set AND an INDEXED property of Yes(No Duplicates) then it will throw an error.

 

[lupis]

Thanks for your reply, the issues you mentioned, wouldnt they also be valid when saving the record locally, so,how come that tre records locally a saved with any problems?

 

[vbaInet]

It could also be timing issues considering you're in a multi-user environment (I'm guessing, since you're using SQL Server). Your query is correct as at when the query is run. So if you've got users adding/deleting after execution, you may end up with conflicts. If you want to ensure that users cannot get to the table during execution then you would want to set a lock on the table(s).

 

[lupis]

This was actually a first test run, with no other users online, all other queries, for example INSERT INTO from the SQL Server to local tables or update queries both ways dont have this issue.

Is there any way to paus the execution of the child table in my macro. The sequence that I am using is, that the parent table is first executed, then directly after the child table action is fired. I could have a seperate macro for the parent table and the child table, but that wouldnt be user friendely, currently they both reside in a single form as partent form and linked subform, and the export action is set to a button connected to a macro.

 

[vbaInet]

It's best you sort out the problem than cloud it. There seems to be a conversion problem from a certain data type to a data type not fully supported in Access. Have you tried casting all the fields to the relevant data types?

 

[lupis]

For the fields regarding the append query, the local table has all the fields set as Text, except the PK whick is a "AutoNumber" and one field which is set to "Yes/No" The same fields for the remote table are set to" nvarchar" instead of text and "int" for the PK instead of "AutoNumber" and "bit" instead of "Yes/No"

But I dont have this issues when doing a update query instead of a INSERT INTO query, at least for the text fields I have updated from the local to the remote table

 

[PNGBill]

Normally you wouldn't append an auto number. This field will be left to the receiving table to full - i think

If the Append to table has an Auto number then it surely will get all bitter and twisted when your append task is trying to ram a number into the field.

 

[Banana]

Wait, is the bit field set to NOT NULL? I've heard of problems when the bit fields is allowed to be null, a concept that Access' Yes/No field cannot handle (it can't be null).

 

[lupis]

Hi PNGBill,

If the Append to table has an Auto number then it surely will get all bitter and twisted when your append task is trying to ram a number into the field.

You were quite right , didnt at all think about the autonumber would be the issue, but quite logical ofcourse. But it will be a valueable lesson for the future, this is acutally my frist foray into access and sql.

-Sohail