[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Access 2007 - sql syntax for insert statement with datetime stamp

Posted on 2011-02-16
7
Medium Priority
?
684 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

656 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