Datetime Timestamp Value Issues

[Stang70Fastback]

Hey guys. I'm new to SQL, and I can't seem to resolve this problem (and neither could my SQL friend.) I recently converted my back end to SQL, and since then I've been trying to track down things that broke, and fix them.

I'm having trouble getting timestamps to communicate properly between Access and SQL. I have a field set to a 'datetime' data type in SQL, but no matter how I structure the timestamp on the Access side, it does not seem to like the data.

The code below worked fine when the table was in Access:

    DoCmd.RunSQL "INSERT INTO ERRORLOG (ErrNum, ErrDesc, ErrProc, ErrUser, ErrTimestamp) " & _
                 "VALUES (" & TheNum & ", '" & TheDesc & "', '" & TheProc & "', '" & fOSUserName() & "', #" & Now() & "#);"

But now that the table in in SQL and using an ODBC connection, I get the following error:

Run-time error '3075':
Syntax error (missing operator) in query expression "The command or action 'Quit' isn't available now.', 'MAINWINDOW - Form_Close', 'jdoe', #1/22/2015 11:29:42 AM#);'.

In SQL, I have the column for the timestamp set as a datetime field. Any help would be appreciated!

 

[Rx_]

Just for fun, is the time stamp happen to be NOW()? When the record was created?
I just moved several of those from the programming side to the SQL Table field default value.

On your linked table to SQL, right-click and look at the deff. It will have a warning that it is read-only (since the table resides on SQL Server).
Just want to verify the data type is Date/Time

As far as the code, look this over, it might help:
http://www.access-programmers.co.uk/forums/showthread.php?t=197320

 

[pbaldy]

If there are really single quotes around Quit in the error message, the string would have an error (though I would think it would have before too).

 

[Stang70Fastback]

If there are really single quotes around Quit in the error message, the string would have an error (though I would think it would have before too).

I literally just figured this out! It seems to work now.
The reason I didn't figure it out sooner is exactly what you said - it shouldn't have worked before, either. There are several bugs that have appeared ever since the conversion that SHOULD have been bugs beforehand, but worked fine, which is making it a nightmare for me to track down the issues, lol.

Thanks!

 

[pbaldy]

Happy to help! did you get that particular error message before, or one with an apostrophe in it?