Query milliseconds in a date field that is in a SQL Server table l

[Nick B]

I have an Access “mdb†database that has multiple linked SQL Server tables. I
'm interested in querying a SQL Server table that has, as part of its primary
key, a date field. I would like to query the data field and retrieve the data
field’s milliseconds. Does anyone have any ideas on to do this?

The solution must be entirely in Access because I only have read permisions
in SQL Server database.

 

[Rick Brandt]

I have an Access "mdb" database that has multiple linked SQL Server
tables. I 'm interested in querying a SQL Server table that has, as
part of its primary key, a date field. I would like to query the data
field and retrieve the data field's milliseconds. Does anyone have
any ideas on to do this?

The solution must be entirely in Access because I only have read
permisions in SQL Server database.

You will have to use a passthrough query and return the date field as a
character field using Convert(). As long as Access recognizes it as a DateTime
field it will round it to the second.

BTW that linked table is very likely to cause you problems if you edit records.
If you edit a record that has an identical DateTime value (rounded to the
second) as other records in the table then your edit could end up being applied
to all of those records. For linked tables you wish to edit you really need
the PK to be a DataType that matches up to a Jet DataType without any rounding.

 

[Nick B]

Thanks for your help Rick. You gave me an option to think about.