JP_TechGroup
asked on
Syntax Error in Query Expression
Ok, experts, the below query runs my logging routine for error handling.
On some errors, it works fine. On other, it crashes with a 3075. I cannot see anything wrong with the query syntax! JP-TechGroup-495674.flv
On some errors, it works fine. On other, it crashes with a 3075. I cannot see anything wrong with the query syntax! JP-TechGroup-495674.flv
strSQL = "INSERT INTO tbl_log ( err_num, err_descrip, err_source, err_module, [timestamp], terminal, t_data, on_statement, [user], signed_on, [language], state, ct2offline)"
strSQL = strSQL & "Values(" & err.Number & ", '" & err.Description & "', '" & err.Source & "', '"
strSQL = strSQL & VBE.ActiveCodePane.CodeModule & "', #" & Now() & "#, " & terminal & ", '" & data & "', "
strSQL = strSQL & TStatus(terminal).onStatement & ", '" & TStatus(terminal).user & "', " & TStatus(terminal).SignedIn & ", "
strSQL = strSQL & TStatus(terminal).Lang & ", '" & State & "', " & CT2offline & ");"
Where does the last line in the error message come from?
It looks like a repeat of the previous line.
In fact it seems like the SQL statement has been terminated with ; but then something has been added on.
It looks like a repeat of the previous line.
In fact it seems like the SQL statement has been terminated with ; but then something has been added on.
ASKER
The whole things is bizzare.
This is part of the error handler. It writes the error to a log file and then sends the user to a message stating the problem. I am using currentdb.execute
Depending on when the error occurs, there may not be a user, so sometimes there is data missing, which has never been a problem. On that note, I have seen this query fail like this when all the fields are populated.
Here is where it gets odder still. Below is a debug print of the string when it fails. It looks like the query is running 3 times.
This is part of the error handler. It writes the error to a log file and then sends the user to a message stating the problem. I am using currentdb.execute
Depending on when the error occurs, there may not be a user, so sometimes there is data missing, which has never been a problem. On that note, I have seen this query fail like this when all the fields are populated.
Here is where it gets odder still. Below is a debug print of the string when it fails. It looks like the query is running 3 times.
INSERT INTO tbl_log ( err_num, err_descrip, err_source, err_module, [timestamp], terminal, t_data, on_statement, [user], signed_on, [language], state, ct2offline)Values(2482, 'C-Tracker v2.0.3 can't find the name 'USER' you entered in the expression.', 'Project', 'Module1', #9/3/2011 11:51:05 AM#, 10, '', 3, '', True, 1, '', False);
INSERT INTO tbl_log ( err_num, err_descrip, err_source, err_module, [timestamp], terminal, t_data, on_statement, [user], signed_on, [language], state, ct2offline)Values(3075, 'Syntax error (missing operator) in query expression ''C-Tracker v2.0.3 can't find the name 'USER' you entered in the expression.', 'Project', 'Module1', #9/3/2011 11:51:05 AM#, 10, '', 3, '', True, 1, '', False);'.', 'DAO.Database', 'Module1', #9/3/2011 11:51:05 AM#, 10, '1', 3, '', True, 1, '', False);
INSERT INTO tbl_log ( err_num, err_descrip, err_source, err_module, [timestamp], terminal, t_data, on_statement, [user], signed_on, [language], state, ct2offline)Values(3075, 'Syntax error (missing operator) in query expression ''Syntax error (missing operator) in query expression ''C-Tracker v2.0.3 can't find the name 'USER' you entered in the expression.', 'Project', 'Module1', #9/3/2011 11:51:05 AM#, 10, '', 3, '', True, 1, '', False);'.', 'DAO.Database', 'Module1', #9/3/2011 11:51:05 AM#, 10, '1', 3, '', True, 1, '', False);'.', 'DAO.Database', 'Module1', #9/3/2011 11:51:05 AM#, 10, '1', 3, '', True, 1, '', False);
ASKER
Here is an example of the same query working correctly on a debug.print.
INSERT INTO tbl_log ( err_num, err_descrip, err_source, err_module, [timestamp], terminal, t_data, on_statement, [user], signed_on, [language], state, ct2offline)Values(11, 'Division by zero', 'Project', 'Module1', #9/3/2011 11:53:12 AM#, 0, '', 0, '', False, 0, '', False);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That did the trick and explains why this error kept tanking the system. This has been driving me nuts! Thank you.
How are you running this action query? If you are using:
currentdb.execute strSQl, dbfailonerror
Then add a line to your error handler (within your error log routine) that will print out the SQL string.
Is [User] a required field in your table? In the SQL string, it looks like that is an empty string.