Sql server date type displayed as text field in MS Access

Question

I have a MS Access 2010 / SQL Server 2012 front end database with multiple date fields in different tables. Sometimes I need to save time, so I used datetime or smalldatetime data types. However, some fields need to store a date, so I used the Date data type.

My problem is that in MS Access my ODBC-bound table shows date data type fields as text. This leads to problems with some dates stored in yyyy-dd-mm format, and others in yyyy-mm-dd format.

This is mistake? Do i need to use smalldatetime?

Thanks for any help, Jim

Answer 1

I linked Access 2007 and SQL Server 2008 with the same problem.

See this question , if you look at Albert D. Callal's comment on the first answer, he tells us that the problem is with an outdated driver connecting the interface to the back end.

If you cannot select the updated driver (and remember that even if you can, other users of your database on different client machines may not be available), a workaround is to use the date and time data type in each case.

Answer 2

Just to explain the comment given by @BiigJiim, I actually had the Native client 11.0 driver installed, but as I was creating connections to DSN-Less, I had to formally change the connection string: Driver={SQL Server Native Client 11.0};