Access Frontend - Case Sensitivity

Something along these lines has been posted regularly in the past. "Can Oracle be made case insensitive?" To which the reply is a perfectly satsifactory "No". (And if your name is Sybrand, followed by a swift "This is the real (case-sensitive) world!").

Anyway: I don't want Oracle to be case insensitive. I just want to make sure that my front end only sends upper case to the back end. Easier said than done when your front end is Access (and no, there's no chance at the moment of changing that!).

For each field in Access, you can set a "Format" property. Set it to ">", and the field is forced to display things in uppercase. Sod all good that does: despite what it displays, it still sends whatever you enter (ie, mixed case) to the back end before displaying it in uppercase, and that's no good.

I've tried Access' 'before insert' event - me!areacode=ucase(me!areacode). Doesn't work.

I've tried a daft "After Insert" trigger in Oracle that does a 'after insert on X update Y set areacode=upper(areacode)' -but of course, that results in a mutating table. I know I could fix that with a view and an appropriate 'instead of' trigger... but I'd rather not have to create such a view and such a trigger for each of my 20 or so lookup tables. Too much maintenance.

I've also tried a before insert trigger whose essential line reads:

:new.AREACODE := upper(:new.AREACODE);

...which works, but gives Access a heart attack (the freshly-inserted record is displayed with all fields showing "#Deleted") -and a simple requery.me refuses to display the real record with real values. Close the form and re-open it, everything's fine.

Any other suggestions? Preferably easy to code and easy to maintain. Preferably in the backend rather than the front, but also preferably something that doesn't then give Access the eebie-jeebies (like displaying "#deleted" for every field).

All help appreciated.

Regards
HJR Received on Sat Jun 22 2002 - 22:45:33 CDT