MSAccess - Timestamp - Universal Time?

Question

I have an Access app that is shared across and run locally in US and India timezones. I need to reliably timestamp record changes. User1 and User2 may be touching records in the same dataset on opposite sides of the clock.

Users will push/pull the timestamped records from SQL server based on Timestamp that gets sent with each periodic SYNC. I made a preliminary function that simply grabbed the Now() value as the timestamp, but I can see how that will be an issue in production.

Any suggestions to handle this timestamp thing?

Elias Elias · Accepted Answer · 2013-08-16T16:39:14

Try this out

Option Compare Database
Option Explicit

Private Declare Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As TimeZoneInfo) As Long


Private Type SystemTime
    intYear As Integer
    intMonth As Integer
    intwDayOfWeek As Integer
    intDay As Integer
    intHour As Integer
    intMinute As Integer
    intSecond As Integer
    intMilliseconds As Integer
End Type


Private Type TimeZoneInfo
    lngBias As Long
    intStandardName(32) As Integer
    intStandardDate As SystemTime
    intStandardBias As Long
    intDaylightName(32) As Integer
    intDaylightDate As SystemTime
    intDaylightBias As Long
End Type

Public Function GetUTCOffset() As Date
Dim lngRet As Long
Dim udtTZI As TimeZoneInfo

lngRet = GetTimeZoneInformation(udtTZI)
GetUTCOffset = udtTZI.lngBias / 60 / 24
End Function

I found it here:

http://www.dbforums.com/microsoft-access/1000377-now-users-using-different-time-zones.html

MSAccess - Timestamp - Universal Time?

1 Answers