Re: Exporting ms-access 2000 tables with Yes/No fields to Oracle 8.1.6

From: dbaz <dbaz_at_email.com>
Date: Wed, 06 Sep 2000 03:30:09 GMT
Message-ID: <5Tit5.660$yY.47623@news.flash.net>

Another solution, which will work with Office2k, 97 and 95 would be to export using a query
which contains a calculated field to "convert" the true/false (boolean) value to something Oracle
won't choke on.

For example:

We have a table:         [Table1]
Containing fields:             [fldKEY] a text field
                                       [fldBOOL] the boolean (y/n) field in

question

The following statement:

        SELECT [Table1].[fldKEY], IIf([Table1].[fldBOOL],"Y","N") AS fldBoolX FROM Table1;

Will convert the resulting boolean to a "Y" or "N".

The POWER of IIF ???

This is NOT a "problem" with Access, it simply a non-standard data type (like Access Date fields)
that Oracle cannot convert. No big deal.

I couldn't possibly conceive why Microsoft would expect other databases to understand it's
quirky boolean data type.

They ARE rather handy though, just like the date/time data type...

Hope this helps.

dbaz

"Huw G" <hgallon_at_openlinksw.co.uk> wrote in message news:39abe525$1_1_at_nnrp1.news.uk.psi.net...
>
> upton wrote in message ...
> >
> >
> >If you use the SQL Loader in Oracle 8 you can write a comparison and
> >assignment modifier to the target table to fit the Oracle table you're
> >populating. (I.E. check for the yes/no type and change it to VARCHAR2(3)
 or
> >VARCHAR2(1) whichever you prefer) check the docs for more detail and
> >examples.
> >
> >
> >~Ryan
> >
> Thanks for this info. However, one of the test requirements was that the
> user should be able to export from Access without recourse to Oracle to
> define the table and fields beforehand.
>
> Trawling http://support.microsoft.com/support/kb/articles/Q253/1/77.ASP
> brings up the admission that this is a problem with Access. Another query
 I
> had, regarding currency formats, is also admitted to be a problem). The
> solution is to obtain MS-Office Service Release 1/1a ... all 128Mb of it !
>
> If it is Micro$oft's fault, I am off the hook !
>
> Huw
>
>
>
Received on Tue Sep 05 2000 - 22:30:09 CDT