I came across something annoying this week, and have decided to make a post here about it with the hopes that it can help someone else who might be experiencing the same thing!
Here's what happened. I have a Microsoft Access database that has a number of linked tables from a SQL Server 2008 database. I started to create an input form in Design view for one of these tables in Access. I added a date field that I'd chosen directly from the table, and then switched to Form view to try it out. I was annoyed to see that a date picker wasn't showing up for the date field. I switched back to Design view and checked all of the properties to make sure that it had been set up correctly (which it had), and then I double-checked the data type in SQL Server. I'd chosen 'DATE'. I checked in Access to see if it recognized this field as being a date, and it seemed to recognize it as such. The control on the form, though? It refused to act like a date.
The solution that I eventually found was to change the field in SQL Server to a DATETIME data type. I relinked the table in Access, and had no problem with the date picker on my form. I was curious to know why, though, and to make sure that this was actually the issue and not just something that I'd done wrong. I did a bit of research online and can confirm based on Microsoft's help content that four of the new date/time data types in SQL Server 2008 are only partially supported in Access 2010. These data types are as follows:
Here's what happened. I have a Microsoft Access database that has a number of linked tables from a SQL Server 2008 database. I started to create an input form in Design view for one of these tables in Access. I added a date field that I'd chosen directly from the table, and then switched to Form view to try it out. I was annoyed to see that a date picker wasn't showing up for the date field. I switched back to Design view and checked all of the properties to make sure that it had been set up correctly (which it had), and then I double-checked the data type in SQL Server. I'd chosen 'DATE'. I checked in Access to see if it recognized this field as being a date, and it seemed to recognize it as such. The control on the form, though? It refused to act like a date.
The solution that I eventually found was to change the field in SQL Server to a DATETIME data type. I relinked the table in Access, and had no problem with the date picker on my form. I was curious to know why, though, and to make sure that this was actually the issue and not just something that I'd done wrong. I did a bit of research online and can confirm based on Microsoft's help content that four of the new date/time data types in SQL Server 2008 are only partially supported in Access 2010. These data types are as follows:
- TIME
- DATE
- DATETIME2
- DATETIMEOFFSET
Posts
Hi Stacy, i found this problem too in my job. This week i started to make some test with linked tables from sql server to access 2010, and now i`m a bit dissapointing with microsoft. We are migrating all our datawarehouse from oracle to sql server (2tb and hundreds of date types), and i don`t now why microsoft doesn`t fix this bug in their own products.i`m searching for an updated odbc driver but i think that it doesn`t exist yet. Have you found anything else about this problem? Thank you and sorry for my bad english.
ReplyDeleteHi Stacy! I`ve been fighting with this problem too, and the solution that i found was to install the microsoft sql server native client 10.0. You can take it for free from microsoft download center.Hope this will help you. Bye!
ReplyDeleteWonderful, thank you for the tip!
DeleteI have the sql server native client 10.0 installed and still can't update a datetimeoffset(2) in an access 2003 form with a value like Now().
ReplyDeleteAnd I've made the same test now with Access 2010 (trying to set datetimeoffset(2) to Now() in a form's before update) and it fails in exactly the same manner.
ReplyDelete