I have a SQL Server 2014 Express server linked to an Access DB. I am able to SELECT and view data in the access db without issue. However, when I run update statements they don't always work. The linked server name is PINKSHEET.
The update query below fails.
UPDATE PINKSHEET...[I Warehouse] SET [Warehouse Name] = 'Test' WHERE ([Warehouse ID #] = 3)
with the message
The Microsoft Jet database engine cannot find the input table or query 'I'.
followed by the message
Cannot execute the query "UPDATE I Warehouse set `Warehouse Name` = 'Test' WHERE `Warehouse ID #`= ? " against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PINKSHEET".
If I rewrite the above query as noted below it works without issue.
UPDATE PINKSHEET...[I Warehouse] SET [Warehouse Name] = 'Test' WHERE [Warehouse ID #] IN (SELECT [Warehouse ID #] FROM PINKSHEET...[I Warehouse] WHERE [Warehouse ID #] = 3)
I cannot figure out why the simpler structure throws the error.