Re: Bad query from Access stopped working

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1082012202.252813_at_yasure>...
> Randy Harris wrote:
>
> > I have an application that uses MS Access 2000 for a front end to an Oracle
> > 8.1.7.4 db. All of a sudden several pass-thru queries have stopped working.
> > They are of the form:
> >
> > SELECT .... FROM ....
> > WHERE WeekID BETWEEN '282' AND '287' ;
> >
> > WeekID is a numeric field so the values should not have been quoted. The
> > queries work if the quotes are removed.
> >
> > What puzzles me, is how they had been working right along with the quotes.
> > Was Oracle automagically doing the conversion to numeric? Nothing that I
> > can identify has changed, either on the Oracle end or the Access end. Why
> > would it stop working all of a sudden? Any insight into what might have
> > caused this change in behavior would be appreciated.
>
> Depending on implicit data conversion is something Oracle has
> warned against for a long long time. Just stop being lazy and
> use the correct data types.
>
> As to why it stopped working? Maybe someone applied a patch to the
> system or upgraded to a more recent version.

Randy, I would think the most likely cause of the problem is a change to the ODBC drivers or something used by ODBC on the client. Daniel is correct in that the datatype provided in the SQL string should match the table datatype, but obviously something changed either in the client or on the db server. Spending some time trying to find out what may be beneficial because whatever it is could be changed again at some point and I would think whoever is responsible for the change should be giving you a head's up.

HTH -- Mark D Powell -- Received on Thu Apr 15 2004 - 08:21:05 CDT