[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 765
  • Last Modified:

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?
0
Triality
Asked:
Triality
1 Solution
 
peter57rCommented:
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now