Greetings All
My application generates a dynamic SQL statement that resolves to:
UPDATE dADDRESS SET Status = 4 WHERE RecordID in (SELECT RecordID FROM [LODMCustomersActive] RIGHT JOIN dADDRESS ON [LODMCustomersActive].ID = dADDRESS.Custom4 WHERE ((Status=0) AND (Category=1) AND (([LODMCustomersActive].LocCity) Is Null)));
Its intent is to find all the records that exist in dAddress that do not exist in LODMCustomersActive (i.e., no City), and change their Status to 4.
The current logged-in user has read/write access to the dAddress table, and read-only access to the underlying tables of the LODMCustomersActive query.
When I run the SQL statement using the DoCmd.RUNSQL method, it works properly, and updates 114 records in the test case. I can also cut-and-paste the SQL into the Access Query Builder, and it works as expected.
But when I use CurrentDB.Execute method with the same SQL statement, it returns an error message about “no Read permission on Table xxxx” (The named table is used in the LODMCustomersActive table.
It generated the same error using this syntax
Set qdf = CurrentDb.QueryDefs(“qryTempActionQuery”)
qdf.SQL = sSQL
qdf.Execute
msgbox qdf.recordsaffected
I wanted to use the latter syntax so I could report the number of records to the user.
Is there something about the way that I’ve structured the SQL statement that causes it to fail with Currentdb.execute or qdf.execute? The LODMCustomerActive query include a CStr() function in one field, a date constant in another field (#1/1/2002#), and a literal constant in a third field (“”) but the other fields are read directly from the underlying tables.
Thanks
