Solved

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

Posted on 2012-03-16
6
294 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
  • 3
  • 2
6 Comments
 
LVL 18

Accepted Solution

by:
Cluskitt earned 250 total points
Comment Utility
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 39

Expert Comment

by:lcohan
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now