Syntax Error in Code

[Stang70Fastback]

Hey guys. 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 posted this in the SQL subforum, but I think it's actually a VBA issue, so I'm putting it here too (this subforum is also much more active.)

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 a syntax error.

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

This might be related, but even on forms where I'm not using an INSERT INTO statement, my timestamps still act odd. One form has a textbox with a default value of "Now()" which worked fine when the back end was in Access. Now that the back end is in SQL, that textbox simply displays #error, and in the SQL database the value remains null.

 

[gemma-the-husky]

not 100% sure, but I think a timestamp is an automatic sql feature, rather than a specific mapping to an access data time value. I think the SQL value function for a datestamp is different from "now()"

I think you just need a normal datetime field, if you want to populate manually.

 

[TJPoorman]

lose the ampersands around NOW()

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

In SQL server the proper syntax for dates is simply single quotes ''

 

[pbaldy]

I believe this was resolved here:

http://www.access-programmers.co.uk/forums/showthread.php?t=273880

 

[pbaldy]

In SQL server the proper syntax for dates is simply single quotes ''

True, but in an Access query against a SQL Server table you'd still use #. That said, your suggestion to not concatenate Now() into the string should also work, since Access would understand it.

 

[TJPoorman]

True, but in an Access query against a SQL Server table you'd still use #. That said, your suggestion to not concatenate Now() into the string should also work, since Access would understand it.

My mistake. I program in both C# and Access with SQL simultaneously and got confused between the two.

 

[pbaldy]

No worries, I just wanted to clarify. I get confused and I don't even have the excuse of using C#.

 

[Stang70Fastback]

Crap, I'm sorry guys. Forgot to state that it was indeed solved. Turns out the other subforum was indeed the place to post it. Thanks for the help though! I'm slowly learning!