Avatar of Aravind Ranganathan
Aravind Ranganathan
 asked on

operation must be an updateable query

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.
Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon

I assume you are connecting to SQL is that correct?

Do you have any other Update queries running without errors?

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 Ranganathan

Tusitala yes i am connecting to Sql Server, no this is my only update query
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Aravind Ranganathan

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.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question