80040E14 - MS Access Syntax Error messages

This short article deals with the following common MS Access-related error messages:
  • Syntax error (missing operator) in query expression 'field='some_partial_string'
  • Syntax error in INSERT INTO statement
  • Syntax error in UPDATE statement
  • Syntax error in FROM clause
  • Syntax error in WHERE clause

There are 4 main causes for these errors: using a Reserved Word for a field name; embedded spaces in field or table names; attempting to insert unescaped single quotes; and incorrectly delimited datatypes. There is a fifth cause, and that is a genuine syntax error resulting from a typo, or otherwise misconstructed SQL statement. Assuming that you are sure this fifth cause is not applicable in your case, here's how to deal with the other four.

Reserved Words and Embedded Spaces

The most common culprits among reserved words are NAME and PASSWORD, which at first glance appear to be perfectly reasonable choices for field names in, say, a User table. What can be more confounding is that PASSWORD doesn't appear on this list of reserved words in Access. However, the fact is that in an ASP.NET application, you aren't dealing with an Access database. It's actually a Jet 4.0 database, and there is a separate list of reserved words for Jet 4.0 that does indeed include PASSWORD (but not NAME). Best advice is to familiarise yourself with both lists (and the one for Sql Server Reserved Words too) and avoid using any of them as a habit. However, if you can't change your field names, you will have to srround them with [ ] brackets:

Select [name], [password], emailaddress From Users

The same resolution applies to embedded spaces in field names: change them or surround them in [ ] brackets:

Select [user name], userpassword, [email address] From Users

When using the Query Builder within Visual Studio or Visual Web Developer, you will find that all field names are surrounded by brackets by default.

Unescaped Single Quotes and Incorrect Datatype Delimiters

The third and fourth causes of these errors will be totally resolved if you use parameters in your code. Single quotes act as string delimiters in Jet SQL, so when you try to pass a vlaue that contains a quote, such as a name like O'Brien, or a piece of text like "It's a lovely day", the Jet engine baulks and throws an error. It thinks that the apostrophe or single quote is telling it that the string value to be passed has ended, and anything afterwards should be treated as legitimate SQL.

In the same way that single quotes delimit string values, other datatypes also have their own delimiters:

text/string single quote '
numeric none
datetime single quote or octothorpe ' or #

If you get these wrong, a Syntax error will be thrown. Paramterised queries solve the problem, because the Jet engine infers the datatype from the database field, and treats the value being passed in the correct way. This means that things like "O'Brien" are treated as literal strings. You will see advice from some people recommending that you use some form of string.Replace() method to double any single quotes, which works by escaping them. While this works, it's a poor idea.

Date Posted: Thursday, May 1, 2008 11:06 AM
Last Updated: Saturday, May 22, 2010 7:49 PM
Posted by: Mikesdotnetting
Total Views to date: 25040

6 Comments

Wednesday, January 21, 2009 3:05 PM - Simbarashe

Hie Mike

I got to change my columnname for my database where I had used password. The first insert worked fine but on attempting to insert another record it simply gave me the same error again.

Is there a solution to this before I dump Dreamweaver for Visual Web Developer, which I already have, reason I am not using it is I am still waiting for my MCST study packs which are stuck in the States.

Thanks so far

Wednesday, January 21, 2009 8:46 PM - Mike

@Simbarashe

It's impossible for me to guess why you get the same error again without knowing your SQL and the values you are trying to insert.

One thing is for sure, while VWD is a far superior product for ASP.NET development than DW, this error is caused by your code, not the tool you are using.

Wednesday, May 6, 2009 11:42 PM - peter

Hi,

We recently installed MS Access on a visa machine. The program we have works fine on 5 other machines with a range of office 2007 and 2003 and vista and XP. The software works fine.

There is an error that comes up -2147217900 (80040e14). The syntax etc is fine, the error is persistent. I have had the problem once before and found a solution on the net, this time I cannot find it. I think it was something to do with registering activex components.

Hope you can help.

Peter

Thursday, May 7, 2009 8:05 PM - Mike

@Peter,

If you are talking about an Access application, I can't help you. I have only ever used Access within a web context

Monday, September 14, 2009 9:41 AM - Jigisha

i have error in insert data in access database with vb.net code
the error is
Syntex error in insert into statement
how to solve this problem?
Help me
Thanks

Monday, September 14, 2009 7:30 PM - Mike

@Jigisha

Yes I can help you. I've written a whole article that explains what you need to do. Oh look! There it is above you!
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Recent Comments

Gautam 11/20/2014 8:01 AM
In response to I'm Writing A Book On WebMatrix
Hello Mike, I read your book, loved it! However, I have a few request/suggestions: 1) an example...

Bret Dev 11/19/2014 8:39 PM
In response to The Difference Between @Helpers and @Functions In WebMatrix
Excellent post! One concern - where can you place global @Functions code within an MVC project to Is...

Rob Farquharson 11/19/2014 4:28 PM
In response to iTextSharp - Links and Bookmarks
How can I place text at an absolute position on the page? Also, how can I rotate text?...

Andy 11/17/2014 8:08 PM
In response to MVC 5 with EF 6 in Visual Basic - Sorting, Filtering and Paging
Hello I'm testing your sorting instructions above. This is great and I was able to get it to work...

Gautam 11/17/2014 5:51 PM
In response to WebMatrix - Database Helpers for IN Clauses
Hi Mike, I am very new to programming: In the above example if I want to use a delete button the...

donramon 11/17/2014 3:22 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Congratulations on your new website look and the excellent articles. Thank you!...

Gautam 11/17/2014 11:26 AM
In response to Looking At The WebMatrix WebGrid
Hi Mike, I add the jquery script at the end of my html file.. when ajax attribute is added to the be...

Chet Ripley 11/15/2014 6:57 PM
In response to Adding A New Field
It appears the command is case sensitive. I had the same issue as Cameron. When I changed the to it...

Alvin 11/14/2014 12:49 PM
In response to Razor Web Pages E-Commerce - Adding A Shopping Cart To The Bakery Template Site
Great article Mike! When do you plan to extend the bakery shopping cart beyond this point?...

Gautam 11/14/2014 10:16 AM
In response to Web Pages - Efficient Paging Without The WebGrid
to get the count can we use only the below sql, why to join category and author table var sql =...