Operation must use an updateable query

Hi,

In Sql Server UPDATE with join should be constructed as UPDATE SET ... FROM ... JOIN ... WHERE ...

task = " UPDATE SET [dbo_Load Table].Processed = 1 FROM [dbo_Load Table] INNER JOIN [WorkTable] ON [dbo_Load Table].ID = [WorkTable].ID WHERE [dbo_Load Table].ID = [WorkTable].ID "
DoCmd.RunSQL (task)

Select allOpen in new window

Regards,
Daniel

@Daniel i am getting an Sytax error in UPDATE statement error.

Try this :

UPDATE  [dbo_Load Table] SET  [dbo_Load Table].Processed = 1 FROM [dbo_Load Table] INNER JOIN [WorkTable] ON [dbo_Load Table].ID = [WorkTable].ID WHERE [dbo_Load Table].ID = [WorkTable].ID 

Select allOpen in new window

Cut WHERE clause, my purpose was to show what is the correct way, indeed I overlooked you don't have correct WHERE :) However, if you need to filter rows further feel free to add your condiftions there.

UPDATE SET [dbo_Load Table].Processed = 1 FROM [dbo_Load Table] INNER JOIN [WorkTable] ON [dbo_Load Table].ID = [WorkTable].ID

Select allOpen in new window

Filter:

UPDATE SET [dbo_Load Table].Processed = 1 FROM [dbo_Load Table] INNER JOIN [WorkTable] ON [dbo_Load Table].ID = [WorkTable].ID WHERE [dbo_Load Table].Processed = 0

Select allOpen in new window

@ John Tsioumpris i am getting this error:


and the filter query is returning the Syntax error in Update statement.

it seems that you didn't copied it correctly...check if there is a space ..

@John Tsioumpris this is what my Debug.Print looks like

UPDATE [dbo_Load Table] SET [dbo_Load Table].Processed = 1 FROM [dbo_Load Table] INNER JOIN [WorkTable] ON [dbo_Load Table].ID = [WorkTable].ID WHERE [dbo_Load Table].ID = [WorkTable].ID

i am still getting the same error :(

make a passthrough query and copy the above output...run it to see if its OK

@ John Tsioumpris get an Invalid object name 'dbo_Load Table'.

@PatHartman there was no primary key on the sql load table but i went in and made the ID a primary key.  but back to the same original error stating Operation must use an updateable query

Hi,

We're in access :) I believe you should narrow your join, either by adding another join condition or by adding DISTINCTROW clause.

UPDATE DISTINCTROW SET [dbo_Load Table].Processed = 1 FROM [dbo_Load Table] INNER JOIN [WorkTable] ON [dbo_Load Table].ID = [WorkTable].ID

Select allOpen in new window

https://msdn.microsoft.com/en-us/vba/access-vba/articles/all-distinct-distinctrow-top-predicates-microsoft-access-sql

I assume permissions to db files are fine(?).

@PatHartman, yes i refreshed the linked table and it worked thnx.

You're welcome.  Looks like three people learned something from this question.  All tables should have primary keys regardless and Access refuses to update any linked table that does not have a primary key or unique index.  This is one of the two major reasons for the "must use an updateable query" message.  The second cause is aggregation in the query.  A very common request is to update tableA with the sum of data in tableB.  Aside from violating second normal form, Access balks because when you aggregate data, you loose the PKs of all the records even though logically in the described scenario you are updating tableA which is not aggregated and so should retain its individual row IDs.

@PatHartman what is the fastest method to update the linked table on sql server the above query is very very slow and we are talking about 200k records and its taking forever.

Are you running the query you posted originally or something else?

Are both tables in SQL Server?  If they are not and you can't get your table uploaded to the server, then you're stuck with slow.

@PatHartman the same query from above i ran the query at 4pm and now its 4.38pm and only 25k of 55k  records are updated. the work table is a local table and the load table is a sql table i am updating the records based on the worktable data the user wants processed.

Behind the scenes this is what is happening:
1. Access requested the entire 200K rows be brought down from the server.
2. Access performed the join to the local table locally.
3. Access is now sending update queries back to the server one row at a time.

The bigger the server table is, the longer this will take.  It is a very inefficient process.  It will NEVER be fast.  The fact that Access can do this at all is miraculous but just because Access can do it doesn't mean you should ask it to especially for large server side tables.

A better, more efficient solution would be to create a server-side table to hold your local data.  Run a Truncate to remove existing rows and then run an append query to upload the local data.  Then run the update query against both server-side tables.  It's more complicated but will be significantly faster.

If you only have to do this once, live with the slowness. Otherwise, try my suggestion.

@PatHartman i tried your solution but the insert into the server-side table to hold the local data is taking for ever to insert :( any idea why i am inserting about 57155 records

In the greater scheme of things, 57 K is not a lot of records.  I push around tables with millions of rows.  How are you dong the insert?

If you have made the passthrough query it would take seconds ....

@PatHartman i am doing this:

Task0= "Insert Into Statement"

CurrentDb.Execute( Task0), dbFailOnError

@John Tsioumpris yea but i am doing an insert from local table to Sql table can i accomplish this by using a pass through query????

Please post the insert statement.

@PatHartman :

task0 = "INSERT INTO dbo_WorkTable ( ID, [Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], [General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE] )" _
& "SELECT WorkTable.ID, WorkTable.[L Batch ID], WorkTable.[Pay Group], WorkTable.[Pay Group Description], WorkTable.[General Ledger Account], WorkTable.[General Ledger Cost Center], WorkTable.[General Ledger Department], WorkTable.[Work Center], WorkTable.[Pay Period Ending Date], WorkTable.Hours, WorkTable.Amount, WorkTable.Week, WorkTable.[Pay Type Code], WorkTable.[Pay Type Description], WorkTable.[File Number], WorkTable.Name, WorkTable.[HOURLY SALARY], WorkTable.[FULL TIME_PART TIME], WorkTable.ACTIVE_INACTIVE, WorkTable.[HOURLY RATE]" _
& " FROM WorkTable; "

Select allOpen in new window

CurrentDb.Execute (task0), dbFailOnError

Select allOpen in new window

Did you get any speed improvement when you removed the join?  I'm assuming that dbo_WorkTable is a linked table.

This is unlikely to be your problem but neatness counts.  Enclose Name in square brackets.  Column names should be comprised ONLY of letters (upper and lower case), numbers, and the underscore.  do not use embedded spaces or special characters.  Also be mindful of reserved words.  You especially do not want to use reserved words that are the names of functions or properties.  "name" is a property.  EVERY object has a name and you will run int situations where Access has trouble distinguishing your [name]  from the Name property of an object.

You could also try opening SSMS and importing the table to see how long that takes.

@PatHartman still stuck on this issue mate, i tried different approaches such as DoCmd.TransferText that creates a csv but i am not sure how to use the bcp utility through access and also the client is not going to have bcp utility installed. i tried DoCmd.TransferDatabase but that did not work either, i am running out of time and patience. can you please think of any other alternative SSMS is much faster but this user does not have any sql components installed she is just going to run this access application so i need to accomplish all the functions via vba.

If you can do this with a stored procedure, you can run the sp using a pass-through query from Access.