Hi. I am using a SQL Server 2008 backend with an Access 2010 frontend. I have a comments table and an email history table, both with DateAdded and DateLastUpdated values. I wrote logic on Access form via a button with vba behind it to send an email that flags (with an *) comments added or changed since the last email was sent. The email history record is created via vba as soon as the email is sent. That vba is here:
Dim sql As String
sql = "Insert into dbo_vwEmailHistory(OrderID, EmailRecipientsTo, EmailRecipientsCC, OIEmailSent, PackagingEmailSent) " & _
" values(" & OrderID & ",'" & EmailRecipientsTo & "','" & EmailRecipientsCC & "','" & OIEmailSent & "','" & PackagingEmailSent & "')"
The comments are created/updated via a standard Access form. The Added and Last Updated dates are defaulted directly on the server using getdate() when a new record is created. The Last Updated date is updated using the BeforeUpdate event on the form when a record is changed.
My email logic checks for a comments record being updated or changed after the last email history record Sent Date. It works correctly for newly created records but it is off for records that have only been updated because the date appears to be coming from my computer and not the server, which is 3 minutes "slower". Therefore, the email is flagging previous comments as changed and displaying the * for those lines, when really those records were already flagged in a previous email. The users will be making comments changes and then immediately sending the emails from Access so the few minutes difference is a problem.
I have tested this many times and it keeps showing results in the same problem. I want to use the server date/time in all cases because other users may have different date/times set on their computers in error. Is there a way to force the Last Updated date from Access to use the server date/time? I can change the server date/time but I don't want any date/time values being based on a user's computer.
I hope I am making sense here. I am surprised by this problem as I have never noticed it before but, then again I probably haven't written logic like this that depends on the dates being so relatively accurate.
Thanks for any help you can offer.