10 May 2012

Optional Feature not implemented -- ODBC call error

The problem:
Features: an access application with one form linked correctly to a table.
The form loads fine, the data in the table is viewed correctly.
The person in need to make a change (in this case: the business analyst) can't make a change. He enters the new data to the field, and once he leaves he gets the following error message:
ODBC -- call failed
[Microsoft][ODBC SQL Server Driver]Optional feature not implemented (#0)
Possible solutions:
The results I've found googling the problem usually relate to the case when there's some code. The problem is, that there's absolutely no code, so I can't tell "which line is it". It cannot be "parameter passing" as it was proved to some people.
Does he have the right permissions on the database?
Apparently he does, as
I've given the business analyst the permissions required on the database (namely: db_datareader, db_datawriter),and the following works as it should:

Also, when trying to use the following code straight from the SQL server, it runs perfectly fine:

EXECUTE AS LOGIN = 'BusinessAnalyst';

--gives 'BusinessAnalyst', 'BusinessAnalyst'
EXECUTE AS LOGIN = 'BusinessAnalyst';
from dbo.dimTable

--produces the results, as expected from someone with db_ddlreader permissions
EXECUTE AS LOGIN = 'BusinessAnalyst';
update DBO.dimTable
set column= 'value'
where ID = 1

--produces the results, as expected from someone with db_ddlwriter permissions

Needless to say, when I try to make the change from my instance of Ms-Access on my computer it run perfectly fine. And no, giving him higher permissions, like "db_ddladmin" didn't really help.
So obviously the problem is in the ODBC driver. But where?
As it works on my machine and doesn't work on his, we've decided to log in from my machine.
Then I had to re configure the ODBC connection, and found out where the error was:
Instead of choosing "SQL Server" as a client, you need to choose "SQL Server Native Client 10.0". 

True, the BA didn't have the driver installed on his machine, but he's a smart guy, so he  googled and got here:

choose "native client" and sent me the following beautiful email:



Excellent! now I'm back to work. There's a new Fact Table coming right up...

Extra comment on October 2013: The problem results from the SQL back end being on an x64 machine. Please note that for Access front end you need your SQL server placed on X86 Machine.