Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access 2007 - sql syntax for insert statement with datetime stamp

Posted on 2011-02-16
7
Medium Priority
?
688 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

810 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