MS Access

Continuing from yesterday’s strangeness…

Yesterday, my hosting provider found that the mdb file’s “write and modify permissions were missing”. Very odd! but since that was fixed my ASP scripts could now INSERT.

Today I have almost a repeat of the same symptoms, except this time I can still INSERT but cannot UPDATE. The error given is the same as yesterday i.e. a syntax error in the sql. The sql works fine when executed within the access application. The error number was the same as yesterday…

-2147217900, Syntax error in UPDATE statement

I followed the advice on this forum entry to switch the connection string from an OLE DB connection string to a basic Microsoft Access Driver string. i.e.

“Driver={Microsoft Access Driver (*.mdb)};
DBQ=D:\wwwroot\domain.com\database\database.mdb”

And this fixed it! I have a load of old sites out there still working happily with OLE DB connection strings to access databases. I’m not sure why this one has been so difficult.

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.