Operation must use an updateable query

i have a access query i am trying to update a bit field on a linked table on sql server, but i am getting a n error saying it must be an updateable query.

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

Open in new window


i have a worktable where user load data on to it from the main load table then edits the data and when successful clicks ona settoprocessed button which has the above code in it and i want all the matching data from work table to be set to 1 from load table so next time user load new data the values with 1 dont show up.
Aravind RanganathanWindows Application DeveloperAsked:
Who is Participating?
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.

Daniel_PLDB Expert/ArchitectCommented:
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)

Open in new window


Regards,
Daniel
0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Daniel i am getting an Sytax error in UPDATE statement error.
0
John TsioumprisSoftware & Systems EngineerCommented:
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 

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Daniel_PLDB Expert/ArchitectCommented:
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

Open 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

Open in new window

0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@ John Tsioumpris i am getting this error:

Capture2.PNG
and the filter query is returning the Syntax error in Update statement.
0
John TsioumprisSoftware & Systems EngineerCommented:
it seems that you didn't copied it correctly...check if there is a space ..
0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@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 :(
0
John TsioumprisSoftware & Systems EngineerCommented:
make a passthrough query and copy the above output...run it to see if its OK
0
PatHartmanCommented:
Your original syntax is correct.  This is not a syntax problem.

1. Does the table you are updating have a primary key or unique index?  If it doesn't, you cannot update the table using an Access query.  Add a PK.  Use an identity column if you don't have a natural key.
2. Are you using the original SQL Server ODBC driver?  It may have trouble with bit fields.  You can try downloading a newer driver and using that or change the datatype to smallInt if that is an option.
1

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
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@ John Tsioumpris get an Invalid object name 'dbo_Load Table'.
0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@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
0
Daniel_PLDB Expert/ArchitectCommented:
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

Open 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(?).
0
PatHartmanCommented:
Did you refresh the link?  If you didn't, Access isn't seeing the new PK.

Open the table in datasheet view.  Can you update any columns?  Can you update the bit column?
0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@PatHartman, yes i refreshed the linked table and it worked thnx.
0
PatHartmanCommented:
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.
1
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@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.
0
PatHartmanCommented:
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.
0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@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.
0
PatHartmanCommented:
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.
0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@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
0
PatHartmanCommented:
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?
0
John TsioumprisSoftware & Systems EngineerCommented:
If you have made the passthrough query it would take seconds ....
0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@PatHartman i am doing this:

Task0= "Insert Into Statement"

CurrentDb.Execute( Task0), dbFailOnError
0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@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????
0
PatHartmanCommented:
Please post the insert statement.
0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@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; "

Open in new window


CurrentDb.Execute (task0), dbFailOnError

Open in new window

0
PatHartmanCommented:
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.
0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@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.
0
PatHartmanCommented:
If you can do this with a stored procedure, you can run the sp using a pass-through query from Access.
0
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
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.