We help IT Professionals succeed at work.

operation must be an updateable query

54 Views
Last Modified: 2018-01-17
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

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

Do you have any other Update queries running without errors?
CERTIFIED EXPERT
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.
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions