MS Access Fomatting when linked to SQL Server 2008 Dates

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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: