0

I'm running MS Access 2007 front end with an SQL 2014 server back end.

I've got a timekeeping system that tracks time for employees. Employees are able to use multiple computers to sign in/out and keep track of their time and is used for payroll purposes so it's pretty important to make sure it is working correctly.

We've run into an issue where a computer's date and time got messed up. This caused all sorts of problems with payroll. The ideal solution would be to always use the server's date/time so we have one gold standard to keep track of time.

Basically when a record is created, it takes the server timestamp as "Time Started". When the user tracks their time the "Time Ended" field is filled in.

Is there some way to get the server to fill in the timestamp without using the Now() function in access or query the server date/time so I can use that to populate the record? The Now() function uses the current system date and time and the cause of this problem.

EDIT 1:

See below for update query

DoCmd.RunSQL "UPDATE dbo_tbl_Timekeeping SET fld_End_Time = Now() WHERE fld_Employee_ID = " & Me.EmployeeID

The problem here is that Access does not recognize functions such as GETDATE() it tells me that the function is undefined (See this link)

timroberts
  • 173
  • 13
  • Showing us your existing code will greatly help us help you. SQL Server has plenty of way of getting the time; `GETDATE()`,`SYSDATETIME()`,`SYSDATETIMEUTC()`,`SYSDATETIMEOFFSET()`,`CURRENT_TIMESTAMP`.... – Thom A Oct 29 '20 at 15:12
  • Point of clarification: The question should be: Is there some way to fill in the "Time Ended" field with the server time since the field starts as null and is just being updated? – timroberts Oct 29 '20 at 15:12
  • So use the computer time for the Start Time, and the server time for time end? Well, I'm changing my PC's clock to be 7 hours ago, and then I'll be signing out immediately afterwards. :) – Thom A Oct 29 '20 at 15:14
  • The idea is that that server is handling both times... start and end – timroberts Oct 29 '20 at 15:19

1 Answers1

0

Use a Pass-Through query for this. Doc link
There you can use all SQL Server functions.

e.g. with a saved PT query as shown here:

With CurrentDb.QueryDefs("MyPass")
  .SQL = "UPDATE dbo.tbl_Timekeeping SET fld_End_Time = GETDATE() WHERE fld_Employee_ID = " & Me.EmployeeID
  .Execute
End With

Note to not use the linked table name (dbo_...) but the name as shown in SQL Server (dbo. ...).

Or create it on the fly as shown here
but to run an action query you must set

qdf.ReturnsRecords = False
Andre
  • 26,751
  • 7
  • 36
  • 80
  • This worked perfectly! I used the passthrough query designer in Access to create the query and just executed it with VBA, but the passthrough query was exactly what i needed. Thank you! – timroberts Oct 29 '20 at 18:33