Solved

Discrepancy between current time on local computer Access 2010 and server with SQL Server 2008 causing problems

Posted on 2012-03-16
6
303 Views
Last Modified: 2012-03-20
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 & "')"
  DoCmd.SetWarnings False
  DoCmd.RunSQL sql
  DoCmd.SetWarnings True

Open in new window


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.
Alexis
0
Comment
Question by:alexisbr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 18

Accepted Solution

by:
Cluskitt earned 250 total points
ID: 37730229
If you're having problems with the update statement and not the insert, you should post it here, so we can help you further. It's possible that you're not using GETDATE() as well (maybe concatenating with NOW()?).
In a totally unrelated application I built, what I did was query the server (SELECT GETDATE()) and store it in a date variable. Then I used that variable for all date related operations.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 37730425
I would always use SQL Server getdate() as above suggested. I think your issue is clear as you say "The Last Updated date is updated using the BeforeUpdate event on the form when a record is changed" so that would be client not server date/time and you have no control on that.
0
 

Author Comment

by:alexisbr
ID: 37730443
Thanks to both of you.  I never thought of just using a getdate from within Access.  I am working on that now and this should totally resolve my issue.  I will post back when I get it working.  I created a view in SQL Server using Getdate but there's a formatting issue.  How do you get the date?  Do you create a view in SQL Server or do you grab it from within Access?

Alexis
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 37730564
You could create a view indeed and format that inside that view - actually is better that way so the stored data will allways be independent on client locale by doing it this way:


select convert(datetime, getdate(), 101)

for more options on the datetime formats please see:

http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 

Author Comment

by:alexisbr
ID: 37731054
Thanks.  I created the view and it works fine in SQL Server but when I open the view in Access, I see #Deleted instead of the date value.  I am connecting through the ODBC.

Here's what I used to create the view.
CREATE VIEW vwServerCurrentDateTime AS select convert(datetime, getdate(), 101) as serverdatetime

Thanks,
Alexis
0
 

Author Comment

by:alexisbr
ID: 37743245
The only way I could get this to work was to create a Pass-through query in Access.

Here's the code that worked.
SELECT     CONVERT(datetime, GETDATE(), 101) AS serverdatetime

Thanks for your help.

Alexis
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question