Solved

Access 2007 - sql syntax for insert statement with datetime stamp

Posted on 2011-02-16
7
643 Views
Last Modified: 2012-05-11
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
Comment
Question by:JosephEricDavis
  • 4
  • 2
7 Comments
 
LVL 75
ID: 34909828
Try this:

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

mx
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34909836
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
 
LVL 75
ID: 34909843
Also ... there is this execute method


CurrentDb.Execute sql, dbFailOnError

for any action query against an Access mdb.

mx
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 7

Author Comment

by:JosephEricDavis
ID: 34909880
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
 
LVL 7

Author Comment

by:JosephEricDavis
ID: 34909896
DatabaseMX

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

Does it run a method called dbFailOnError if the sql fails?
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 34910002
"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
 
LVL 75
ID: 34910107
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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question