Linked SQL date field showing as text field in MS Access 2010

When I link to my SQL server 2008 R2 table in MS Access 2007 and 2010 the date type field in SQL is showing as a text field in Access.  I have read that MS Access 2010 supports the SQL date field, so I upgraded but I am still seeing the same issue.  When i create a MS Access database link to the SQL table it shows as text.  Any ideas how this can be fixed without changing the SQL data type to datetime?
TrialityAsked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
I think you'll find that the MS position is 'partially supports' rather than 'supports'.  Several of the new date & time datatypes fall into this category.

In practice I think this means you can get the data but it won't be the correct data type.

You will either have to do more work in Access to handle the values or else change the datatype in SQL Server.
0
 
GRayLCommented:
You can always represent the table using a query which changes the datatype from text to datetime.

SELECT fld1, fld2, fld3, CDate(fld4) as [fld4] from tblLinked;

If this is saved as qryLinked, then instead of using tblLinked in Access, you use qryLinked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.