I am currently building a Shiny App that is based on a previous MS Access App. I need to replicate MS Access queries behind each Shiny app button in SQL Server. What is the best way to reuse the SQL syntax in MS Access in R (i.e., copy-paste the MS Access query directly in R)?
Indeed it appears that Access SQL is slightly different from SQL Server syntax and therefore I cannot simply do it using either DBI (dbGetQuery(), dbExecute(), dbSendQuery()) or dbplyr (sql()).
Here is an example with MS Access SQL syntax in R. ("100%" is left on purpose as a table name contains that string.)
UPDATE [table1]
INNER JOIN ([table2 100%]
INNER JOIN ([table3]
INNER JOIN table4
ON ([table3].[col1] = table4.[col1])
AND ([table3].col2 = table4.col2))
ON ([table2 100%].[col1] = [table3].[col1])
AND ([table2 100%].[col2] = [table3].[col2]))
ON [table1].col1 = [table3].col1
SET [table2 100%].[col2] = [table3]![col2]
WHERE ((([table3].[colY])<>0) AND (([table3].[colZ])=True));
Resulting in the following error message in R console :
Erreur : nanodbc/nanodbc.cpp:1617: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'INNER'. [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'table3'. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
I also get : "The multi-part identifier … could not be bound in Join statements" when I tweak the query.
I set up my connection with :
con <- DBI::dbConnect(odbc::odbc(), driver = params.SQL.driver,
server = params.SQL.server, database = params.SQL.database,
encoding = params.SQL.encoding)
where params.SQL.server = "SQL Server"
In addition to UPDATEE...FROM translation, consider avoiding the nesting of JOIN as MS Access tends to do. Specifically, move all ON clauses right after JOIN clauses which should work given all tables are combined with INNER JOIN. In fact, you can even move WHERE conditions to ON.
Consider also table aliases for short-hand readability. Finally, usually the table colum being updated should be in FROM clause. Also, if table1 and table4 are not used for filtering, they are redundant.
UPDATE [t2] -- USING ALIAS
SET [t2].[col2] = [t3].[col2]
FROM [table2 100%] t2 -- MOVED DUE TO SET COLUMN REFERENCE
INNER JOIN [table3] t3
ON [t3].[col1] = [t2].[col1]
AND [t3].[col2] = [t2].[col2]
AND [t3].[colY] <> 0
AND [t3].[colZ] = 1 -- NO True CONSTANT IN SQL SERVER
INNER JOIN [table1] t1
ON [t1].[col1] = [t3].[col1]
INNER JOIN table4 t4
ON t4.[col1] = [t3].[col1]
AND t4.[col2] = [t3].[col2]