VBA Update Query in Access 2016 to update SQL Server.

[cage4000]

HI Everyone,

Does anyone have any sugestions on how to update a date/time fuild on SQL server using access VBA. Here is what i have:

Dim updTblPrf As String

updTblPrf = "UPDATE tblPRF " _
            & "Set PRStatus = 'Complete', TimeStamp = convert(datetime, '" & Now() & "'), AsscName = '" & [Forms]![Switchboard]![txtLogin] & "' " _
            & "WHERE PRF_ID = " & Me.PRFNum & ";"


sqlDB().Execute updTblPrf, dbSeeChanges

If i take the ( TimeStamp = convert(datetime, '05/12/2018'), ) out of it, the update query runs fine so i know that the communication to the SQL server and the query structure is sound.


I have tried following different variations of this code:

TimeStamp = 05/12/2018

TimeStamp = '05/12/2018'

TimeStamp = #05/12/2018#

TimeStamp = convert(datetime, '05/12/2018')

TimeStamp = CONVERT(DATETIME, '2018-05-12 00:00:00', 102)

Any suggestions on how to make this work?

 

[cage4000]

I also tried this:

TimeStamp = # " & FormatDateTime(Me.ADJ_DATE, vbGeneralDate) & " #

but it did not work as well.

 

[cage4000]

Also, to help provide more info on this issue, if i run ether of the following SQL statements directly in my SQL server, it will update with no problems:

UPDATE       tblPRF
SET                PRStatus = N'com', AsscName = N'dav', TimeStamp = '05/12/18'
WHERE        (PRF_ID = 62713)

UPDATE       tblPRF
SET                PRStatus = N'com', AsscName = N'dav', TimeStamp = CONVERT(DATETIME, '2018-03-12 00:00:00', 102)
WHERE        (PRF_ID = 62713)

 

[cage4000]

I also tried:

TimeStamp = CDate('" & Now() & "')

but it did not work.

 

[James Dickinson]

Hi,

not 100% but try
[Timestamp] = FORMAT(Now(), "yyyy/mm/dd hh:mm:ss") - or however you want to format this

 

[isladogs]

Just to clarify, are you describing a SQL field called Timestamp which is a datetime datatype ... or a field where you've used the timestamp datatype.

If the latter then despite its name its not a datetime field - its binary data

 

[cage4000]

Hi Ridders, yes this is a SQL field with a datetime datatype.

James, your recommendation worked flawlessly. thank you both for your help.

 

[isladogs]

For info, datetime fields in SQL Server actually use yyyy-mm-dd hh:nn:ss format