5

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)
Rob Sedgwick
  • 4,342
  • 6
  • 50
  • 87
  • 1
    How about posting the code you are using so we can try and spot the problem? – Wayne G. Dunn Jan 12 '16 at 14:29
  • You have an MS_ACCESS tag on this. What part is Access playing? – simon at rcl Jan 12 '16 at 14:40
  • @simonatrcl Rob posted ."..via Micosoft Access using ODBC..." there are dozens of search results on this topic, but they mention specifics which I think we need to see the code for. i.e. https://support.microsoft.com/en-us/kb/214459 – Wayne G. Dunn Jan 12 '16 at 14:50
  • @WayneG.Dunn - D'Oh! Sorry! Agree on the need for code. – simon at rcl Jan 12 '16 at 14:55
  • @WayneG.Dunn there isn't any code though. I am editing a record in Access. Any Sql is generated in Access and gets sent to Sql Server via ODBC, – Rob Sedgwick Jan 12 '16 at 15:51
  • @simonatrcl the access tag was added by the moderator, but it is the software where the editing takes place. I don't think it's really an Access issue, I think it's more of an ODBC issue which is why I left that tag out, – Rob Sedgwick Jan 12 '16 at 15:53
  • Are you opening the linked table directly and trying your add/change? Or do you have a form involved? Are you using a stored procedure? Can you add / change ANY records? if so, under what circumstances? Does it always fail if touching a certain field? Does it always work if NOT touching a certain field? I have found a bunch of articles relating to Date / ODBC / Access, but without knowing how and what you are trying to store in the date field... – Wayne G. Dunn Jan 12 '16 at 18:19

3 Answers3

6

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.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • Quite right, using the SQL Server Native Client 11.0 did the trick – Rob Sedgwick Jan 14 '16 at 10:14
  • +1 this worked for me. These days, "ODBC Driver [version] for SQL Server" is recommended over the "native" driver though, as detailed here: https://stackoverflow.com/a/54206996/5405967 – MarredCheese Dec 13 '19 at 21:55
1

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.

Michael Currie
  • 13,721
  • 9
  • 42
  • 58
0

I've experienced the same problem today. I use MsAccess 2010 for developlemt, and have MsSql2012 at back-end.

There was no problem on my computer, but other clients that use the accde runtime version has experienced this trouble.

After several trials; Issue resolved when replacing DATE type with SMALLDATETIME. please try this..? Indeed I only needed the date part, not the time, but ok anyway!

[DOB] [date] NOT NULL, [DOD] [date] NULL DEFAULT (NULL),

Hope this helps to you as well

Yahya Hussein
  • 8,767
  • 15
  • 58
  • 114
Çağlar Durgun
  • 1
  • 1
  • 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) – Rob Sedgwick Aug 27 '18 at 18:36