ODBC linked table not showing fractions of seconds

Question

I have linked an IBM informix database table through an ODBC connection to an Access 2010 database. My issue is that the date field in this table only shows dd/mm/yy HH:nn:ss in the Access view, where the stored data is to 1000th of a second.

I can show this in Excel 2010 but not in Access 2010: is this possible? Not having this level of accuracy is preventing me making accurate calculations!

Answer 1

There is a similar question on another forum here. The Date/Time field type in Access does not store fractions of seconds, and linked tables implicitly cast their columns to the corresponding Access data type, so the fractions of seconds are not available in a linked table even though they are stored in the remote database.

For example, I have a SQL Server database with a table named dbo.linkedTable that has a datetime column with fractions of seconds:

If I create a linked table in Access the [datetimeCol] is mapped to the Date/Time field type in Access and the times are rounded to the nearest second

As a workaround, I can create a Pass-Through query that uses T-SQL to convert the datetime value to a string...

SELECT ID, CONVERT(varchar, datetimeCol, 21) AS strDatetime FROM dbo.linkedTable

...returning...

...and I can parse the [strDatetime] string value to retrieve the fractional seconds.