2

I will try to simplify the situation as best as I can... I work with a team of field service technicians. We share a lot of our tools and equipment between us because SOMEONE doesnt want to spend money on us. I am using MS Access to develope a way to track who has what tool or specific piece of equipment out of a pool of about 2000+ items. I have got the process figured out down to one last step. It gos like this:

  1. Tech opens a form that shows current location/ownership of all 2000+ items, which is pulled off a SharePoint List and inserted into a local table.
  2. Tech makes changes to local table.
  3. When Tech is done, the Sharepoint List gets updated FROM the local table (This is where I am stuck).

I have followed every tutorial, walk through, and work around that I can understand. But every time I run the Query, Access asks me for the "Parameter Value" of each field I am trying to update. Mind you, everything I have found shows an example of how to update one field in a table. I am trying to update 5 fields per row. When I let Access build the SQL for this it looks like:

UPDATE Table1 INNER JOIN Table2 
ON Table1.[Asset ID] = Table2.[Asset ID] 
SET Table1.Department = Table2.[Department], Table1.[Cal Status] = Table2. 
[Cal Status], Table1.[Return Date] = Table2.[Return Date], Table1.Comments = 
Table2.[Comments], Table1.[Cal Due] = Table2.[Cal Due], Table1.Active = 
Table2.[Active];

The fields are not misspelled, I already checked and double checked that.

I have seen some comments about how Access has problems with UPDATE. But I am self taught with everything I do and I cant switch to something else until I understand this. I just started with Access and SQL at the begining of this week, so please be patient with me.

EDIT

UPDATE Assets
SET Assets.Department = AssetsSharePoint.[Department],
Assets.[Cal Status]   = AssetsSharePoint.[Cal Status],
Assets.[Return Date]  = AssetsSharePoint.[Return Date],
Assets.Comments       = AssetsSharePoint.[Comments],
Assets.[Cal Due]      = AssetsSharePoint.[Cal Due],
Assets.Active         = AssetsSharePoint.[Active]
FROM Assets
INNER JOIN AssetsSharePoint ON Assets.[Asset ID] = AssetsSharePoint.[Asset 
ID];

This gives me a "Syntax Error. Missing Operator" Then Highlights FROM

EDIT 2

If I cut it down to 1 field, leave out the FROM, and include WHERE it works. I dont want to make 5 seperate queries for this. But I guess I am going to have to....

Tucker Black
  • 21
  • 2
  • I was also going to suggest updating a single field, but that sort of defeats the purpose of what you are trying to do. – Tim Biegeleisen Apr 20 '18 at 03:41
  • This should work, especially if you created the query in the query designer. Maybe you have simplified the code too much, can you add the original query that isn't working? – Andre Apr 20 '18 at 06:03
  • @andre The code in the first edit is straight copy and paste. – Tucker Black Apr 20 '18 at 15:02

2 Answers2

0

Your syntax is off, and it should be UPDATE ... SET ... FROM:

UPDATE t1
INNER JOIN Table2 t2
    ON t1.[Asset ID] = t2.[Asset ID]
SET
    t1.Department    = t2.[Department],
    t1.[Cal Status]  = t2.[Cal Status],
    t1.[Return Date] = t2.[Return Date],
    t1.Comments      = t2.[Comments],
    t1.[Cal Due]     = t2.[Cal Due],
    t1.Active        = t2.[Active]
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thank you, I didnt expect such a quick answer! I used that syntax (substituted the real table names) and it is giving me `Syntax error. Missing Operator in Query Expression` Then highlights `FROM` And sorry I realize after seeing your response I could have tried a little harder to format my code. – Tucker Black Apr 20 '18 at 01:47
  • @TuckerBlack I can't see the actual code you ran, so I can't explain why it didn't work. [From this SO answer](https://stackoverflow.com/questions/12882212/sql-updating-from-an-inner-join) my syntax looks correct. Can you give me more information which would help me to debug your query? – Tim Biegeleisen Apr 20 '18 at 01:52
  • Updated my original post with the info – Tucker Black Apr 20 '18 at 03:06
  • @TuckerBlack One of our queries should be working. Maybe someone else can shed light on this. – Tim Biegeleisen Apr 20 '18 at 03:13
  • This is T-Sql, Access Sql doesn't do `UPDATE .. SET ... FROM ...`, see the 2nd answer in the question you linked. – Andre Apr 20 '18 at 06:00
  • @Andre I'm flying blind to be honest, because I don't have Access setup locally, so I can't test my answer. – Tim Biegeleisen Apr 20 '18 at 06:01
0

Try this:

UPDATE Assets, AssetsSharePoint
SET Assets.Department = AssetsSharePoint.[Department],
Assets.[Cal Status]   = AssetsSharePoint.[Cal Status],
Assets.[Return Date]  = AssetsSharePoint.[Return Date],
Assets.Comments       = AssetsSharePoint.[Comments],
Assets.[Cal Due]      = AssetsSharePoint.[Cal Due],
Assets.Active         = AssetsSharePoint.[Active]
WHERE Assets.[Asset ID] = AssetsSharePoint.[Asset ID];

Neater statement:

UPDATE Assets INNER JOIN AssetsSharePoint ON Assets.[Asset ID] = AssetsSharePoint.[Asset ID]
SET Assets.Department = AssetsSharePoint.[Department],
Assets.[Cal Status]   = AssetsSharePoint.[Cal Status],
Assets.[Return Date]  = AssetsSharePoint.[Return Date],
Assets.Comments       = AssetsSharePoint.[Comments],
Assets.[Cal Due]      = AssetsSharePoint.[Cal Due],
Assets.Active         = AssetsSharePoint.[Active]
Rene
  • 1,095
  • 1
  • 8
  • 17