Re: Access Frontend - Case Sensitivity

On Sun, 23 Jun 2002 13:45:33 +1000, "Howard J. Rogers" <dba_at_hjrdba.com> wrote:

>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
>

Two ideas:
- without doubt there must be a Winblows API call setting the CAPS LOCK (attach that to the appropiate events and you are done :) ) - In the past I have been having similar issues with Oracle triggers fired in an Access enviroment, in this case dealing with Oracle sequences. I don't remember the exact details anymore, but I ended up with converting the trigger code in a stored procedure, calling that using a passthrough query in Access.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sun Jun 23 2002 - 05:52:52 CDT