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.
Aravind RanganathanWindows Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Do you have any other Update queries running without errors?
0
PatHartmanCommented:
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.
0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
Tusitala yes i am connecting to Sql Server, no this is my only update query
0
Aravind RanganathanWindows Application DeveloperAuthor 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.
0
PatHartmanCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.