Error 3086 - Could not delete from specified tables

[John_G]

I am getting the above error in VBA when I try to execute this statement:

currentdb.Execute "Delete from [Job_Statement_with_MOSID] WHERE [Duty_area_Occupation] = 199 AND [Statement_ID] = 122839 AND [Job_Statement_ID] <> -77228"

where [Job_Statement_with_MOSID] is an updateable query, with three tables joined to get at one field value ([duty_Area_Occupation]).



The issue is not related to the WHERE, because I get the same error without a WHERE clause.

It is not related to an open form, because I can get the same error from the immediate pane (though the error first showed up in a form) with no forms open.

Now the strange part - if I open the query directly, I can delete the required record with no problem (and it does delete from the correct table).

Do any of you real experts have any ideas? The Help for that error, doesn't help!

Thanks

John

[help_me_with_access]

John,

you are aware aren't you, that office software has many things wrong with it. even undiscovered issues? it's not the best...it's not even close. many "experts" out there will tell you, when they run into a scenario that doesn't seem to have an answer, that access is kind of a piece of junk. it's not, as every software has ups and downs, but dare I say again that office software is not considered HIGH-END.

try compacting. restarting your machine? any open connections like odbc not closed, to your database? believe or not, I've even seen issues with visual basic not executing the qualifier "currentdb" correctly and throwing an error. but technically, for best anti-corruption practices (especially with basic), people say to declare everything and issue option explicit statements in order to force you to do so. taking shortcuts in programming can cause weird stuff.

if you're interested, i was asked to write a small app in vb6 last week and I purposely used the compiler multiple times to check for mistakes that I missed. apparently that method resulted in corruption. the result? a procedure initializing itself in a never-ending loop after it was called the first time. e.g. - running over and over again and the code not advancing to the next line after the called procedure ended. talk about WEIRD...

[John_G]

Hi -

I solved it by rewording the query so that it used a sub-query: Delete from TableName where Table_ID in (Select Table_ID from.....) - that sort of thing.

As it turns out, in SQL you cannot delete from a multi-table query by using the query name in the SQL. Instead, you have to use the SQL of the multi-table query, replacing Select From with Delete From, and specifying which table you want to delete from, something like this:

Delete DISTINCTROW table1.* from table1 inner join table2 on table1.ID = table2.ID where......

I got that from the old AccessMonster newsgroup. The DISTINCTROW is needed to make it work, though it wasn't really explained why.

Thanks for your assistance.

John