So I have a linked server (access mdb database) in SQL SERVER. I have a table in there called XX2 Sectors (yes i know that white spaces in table names/columns are a bad practice but I don't have any control over it in this case).
When I run a SELECT query in a form
SELECT * FROM [Server_Name]...[XX2 Sectors]
it works completely fine, BUT when I am running and UPDATE query on this table e.g.
UPDATE [Server_Name]...[XX2 Sectors] SET Column_Name = 'Variable' WHERE
Column_Name = 'whatever'`
I get an error: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Server_Name" returned message "The Microsoft Access database engine cannot find the input table or query 'XX2'. Make sure it exists and that its name is spelled correctly."
-> So basicaly it cuts off the table name at the white space point which [] should prevent... But if I rename the table from XX2 Sectors to XX2_Sectors -> effectively removing the white space then the UPDATE query works fine.
Any thoughts? I think i tried everything and I am now at a loss...
Thank you.
UPDATED: So the answer was that for some reason when you use linked server is SQL Sever (bug or not I don't know) but if you try to update a filed table in the Linked Datasheet (and it is Access) based on the value of the field it won't work unless you update something else as well or choose another criteria. For Example:
UPDATE TABLE SET FIELD1 = Z WHERE FIELD1 = X
will not work, BUT
UPDATE TABLE SET FIELD1 = Z WHERE FIELD2 = X
OR
UPDATE TABLE SET FIELD1 = Z, FILED2 = F WHERE FIELD1 = Y
OR
UPDATE TABLE SET FIELD1 = Z, FIELD2 = FIELD2 WHERE FIELD1 = X
Will work fine.
-------- OLD ANSWER BELOW ------
After 3 hours I found the solution for this problem which unfortunately means there is no solution as the problem resolved itself after I had launch...