?
Solved

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

Posted on 2012-03-16
6
Medium Priority
?
311 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 1000 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 1000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

743 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