ms access more syntax error issues that aren’t

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)};

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.

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.

a little fireworks CS3 bug

Well in my opinion incorrect user-interface behaviour rather than a bug. I just used Adobe’s report a bug form, but ironically my Safari crashed when I hit the submit button, so I’ve currently got no idea if this went forward.

The issue concerned is not new and not really a big deal, but I just had to process a pile of images and it bit me so many times I had to get it out of my system by writing about it…

The interpretation of the tab key within the properties panel for certain selected tools is in my opinion incorrect and is inconsistent with established convention.

Example 1: Marquee tool selected:

1. Set the style to Fixed Ratio or Fixed size
2. Double-click the marquee width property
3. Type with desired width.
4. Hit tab

At this point I’d expect the focus to tab across to the Marquee height property. But it doesn’t. It sets the focus on the marquee width property and selects it. I need to hit Tab a second time to get to the height property.

I’ve been caught by this so many times. It is impossible to learn to accommodate to this odd behaviour because in every other application I have ever used, tab takes focus to the next input field.

Example 2 : Eraser tool selected:

This shows more clearly the logic which has been implemented.

1. Double-click the value of the Size property
2. Type a new value
3. Hit tab

Again, instead of tabbing to the next input field (Edge) the focus remains at the Size property – value selected.

4. Double-click the Edge property
5. Type a new value
6. Hit tab

At this point instead of tabbing to the next field (Opacity), you tab back to the Size property!

In summary, it seems the implemented behaviour is that the first use of tab within a field-set of the properties panel always tabs to the first item of that field-set, the tab sequence is thereafter obeyed until you leave the field-set. I feel the behaviour should always be to tab from the current field to the next.

I’m a programmer rather than graphics dude and I use Fireworks infrequently. So many times I’ve typed a width, tab, then a height only to find I’ve just overwritten the width with the height – no big deal – but a tad annoying!

endurance (other people’s) for charity

This seems to be the month for it. Two events of endurance. Both have personal friends participating…

1. Trailwalker 2007 : 14th July 2007: A sponsored 100Km 30 hour hike in aid of Oxfam and the Gurkha Welfare Trust. Two teams from Sun Microsystems are taking part. I’m sponsoring “Team Niagra Fallouts”. More information on the event on their blog here.

2. Sponsoring the ‘puntourists’ in their “Capital to Coast Bike Ride” on 15th July 2007. Raising funds for norwood, children’s and families trust, cystic fibrosis trust and downs syndrome association. More information on their JustGiving page.

Feeling the need to get off my backside and do something worthy myself – maybe next year.