Trouble with Date and Time SQL Passthrough Update

[Joey]

Hi All,

I'm having problems updating a date/time field for a record on a table
on a SQL Server via an SQL Passthrough statement. I set my statement to
this

strSQL = "UPDATE tbl SET dtm_updated = #" & Now & "# WHERE int_tbl_id =
12345;"

And I get an ODBC Call Failed Error.

It works if I set the dtm_update = NULL so I'm wondering if there is
some date/time conversion issue with Access97 and SQL Server. How and
what format do I need to convert the date/time so that I can update the
SQL Server table?

Thanks,
Joey.

 

[Douglas J. Steele]

SQL Server doesn't use # to delimit dates: it uses '.

Try:

strSQL = "UPDATE tbl SET dtm_updated = '" & Format(Now, "yyyy-mm-dd
hh:nn:ss") & "' WHERE int_tbl_id = 12345;"