Error why would I get a 0x80040e14 error message?
This error is thrown the underlying OLE DB Provider. The actual error message is:
The command contained one or more errors
The error message this is you in the screen would vary depending on the MDAC so you have installed and the actual cause of the error.
There are a number of possible causes:
Scenario 1-syntax error in FROM clause
Scenario 2a-syntax Error in INSERT into statement
Scenario 2b-syntax error in UPDATE statement
Scenario 3a-syntax error (missing operator)-caused by ' mark
Scenario 3b-syntax error (missing operator)-incorrect delimiters
Scenario 4-syntax error-division by zero error
Scenario 1-syntax error in FROM clause
This commonly occurs if you have a table name, is a reserved word or your table name contains a space (eg "Table 1") .
Certain Words (like table, field, date, select, password, level etc) are reserved by either ADO, OLE DB or by Access for us e As commands or system objects.
You can get a list of reserved words in Access to the online help. The Microsoft Platform SDK outlines ado/oledb reserved words.
You are should never use this words as names for tables or fields nor should your have spaces in your tablenames. It is recommended this you rename your offending tables/fields and adjust your SQL statement.
If is not possible your should enclose your offending table names with [] marks, eg
SELECT field1
from [table]
Scenario 2a-syntax Error in INSERT into statement.
This commonly occurs if your field name is a reserved word (in the scenario 1 above). Adjust your field names and SQL statement accordingly and you should the avoid.
If you can ' t adjust your fieldnames can be [] marks to delimit the field names, eg
INSERT into table1
([Field], [password])
VALUES (' value1 ', ' value2 ')
Scenario 2b-syntax error in UPDATE statement.
This has the same cause as scenario 2a immediately above.
Scenario 3a-syntax Error (Missing Operator)
This is commonly caused when some value, are trying to select/update, etc contains a single quote mark. The error that you receive looks like:
Microsoft JET Database Engine (0X80040E14)
Syntax error (missing operator) in query expression ' Name = ' O ' Malleys '.
Because of the presence of the ' in ' name O ' Malleys the database engine thinks to you are constructing a WHERE clause Like
WHERE name = ' O '
and doesn ' t know what to does with the rest of the ' name (Malleys). To solve this problem your need to use the Replace () function and replace all single quotes with two single quotes. It is recommended the Replace () function above to a user-defined function and call as necessary within your Page. Click here for a example of such a function.
Scenario 3b-syntax Error (Missing Operator)-incorrect delimiters
This error can also is caused when attempting a INSERT or UPDATE SQL statement and you have used the incorrect.
field type (Access) field type (SQL Server) Delimiter
Text Char, VarChar '
Numeric any Numeric type <none>
Date/time any Date/time # (Access), ' (SQL Server)
UPDATE table1
SET Sometextfield = ' Sometextvalue ',
Somenumericfield = 5
Somedatefield = #01/01/2000#
Scenario 4-division by Zero
Dates need to is delimited with # marks when passed to the Jet Database Engine. This marks them as a date literal. Access then takes the literal date and converts it into a number. Alternatively, you can pass an integer to Access, and Access would construct a date based on that number.
However If you:
SELECT field1
From table1
WHERE field1 = 01/01/00
Access would treat this as is integer-namely 1 divided by 1 divided through 0, resulting in a division by zero error. Instead you need to write your SELECT query as:
SELECT field1
From table1
WHERE field1 = #01/01/00#
