Link to home
Create AccountLog in
Avatar of JosephEricDavis
JosephEricDavis

asked on

Access 2007 - sql syntax for insert statement with datetime stamp

I've got an access table with the following schema...

ErrorLogID - AutoNumber
ErrorMessage - Memo
TimeStamp - Date/Time
Location - Text

I'm trying to run an insert statement against this table with the following VBA code...

Public Sub Error_Report(Msg As String, location As String)
    Dim sql As String
    sql = "INSERT INTO tbErrorLog (ErrorMessage, TimeStamp, Location) VALUES ('" + Common.PrepSqlString(Msg) + "', '" + CStr(Now) + "', '" + Common.PrepSqlString(location) + "')"
    Common.ExecuteNonQuery (sql)
End Sub

This code produces the following sql for the insert statement.

INSERT INTO tbErrorLog (ErrorMessage, TimeStamp, Location) VALUES ('Testing', '2/16/2011 1:00:34 PM', 'Test')

I'm getting an error message saying there is incorrect syntax on the insert statement.  I know that my Common.ExecuteNonQuery method is working correctly.  What is wrong with my sql syntax for this insert statement.

Thanks
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Try this:

INSERT INTO tbErrorLog (ErrorMessage, TimeStamp, Location) VALUES ("Testing", #2/16/2011 1:00:34 PM#, "Test")

mx
Use # for dates, not single quotes

Public Sub Error_Report(Msg As String, location As String)
    Dim sql As String
    sql = "INSERT INTO tbErrorLog (ErrorMessage, TimeStamp, Location) VALUES ('" + Common.PrepSqlString(Msg) + "', #" + CStr(Now) + "#, '" + Common.PrepSqlString(location) + "')"
    Common.ExecuteNonQuery (sql)
End Sub

Open in new window

Also ... there is this execute method


CurrentDb.Execute sql, dbFailOnError

for any action query against an Access mdb.

mx
Avatar of JosephEricDavis
JosephEricDavis

ASKER

This statement is still giving me a sql syntax error

INSERT INTO tbErrorLog (ErrorMessage, TimeStamp, Location) VALUES ('Testing', #2/16/2011 1:00:34 PM#, 'Test')
DatabaseMX

what exactly does the CurrentDb.Execute sql, dbFailOnError do?

Does it run a method called dbFailOnError if the sql fails?
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Technically what dbFailOnError does is ... (basically from Help) Rollback updates if an error occurs.  If you were using Transactions (BeginTrans, EndTrans, Rollback) ... if you do not include the dbFailOnError option, then the Rollback does not occur ... when an error occurs.

mx