The MS Access Error Code '3086' issue (1 Viewer)

The MS Access Error Code '3086' issue.
Platform Windows 7 Professional (applies to Windows XP as well)
MS Access version 2010 (applies to version 2007 as well)
Error Code Returned: 3086 on delete statement only
This sometimes works fine. It sometimes fails.

DELETE DISTINCTROW LINKED_TABLE.*, [tblItem1].[item2], [LINKED_TABLE].[item3]
FROM LINKED_TABLE INNER JOIN tblItem1 ON LINKED_TABLE.item1=tblItem1.item1
WHERE ((tblItem1.item2 = True)
and (LINKED_TABLE.item3 = Forms!frmItem3!DateField));
Only solution I find is moving INNER JOIN clause to new line (HEX (0D 0A)) as follows:
DELETE DISTINCTROW LINKED_TABLE.*, [tblItem1].[item2], [LINKED_TABLE].[item3]
FROM LINKED_TABLE
INNER JOIN tblItem1 ON LINKED_TABLE.item1=tblItem1.item1
WHERE ((tblItem1.item2 = True)
and (LINKED_TABLE.item3 = Forms!frmItem3!DateField));
Then I SAVED the query and I ran the query and it works fine.
After I close the MS Access apps, when I re-launched the access, the syntax shows the old format (new line shifted back to old line as HEX(0D 0A) disappeared) as follows:
DELETE DISTINCTROW LINKED_TABLE.*, [tblItem1].[item2], [LINKED_TABLE].[item3]
FROM LINKED_TABLE INNER JOIN tblItem1 ON LINKED_TABLE.item1=tblItem1.item1
WHERE ((tblItem1.item2 = True)
and (LINKED_TABLE.item3 = Forms!frmItem3!DateField));
Why does the internal SQL syntax change at each time the MS Access is launched?

You may want to just execute the sql statement with code.

Where did you find the syntax for the DELETE query you are using?
What exactly are you trying to delete?
What is the purpose of including these fields on the first line of your query?

[tblItem1].[item2], [LINKED_TABLE].[item3]

Click to expand...

jdraw said:

Where did you find the syntax for the DELETE query you are using?
What exactly are you trying to delete?
What is the purpose of including these fields on the first line of your query?

Click to expand...

The delete query is in vba code.
We are trying to delete the row from linked table AND flag at access table that matches the month date at linked table. The flag is set true based on user's month selection. When the deletion is made, the flag is to be set false or deleted.

KenHigg said:

You may want to just execute the sql statement with code.

Click to expand...

Not sure I understand you. The sql statement is in query executed in vba code.

What jdraw was getting at is that your SQL does not make sense. You cannot delete a column. You can only delete full records, or update a value in a column.

Try to build a select query step by step in the query designer. Once that works, and selects records to be deleted, convert it to a delete query. And then you have SQL you can copy from the SQL view of the query designer. AS to updates in a linked table - that is a separate action query.

I was saying to create a SQL string but it seems they have spotted some other issue with you logic

Interesting. The code or sql statement is fine. Resolution I figured was deleting the linked table and relink table with good index. The reason it failed was due to undefined or lost index. Thank you for all your help!

Very interesting. Your "fine" SQL is just way beyond any manuals. Easter eggs from Microsoft ?

Hi spike-just signed in- have been away all day. Yes, I agree, it must be some divined creation within M$oft the poster has found. The syntax defies logic - at least as we know and use it. Anyway, seems the problem has been resolved --fine.

If you want Access to stop reformatting your SQL in querydefs, switch to SQL view and save the query. Then never save it from QBE view again. You should be able to switch back to QBE view and make modifications but go back to SQL view before saving and closing. It is the QBE view that rewrites the SQL string.