• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 696
  • Last Modified:

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
0
JosephEricDavis
Asked:
JosephEricDavis
  • 4
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try this:

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

mx
0
 
cyberkiwiCommented:
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

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Also ... there is this execute method


CurrentDb.Execute sql, dbFailOnError

for any action query against an Access mdb.

mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
JosephEricDavisAuthor Commented:
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')
0
 
JosephEricDavisAuthor Commented:
DatabaseMX

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

Does it run a method called dbFailOnError if the sql fails?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"what exactly does the CurrentDb.Execute sql, dbFailOnError do?"
It runs an Access action query (update, delete, make table, append)

So ... it executes your SQL.  dbFailOnError generates a trappable error if the Execute encounters an error, which you would handle in a error trap.

And try this:

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


mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now