Re: Access and Boolean - Mailing list pgsql-general

Hello Andrew,

Thanks for your advise, comments inline.

> I just created a table with a boolean field in
> PostgreSQL and created a form in MS Access with which
> to edit it.  The form allowed me to enter the
> following characters with the following results:
>
> entry     result
> 1         1
> 0         0
> t         1
> f         0
> y         1
> n         0

I've not yet tried this, but I think this should be OK.

> I am using PostgreSQL 7.1.3 on FreeBSD 4.4 and MS
> Access 97 on Windows 98.  My PostgreSQL ODBC driver is
> set to "Use Declare/Fetch".  (I don't know if that
> matters.)

I have: PostgreSQL version string = 'PostgreSQL 7.0.2 on i686-pc-
linux-gnu, compiled by gcc 2.96' with Access97 and PostgreSQL ODBC
driver v7.01.00.07 and USEDECLAREFETCH=0.
I don't know either if the last item is relevant.

> Perhap you could use a combo box in your Access form
> to offer values acceptable to PostgreSQL.  This should
> be a simple form edit.

This is the main-problem: it's a checkbox, with either true or false
as values, but MS represents a true as -1. Why they then name it a
boolean value is beyond my poor brain. I always learned, that Boolean
values only can be 0 or 1. If this wouldn't be the case, could I have
types this? I mean computing is all about 0's and 1's!?

> When you "imported" Access tables into PostgreSQL, how
> did you do it?  Perhaps the surest method of
> conversion is to manually create the tables in
> PostgreSQL and then use an Append query in MS Access
> to move the data.  This is less efficient than
> exporting a table via ODBC; but the results might be a
> little better.

I was aware of these problems and used the pgupt from Steve Boyle,
with which I created the sql statements and the data. Then copied to
the Linux machine and tried executing loadd.sh. Things went miserably
and I knew why. Copying from Windows to Unix preserves the crlf, so
dragged everything through sed, yielding only lf. Now I could import
correctly all data, including some functions which ensured
referential integrity, but upon testing some tables didn't return
values? Recreating the database without those went okay.

> Have you reviewed the boolean data that was moved to
> PostgreSQL to make sure it went across okay?

Yep, they are perfect, everywhere 0's and 1's if I go through
pgaccess in a Xwindows session and other values are not allowed while
editing, so they are (real) Booleans.

One remark: if there already is a checked box in the form and I
rightclick on it while copying, then put the mousecursor on an
unchecked box and perform a rightclick paste, there is the check!
Looking up the table through pgaccess then shows the right values.

Hauke de Vries

> Best of luck,
>
> Andrew Gould
>
> --- Hauke de Vries <H.de.Vries@philos.rug.nl> wrote:
> > I've imported some Access tables into PostgreSQL and
> > Would like to
> > use Access as frontend with linked tables.
> >
> > For modifying, updating, appending we use a form.
> > Some fields have
> > Boolean values (default 0). In Access this is
> > expressed as '=No'.
> > If we want it marked, i.e. set 'field' to 1, Access
> > returns "The
> > field is too small to accept the amount of data you
> > are trying to
> > add. Try inserting or paste less data."
> >
> > This is a remarkable 'feature' of Access returning
> > -1 upon clicking.
> > How to circumvent this behaviour?
> >
> > Hauke de Vries
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the
> > unregister command
> >     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
>
> __________________________________________________
> Do You Yahoo!?
> Send your FREE holiday greetings online!
> http://greetings.yahoo.com
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 1: subscribe and unsubscribe
> commands go to majordomo@postgresql.org

---
Hauke de Vries
Fakulteit der Wijsbegeerte
RijksUniversiteit Groningen