Using SQL Server timestamp in Access

[maccaroo]

Hi

I've got an database with SQL Server back-end and Access front-end. The Server gets a number of audit records from a (totally separate) remote db. The table on the remote db has a timestamp field which allows us to work out the exact order the fields were entered (very important). The problem comes in when we need to compare these fields in Access. Is there any way to use the (SQL Server 2000) timestamp field in Access? I do have a DateTime field in the audit table, but since there may be multiple audits in the same second, it's no good for that...

 

[HitechUser]

Timestamps
On servers that support them (such as Microsoft SQL Server), timestamp fields make updating records more efficient. Timestamp fields are maintained by the server and are updated every time the record is updated. If you have a timestamp field, Microsoft Access needs to check only the unique index and the timestamp field to see whether the record has changed since it was last retrieved from the server. Otherwise, Microsoft Access must check all the fields in the record. If you add a timestamp field to an attached table, re-attach the table in order to inform Microsoft Access of the new field.

The SQL Server timestamp data type has no counterpart in Access. In spite of its name, it is neither a time nor a date, nor is it some encoded representation of a time or date. A timestamp is a binary number column that is updated automatically every time a row is inserted or updated. This permits the client program to confirm whether values have changed since it last checked the record. A timestamp column enables BROWSE-mode updates and makes cursor update operations more efficient.

The timestamp field indicates only that a record was changed, not when it was changed. You cannot set the timestamp column to any specific value. To record the times data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers to update the values automatically when any modification takes place.

Just some food for thought.

 

[maccaroo]

Thanks. To be honest, I'm not that familiar with timestamp fields. What I do understand (I think), is that it doesn't represent an actual time, but rather a the sequence of the record's last modification in relation to the other records in the database. I'm hoping it should then be possible to order the records in a table by the timestamp field, and thereby get the oldest to newest fields. As I mentioned before, I need to know the order in which records were added to the table, since there are often a number of audits that arrive at the same second, and I need to know the exact order...