CODEWITHSUNDEEP
sqlms-accessms-access-2007
CodeXerox
CodeXerox

Reputation: 93

What is same as TIMESTAMP datatype in Access?

For auto date\time we use TIMESTAMP datatype in SQL. What is equivalent to that datatype in MS Access 2007...

Upvotes: 1

Views: 7474

Answers (2)

Johnny Bones
Johnny Bones

Reputation: 8404

DateTime is the only date-based datatype in Access. You would then format the field to either General Date or Long Time to capture down to seconds. I don't think Access gets more accurate than that.

Upvotes: 0

Renaud Bompuis
Renaud Bompuis

Reputation: 16786

There is no exact equivalent in Access.
To clarify, TIMESTAMP in SQL is not always a usable Date/Time, for instance in SQL Server it is deprecated and equivalent to ROWVERSION, which always returns a unique value and it not used to track date and time, even though its value is loosely derived from the current time.

But let's say you want to track changes to records.
In Access, the following ways let you set a field to a DateTime automatically:

First, you can assign =Now as the default value for a DateTime field in a table. This will assign the current time when the record is created (but will not update it automatically when the record is changed).

For recording the current DateTime whenever you make a change, you will have to program that in VBA or through Macros:

  • When going through a recordset, just update your !ModifiedDateTime or (whatever you called your field) whenever you make a change to a record.

  • When your table/query is bound to a form, you can let the form update your ModifiedDateTime field by handling the BeforeUpdate event of the form:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    ModifiedDateTime = Now
End Sub

If you use a query rather than a table to bind to the form, make sure that the field is present in the query.

In Access 2010 and later, you may also use the new Data Macro, which are the Access equivalent of triggers, to record the current date and time when a record changes.
This is less portable, but would probably be more reliable than using VBA since you don't have to remember to code it whenever you modify a record (Data Macros are handled at the ACE database driver level).

There are tons of articles on how to create audit trails in Access if that is what you are looking for.

Upvotes: 3

Related Questions