Case Sensitivity In Queries Linked to Oracle Database

  • Thread starter Thread starter mcl
  • Start date Start date
M

mcl

Some time ago I asked if there was a way to get around the fact that queries
became case sensitive when linked to oracle tables.
No one answered so I assumed the answer was no. The other day I stumbled
across the strconv function. Got it out of a PC Mag article on excel macros.
Tried it and it works great. So none of you so called access gurus could
think of that?
 
mcl said:
Some time ago I asked if there was a way to get around the fact that
queries became case sensitive when linked to oracle tables.
No one answered so I assumed the answer was no. The other day I
stumbled across the strconv function. Got it out of a PC Mag article
on excel macros. Tried it and it works great. So none of you so
called access gurus could think of that?
Click to expand...

There are a few ways around that, but they are all very inefficient as you
lose the ability to use indexes on the field that the criteria is being
applied to. The most common is to use UCase(), but StrConv() will work as
well.
 
I don't remember seeing your original post, but you should consider that the
problem arose from the fact that the default of some later versions of the
Oracle ODBC drivers were case sensitive. Here's a quote from Oracle's
documentation:

"SQL_ATTR_METADATA_ID attribute. This attribute is an ODBC 3.0 feature which
is used to specify case sensitivity on arguments of the ODBC catalog
functions. For example, if SQL_ATTR_METADATA_ID is set to SQL_TRUE then the
case of the TableName argument of the function SQLTables is not significant.
The default value, as defined by the Microsoft ODBC specification, is
SQL_FALSE (case sensitive)."


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html
 
I'm confused. When I saw this I asked our IT people if this was a way to
solve the problem for all. Most here access our oracle tables with using
exceed sessions and sql. I hate sql (you will never find a lousy typist who
likes unix or sql). I found MS Access as a great go-between. There are
actually many here who like sql????? However, many don't like the case
sensitivity of unix/oracle tables. When I asked our IT people about
SQL_ATTR_METADATA_ID they said that was a MS Access thing. So what is the
story of this SQL_ATTR_METADATA_ID attribute and setting it to SQL_TRUE ?
How is it used? Where would it be used (examples)? If it's for use with ODBC
only where? I set my system to use ODBC through Control Panel-Administrative
Tools-Data Sources (ODBC). This is Windows 2000. Right now I'm using the
oracle driver which I believe is how most do. It's what I was shown and how
I've shown others.
 
Back
Top