when is a syntax error not a syntax error

Answer: When you are doing an INSERT INTO on an Access database which is read-only.

I’ve just been doing a bit of classic ASP with MS Access back-end database. I traditionally use the RecordSet object and AddNew to insert records. But thought I’d be good on this occasion and use Jet SQL and the Command Object. Unfortuantely the script broke with error:

Microsoft JET Database Engine error ‘80040e14’
Syntax error in INSERT INTO statement.

80040e14 is a fairly generic error code, with lots of possible causes. Just give it a google!

I checked and double-checked my SQL syntax, which looked fine. Even entering it directly into the Access application showed no problem. I then wrapped the code with the On Error Next, and an error trap which reported the following variation:

Error: -2147217900 Syntax error in INSERT INTO statement.

Same description, but googling the error number revealed just a few results, this amongst them suggesting a permissions problem. Which I didn’t quite believe at first, why would the hosting provider cause the mdb file to be read-only? It is in a folder outside the webroot, if I ask them for a SQLServer account, I’m sure it will allow me to insert records!

Replacing my code with my more traditional RecordSet object, AddNew and Update commands revealed and confirmed the error…

Microsoft JET Database Engine error ‘80040e09’
Cannot update. Database or object is read-only.

If I’d stuck with my old way of doing things I’d have saved a whole load of time spent looking in the wrong places.

Posted by creacog

Product Owner/Manager; Digital Project Manager, ex-Developer Available for Product Owner roles

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.