>>I see a few minor problems with the way you retrieve identity - I suggest to remove the line with select @@identity and add OUTPUT clause to the first insert statement, so you will have one less statement to run.
>>
>>Now, I also suggest to use text to lcSQL noshow instead of the one line - the query will be easier to read.
>>Finally, if the above suggestions will not help much, as an experiment, you can use textmerge noshow in the above and use the actual values, e.g.
>>
>>text to lcSQL textmerge noshow
>>insert into myAccessTable (field1, field2, field3)
>>values (<<Val1>>, '<<Val2>>', '<<Val3>>')
>>endtext
>>
>>The above is not recommended approach, I suggest it just to try.
>
>Well, OK, I can try that, but note that the @@identity line is working fine; it's the INSERT for the second table that fails. Text To and TextMerge may make it look prettier, but will it change the result?
I know it works, but if you can get info in one statement, don't you think it's better to use 1 statement instead of 2? Not to mention that @@Identity is dangerous approach in case the table has a trigger which may insert data into another table with identity column.
And yes, the textmerge and parameters will not change the result. I do, however, recommend to try without using parameters or try to change one parameter at a time to literal value until we find the culprit.
If it's not broken, fix it until it is.
My Blog