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

Posted on 2011-10-31
Last Modified: 2012-05-12
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?
Question by:Triality
    LVL 77

    Accepted Solution

    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.
    LVL 44

    Expert Comment

    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.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now