operation must be an updateable query

Aravind Ranganathan
Aravind Ranganathan used Ask the Experts™
on
i have a button called set to processed, i also have a field on a linked table called processed which is a bit field. I want to update this  to a 1 when clicked on the button based on some where conditions.

this is my query

Private Sub Set_To_Processed_Click()
task = " UPDATE [dbo_Load Table] INNER JOIN WorkTable ON [dbo_Load Table].ID = WorkTable.ID SET Processed = 1" _
& " WHERE (((WorkTable.[Pay Group])<>[dbo_Load Table].[Pay Group])) OR (((WorkTable.[Pay Group Description])<>[dbo_Load Table].[Pay Group Description])) OR (((WorkTable.[General Ledger Account])<>[dbo_Load Table].[General Ledger Account])) OR (((WorkTable.[General Ledger Cost Center])<>[dbo_Load Table].[General Ledger Cost Center])) OR (((WorkTable.[General Ledger Department])<>[dbo_Load Table].[General Ledger Department])) OR (((WorkTable.[Work Center])<>[dbo_Load Table].[Work Center])) OR (((WorkTable.[Pay Period Ending Date])<>[dbo_Load Table].[Pay Period Ending Date])) OR (((WorkTable.Hours)<>[dbo_Load Table].[Hours])) OR (((WorkTable.Amount)<>[dbo_Load Table].[Amount]) OR ((WorkTable.Week)<>[dbo_Load Table].[Week]) OR ((WorkTable.[Pay Type Code])<>[dbo_Load Table].[Pay Type Code]) OR ((WorkTable.[Pay Type Description])<>[dbo_Load Table].[Pay Type Description]) OR ((WorkTable.[File Number])<>[dbo_Load Table].[File Number]) OR ((WorkTable.Name)<>[dbo_Load Table].[Name])" _
& " OR ((WorkTable.[HOURLY SALARY])<>[dbo_Load Table].[HOURLY SALARY]) OR ((WorkTable.[FULL TIME_PART TIME])<>[dbo_Load Table].[FULL TIME_PART TIME]) OR ((WorkTable.ACTIVE_INACTIVE)<>[dbo_Load Table].[ACTIVE_INACTIVE]) OR ((WorkTable.[HOURLY RATE])<>[dbo_Load Table].[HOURLY RATE]));"
DoCmd.RunSQL (task)
End Sub

Open in new window


but i get the above error, i looked online they said it might have to do with some permission on the database file or the folder and i checked that and i have read-write access.

any help will be greatly appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I assume you are connecting to SQL is that correct?

Do you have any other Update queries running without errors?
Distinguished Expert 2017

Commented:
Make sure both tables have primary keys defined.
Make sure that one of the join fields is a primary key so the query engine can determine the relationship between the tables.
Aravind RanganathanWindows Application Developer

Author

Commented:
Tusitala yes i am connecting to Sql Server, no this is my only update query
Aravind RanganathanWindows Application Developer

Author

Commented:
PatHartman yes i have primary key defined on the join fields but it still does'nt run even when i run on GUI i am getting the same error.
Distinguished Expert 2017
Commented:
Create a new query that uses the exact join used in your update query.  If you open a recordset of that query, can you update the records?  

If Processed appears in both tables, you need to qualify it with the table name.  I never create static SQL using VBA.  I always use querydefs.  It makes the SQL easier to test.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial