MS Access Fomatting when linked to SQL Server 2008 Dates
13-Aug-0909 Leave a comment
If linking to the new SQL Server date types, which are better in SQL Server and for the future, then MS Access 2003 treats them like strings.
To resolve:
- Create a view in SQL Server 2008 and use CONVERT(datetime, MyDate) with optional ,105 on end.
- Link to the view in MSAccess. Possibly renaming the view as the original linked table name, so that it will still work throughout existing Access design
- You may need to set unique keys during linking process. This helps MS Access to know what are unique keys so that it will be able to update records if required.
- Voila. Fixed
Steve