I’m forced to use MS Access for some legacy system. I don’t know what Microsoft thought when they made it so that MS Access uses a flavor of SQL that’s pretty different from their main product SQL Server. Did they just think nothing or is there some evil intent? Nothing probably. But anyhow, they did it.
In this post, I describe how to do a
JOIN query in MS Access.
I want to replicate the following SQL Server query (in TSQL) in MS Access.
-- MSSQL UPDATE Sachwert SET Wert = '1', DATE = GETDATE() FROM Artikel a INNER JOIN Sachwert AS s on a.GUID = s.GUID INNER JOIN Sgpos AS sgp on sgp.SACHGRUPPE = s.SACHGRUPPE INNER JOIN Catalog c on c.Year = a.Year WHERE a.Artikel = '104905' AND sgp.SACHGRUPPE = 'V1'
For MS Access you have to do these changes:
- Put the name of the table where the update should happen before each name of each column which you want to change and remove the name of the table which is located before the
SET. This gives
UPDATE SET Sachwert.Wert='1', Sachwert.DATE = GETDATE().
- Put the
SETclause after the
JOINs, remove the word
FROMbut leave the word
UPDATEat the beginning.
- If you have defined an alias, you must use this alias in the rest of the query. You then cannot longer use the original name. In our case, the
SETclause must change to
SET s.Wert='1', s.DATE = GETDATE()because of the
Sachwert AS s.
- If you have N
JOINs, you have to add N-1 sets of parentheses. The left parentheses must all be directly after the
UPDATE, the right ones one after each
JOIN-clause – except the last one.
So in the end you get this:
-- MS Access UPDATE ((Artikel a INNER JOIN Sachwert AS s on a.GUID = s.GUID) INNER JOIN Sgpos AS sgp on sgp.SACHGRUPPE = s.SACHGRUPPE) INNER JOIN Catalog c on c.Year = a.Year SET s.Wert = '1', s.DATE = NOW() WHERE a.Artikel = '104905' AND sgp.SACHGRUPPE = 'V1'