Sql Server ODBC Date Field - Optional feature not implemented

Question

I have a SQL Server table which has fields of type Date in it. I am trying to update or insert a record into the table via Micosoft Access using ODBC. I get the error:

[ODBC SQL Server Driver]Optional feature not implemented

when I try and update or insert a record.

I have to use Date fields not DateTime fields in my table because I am using very old dates going back 2000 years.

Is there any way round this problem, which I assume is caused by the Date fields?

This is what the table looks like

CREATE TABLE [dbo].[Person](
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[DOB] [date] NOT NULL,
[DOD] [date] NULL DEFAULT (NULL),
[Name] [nvarchar](100) NOT NULL)

Solution 1

You best bet is to dump the use of the "legacy" sql driver, and user the newer native 10 or 11 driver. The older driver will view date fields as text, but using the newer native 10/11 driver will see the column as a date column. This will require you to re-link your tables.

Solution 2

If you can't change your SQL Server version, an easier solution is to pass the date as an adVarChar, and then do a CAST(@param AS DATE) in your SQL stored procedure.

Comments

[Rob Sedgwick]

Quite right, using the SQL Server Native Client 11.0 did the trick

[Rob Sedgwick]

I don't think this is the best answer because you are changing your database types when all you need to do is use the correct driver (see accepted answer)

[MarredCheese]

+1 this worked for me. These days, "ODBC Driver [version] for SQL Server" is recommended over the "native" driver though, as detailed here: stackoverflow.com/a/54206996/5405967