Checkboxes on MSAccess and PostgreSQL

Valerio Santinelli wrote:

First of all.. thanks to everybody for helping me out witht the
"sequence" stuff. :)

I've got another questions for you dudes. When using a CheckBox in a
Form written in MSAccess that's related to a table in a
PostgreSQL database, if I simply turn its status from ON to OFF it works
fine, while if i'm doing the opposite it won't work.

I noticed that when exporting the table from MSAccess to PostgreSQL the
"yes/no" fields all became char(1) and not boolean.. maybe this could be
the problem.

I also noticed from the logs that when updating the status from ON to
OFF the UPDATE goes out with something like field_name='0'
I'm not sure it should use the "'" since it's more like a numerical, no
?

Try setting the Advanced Driver Option "Bools as Char" to disabled. This
will cause the export to use a Boolean field in postgres.

Byron

Hi,
I'm pretty new to postgresql, but am reasonably familiar with MSAccess, and I
see here that Valerio can export tables from Access to pgsql. How is this done?
And using forms from Access related to pgsql databases?
This could be invaluable. While I'm at it, does anyone know where there might
be some sample code for a shopping cart using pgsql as the database and php3?

Thanks for any info, especialy on getting msaccess and postgresql working
together.

Robert

Valerio Santinelli wrote:

First of all.. thanks to everybody for helping me out witht the
"sequence" stuff. :)

I've got another questions for you dudes. When using a CheckBox in a
Form written in MSAccess that's related to a table in a
PostgreSQL database, if I simply turn its status from ON to OFF it works
fine, while if i'm doing the opposite it won't work.

I noticed that when exporting the table from MSAccess to PostgreSQL the
"yes/no" fields all became char(1) and not boolean.. maybe this could be
the problem.

I also noticed from the logs that when updating the status from ON to
OFF the UPDATE goes out with something like field_name='0'
I'm not sure it should use the "'" since it's more like a numerical, no
?

Thanks again,

Valerio Santinelli
tanis@mediacom.it

--
http://www.chalmers.com.au. Publications From China in 24 different languages.
English, French, German, Russian, Arabic, Spanish, Chinese, Burmese, Bengali,
Hindi, Indonesian, Italian, Japanese, Korean, Portuguese, Persian, Swahili,
Sinhalese, Thai, Tamil, Urdu, Vietnamese. China Books for CIBTC, Beijing.

Robert Chalmers wrote:

Hi,
I'm pretty new to postgresql, but am reasonably familiar with MSAccess, and I
see here that Valerio can export tables from Access to pgsql. How is this done?
And using forms from Access related to pgsql databases?

You need ODBC driver for PostgreSQL. (See
http://www.insightdist.com/psqlodbc) Once you connect to the backend everything
is more or less the same. There are, however, many little considerations with
regard to performance and locking. But, it doesn't take to much to get
started.

Robert Chalmers wrote:

Hi,
I'm pretty new to postgresql, but am reasonably familiar with MSAccess, and I
see here that Valerio can export tables from Access to pgsql. How is this done?
And using forms from Access related to pgsql databases?
This could be invaluable. While I'm at it, does anyone know where there might
be some sample code for a shopping cart using pgsql as the database and php3?

Thanks for any info, especialy on getting msaccess and postgresql working
together.

Well.. I'm in a hurry so I'll be quick and concise.

To access the PostgreSQL database from a win32 machine you can simply install the
ODBC driver (there's a link on www.postgresql.org) and then you've got to configure
your database on the linux machine to accept calls through TCP/IP from other
machines. The way to do that is fully explained in the postgresql manual, but if
you need more help on the topic just ask and I'll try to explain everything in a
better way as soon as I get some time to write a full e-mail ;)

After you configured your database on the linux box, just create a new dataabse in
MSAccess and "connect external tables.." and tell him that you're using an ODBC
database. You'll be prompted to enter some details about the database, expecially
the IP address of the machine the database resides on and its name, then if
everything goes fine you'll end up with a list of the tables contained in your
postgresql database.

Have fun! :)

--

C'ya!

Valerio Santinelli a.k.a. TANiS
[tanis@mediacom.it]+:+[http://www.mediacom.it/~tanis]

Great,
Thanks chaps. I have it working. Now just have to get used to it so I am not looking up
the instructions every time I make a move.

I can load tables, and create tables in an existing database - which I have running on
the server, and probably even update the data.. but is it possible to create a database
on the server from Access? That way, I could do all the creation and updating from the
Access machine. It's not a major headache anyway. Indeed no problem at all, as I can
obviously just go to Unix, create a database, then back to Access to populate it and
update it.

Anyway, thanks for the pointers. Saved me HOURS AND HOURS ....

Best regards
Robert

Valerio Santinelli wrote:

Robert Chalmers wrote:

Hi,
I'm pretty new to postgresql, but am reasonably familiar with MSAccess, and I
see here that Valerio can export tables from Access to pgsql. How is this done?
And using forms from Access related to pgsql databases?
This could be invaluable. While I'm at it, does anyone know where there might
be some sample code for a shopping cart using pgsql as the database and php3?

Thanks for any info, especialy on getting msaccess and postgresql working
together.

Well.. I'm in a hurry so I'll be quick and concise.

To access the PostgreSQL database from a win32 machine you can simply install the
ODBC driver (there's a link on www.postgresql.org) and then you've got to configure
your database on the linux machine to accept calls through TCP/IP from other
machines. The way to do that is fully explained in the postgresql manual, but if
you need more help on the topic just ask and I'll try to explain everything in a
better way as soon as I get some time to write a full e-mail ;)

After you configured your database on the linux box, just create a new dataabse in
MSAccess and "connect external tables.." and tell him that you're using an ODBC
database. You'll be prompted to enter some details about the database, expecially
the IP address of the machine the database resides on and its name, then if
everything goes fine you'll end up with a list of the tables contained in your
postgresql database.

Have fun! :)

--

C'ya!

Valerio Santinelli a.k.a. TANiS
[tanis@mediacom.it]+:+[http://www.mediacom.it/~tanis]

--
http://www.chalmers.com.au. Publications From China in 24 different languages.
English, French, German, Russian, Arabic, Spanish, Chinese, Burmese, Bengali,
Hindi, Indonesian, Italian, Japanese, Korean, Portuguese, Persian, Swahili,
Sinhalese, Thai, Tamil, Urdu, Vietnamese. China Books for CIBTC, Beijing.

I have not tried this one myself, but I believe almost any statement
recognized by the backend will work in a "Pass-Through Query".

1. Open a new empty query in design mode.

2. Select Query|SQL Special| Pass-Through.

3. Enter "CREATE DATABASE dbname"

4. Run the query.

5. Configure your new data source (DSN) on the PC.

This method may be more trouble then it is worth, but it should work.

Sorry, I can't try it here at home. I only have a single dual boot
machine.

----- Original Message -----
From: Robert Chalmers <robert@chalmers.com.au>
To: Valerio Santinelli <tanis@mediacom.it>
Cc: <pgsql-general@postgreSQL.org>; <daveh@insightdist.com>
Sent: Monday, January 18, 1999 8:44 PM
Subject: Re: Was...[GENERAL] Checkboxes on MSAccess and PostgreSQL

Great,
Thanks chaps. I have it working. Now just have to get used to it so I am

not looking up

the instructions every time I make a move.

I can load tables, and create tables in an existing database - which I have

running on

the server, and probably even update the data.. but is it possible to

create a database

on the server from Access? That way, I could do all the creation and

updating from the

Access machine. It's not a major headache anyway. Indeed no problem at all,

as I can

obviously just go to Unix, create a database, then back to Access to

populate it and

Jose' Soares wrote:

Valerio Santinelli wrote:

First of all.. thanks to everybody for helping me out witht the
"sequence" stuff. :)

I've got another questions for you dudes. When using a CheckBox in a
Form written in MSAccess that's related to a table in a
PostgreSQL database, if I simply turn its status from ON to OFF it works
fine, while if i'm doing the opposite it won't work.

I noticed that when exporting the table from MSAccess to PostgreSQL the
"yes/no" fields all became char(1) and not boolean.. maybe this could be
the problem.

I also noticed from the logs that when updating the status from ON to
OFF the UPDATE goes out with something like field_name='0'
I'm not sure it should use the "'" since it's more like a numerical, no
?

Thanks again,

Valerio Santinelli
tanis@mediacom.it

You have to uncheck "Bools as Char" on Advanced options Driver on ODBC
Data Source Administrator
and then you have to create an operator for bool=int4 like this:

/* M$Access tratta il valore booleano come un intero 0=false o -1=true
mentre invece PostgreSQL lo tratta come una stringa:
'true','t','1','y','yes','false','f','0','n','no'
Questo script crea l'operatore = e implicitamente <> per bool e int4
per compatibilita' con M$Access.
*/
drop operator = (bool,int4);
drop function MsAccessBool(bool,int4);

create function MsAccessBool(bool,int4)
returns bool
as ''
language 'internal';

create operator = (
leftarg=bool,
rightarg=int4,
procedure=MsAccessBool,
commutator='=',
negator='<>',
restrict=eqsel,
join=eqjoinsel
);

Some time ago I got this message and I tried to make booleans work with my
database, but there's been no way to do that. First of all, during the
"DROP MsAccessBool" my PostgreSQL database engine reports that the function
can't be dropped because it's an internal (built-in).
Second, it seems to replace it by creating the function (i don't know how it
could if the function already exists..) but then again when I'm updating a
record in a table with booleans it simply doesn't let me do that. I'm
desperately seeking help now.. I don't really know how to solve this problem.

Thanks in advance

Valerio Santinelli
tanis@mediacom.it

I defined my Access boolean fields as int2 in Postgres. This, in
combination with removing the "Bools as Char" flag in the ODBC driver
appears to work. However, I have not been able to much testing.

-----Original Message-----
From: Valerio Santinelli [SMTP:tanis@mediacom.it]
Sent: Wednesday, February 24, 1999 4:25 AM
To: sferac@bo.nettuno.it
Cc: pgsql-interfaces@postgreSQL.org;
pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Checkboxes on MSAccess and PostgreSQL

Jose' Soares wrote:

Valerio Santinelli wrote:

First of all.. thanks to everybody for helping me out witht the
"sequence" stuff. :)

I've got another questions for you dudes. When using a CheckBox

in a

Form written in MSAccess that's related to a table in a
PostgreSQL database, if I simply turn its status from ON to OFF

it works

fine, while if i'm doing the opposite it won't work.

I noticed that when exporting the table from MSAccess to

PostgreSQL the

"yes/no" fields all became char(1) and not boolean.. maybe this

could be

the problem.

I also noticed from the logs that when updating the status from

ON to

OFF the UPDATE goes out with something like field_name='0'
I'm not sure it should use the "'" since it's more like a

numerical, no

?

Thanks again,

Valerio Santinelli
tanis@mediacom.it

You have to uncheck "Bools as Char" on Advanced options Driver on

ODBC

Data Source Administrator
and then you have to create an operator for bool=int4 like this:

/* M$Access tratta il valore booleano come un intero 0=false o

-1=true

mentre invece PostgreSQL lo tratta come una stringa:
'true','t','1','y','yes','false','f','0','n','no'
Questo script crea l'operatore = e implicitamente <> per bool e

int4

per compatibilita' con M$Access.
*/
drop operator = (bool,int4);
drop function MsAccessBool(bool,int4);

create function MsAccessBool(bool,int4)
returns bool
as ''
language 'internal';

create operator = (
leftarg=bool,
rightarg=int4,
procedure=MsAccessBool,
commutator='=',
negator='<>',
restrict=eqsel,
join=eqjoinsel
);

Some time ago I got this message and I tried to make booleans work
with my
database, but there's been no way to do that. First of all, during
the
"DROP MsAccessBool" my PostgreSQL database engine reports that the
function
can't be dropped because it's an internal (built-in).
Second, it seems to replace it by creating the function (i don't
know how it
could if the function already exists..) but then again when I'm
updating a
record in a table with booleans it simply doesn't let me do that.
I'm
desperately seeking help now.. I don't really know how to solve this
problem.

Thanks in advance

Valerio Santinelli
tanis@mediacom.it

That's the same thing I did today to get rid of the boolean problem. Now I've
got another one ;)
I want to use my data contained in the table filled with boolean stuff and I'd
like to convert all the booleans to int2. I know there's a way to do that by
doing something like:

INSERT INTO mynewtable SELECT field1, field2, (booleanfield='t'), field3,...
FROM myoldtable;

but "(booleanfield='t')" gives another boolean value. I think I should typecast
it into an int2 but I don't know how to do that since there isn't a function
liek a bool_to_int2.
Any ideas ?

Michael Davis wrote:

I defined my Access boolean fields as int2 in Postgres. This, in
combination with removing the "Bools as Char" flag in the ODBC driver
appears to work. However, I have not been able to much testing.

-----Original Message-----
From: Valerio Santinelli [SMTP:tanis@mediacom.it]
Sent: Wednesday, February 24, 1999 4:25 AM
To: sferac@bo.nettuno.it
Cc: pgsql-interfaces@postgreSQL.org;
pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Checkboxes on MSAccess and PostgreSQL

Jose' Soares wrote:

Valerio Santinelli wrote:

First of all.. thanks to everybody for helping me out witht the
"sequence" stuff. :)

I've got another questions for you dudes. When using a CheckBox

in a

Form written in MSAccess that's related to a table in a
PostgreSQL database, if I simply turn its status from ON to OFF

it works

fine, while if i'm doing the opposite it won't work.

I noticed that when exporting the table from MSAccess to

PostgreSQL the

"yes/no" fields all became char(1) and not boolean.. maybe this

could be

the problem.

I also noticed from the logs that when updating the status from

ON to

OFF the UPDATE goes out with something like field_name='0'
I'm not sure it should use the "'" since it's more like a

numerical, no

?

Thanks again,

Valerio Santinelli
tanis@mediacom.it

You have to uncheck "Bools as Char" on Advanced options Driver on

ODBC

Data Source Administrator
and then you have to create an operator for bool=int4 like this:

/* M$Access tratta il valore booleano come un intero 0=false o

-1=true

mentre invece PostgreSQL lo tratta come una stringa:
'true','t','1','y','yes','false','f','0','n','no'
Questo script crea l'operatore = e implicitamente <> per bool e

int4

per compatibilita' con M$Access.
*/
drop operator = (bool,int4);
drop function MsAccessBool(bool,int4);

create function MsAccessBool(bool,int4)
returns bool
as ''
language 'internal';

create operator = (
leftarg=bool,
rightarg=int4,
procedure=MsAccessBool,
commutator='=',
negator='<>',
restrict=eqsel,
join=eqjoinsel
);

Some time ago I got this message and I tried to make booleans work
with my
database, but there's been no way to do that. First of all, during
the
"DROP MsAccessBool" my PostgreSQL database engine reports that the
function
can't be dropped because it's an internal (built-in).
Second, it seems to replace it by creating the function (i don't
know how it
could if the function already exists..) but then again when I'm
updating a
record in a table with booleans it simply doesn't let me do that.
I'm
desperately seeking help now.. I don't really know how to solve this
problem.

Thanks in advance

Valerio Santinelli
tanis@mediacom.it

--

C'ya!

Valerio Santinelli a.k.a. TANiS
[tanis@mediacom.it]+:+[http://www.mediacom.it/~tanis]

I wrote a function that takes a boolean parameter and returns int2.

-----Original Message-----
From: Valerio Santinelli [SMTP:tanis@mediacom.it]
Sent: Wednesday, February 24, 1999 1:10 PM
To: Michael Davis
Cc: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Checkboxes on MSAccess and PostgreSQL

That's the same thing I did today to get rid of the boolean problem.
Now I've
got another one ;)
I want to use my data contained in the table filled with boolean
stuff and I'd
like to convert all the booleans to int2. I know there's a way to do
that by
doing something like:

INSERT INTO mynewtable SELECT field1, field2, (booleanfield='t'),
field3,...
FROM myoldtable;

but "(booleanfield='t')" gives another boolean value. I think I
should typecast
it into an int2 but I don't know how to do that since there isn't a
function
liek a bool_to_int2.
Any ideas ?

Michael Davis wrote:

I defined my Access boolean fields as int2 in Postgres. This, in
combination with removing the "Bools as Char" flag in the ODBC

driver

appears to work. However, I have not been able to much testing.

-----Original Message-----
From: Valerio Santinelli [SMTP:tanis@mediacom.it]
Sent: Wednesday, February 24, 1999 4:25 AM
To: sferac@bo.nettuno.it
Cc: pgsql-interfaces@postgreSQL.org;
pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Checkboxes on MSAccess and

PostgreSQL

Jose' Soares wrote:

Valerio Santinelli wrote:

First of all.. thanks to everybody for helping me out

witht the

"sequence" stuff. :)

I've got another questions for you dudes. When using

a CheckBox

in a

Form written in MSAccess that's related to a table in

a

PostgreSQL database, if I simply turn its status from

ON to OFF

it works

fine, while if i'm doing the opposite it won't work.

I noticed that when exporting the table from MSAccess

to

PostgreSQL the

"yes/no" fields all became char(1) and not boolean..

maybe this

could be

the problem.

I also noticed from the logs that when updating the

status from

ON to

OFF the UPDATE goes out with something like

field_name='0'

I'm not sure it should use the "'" since it's more

like a

numerical, no

?

Thanks again,

Valerio Santinelli
tanis@mediacom.it

You have to uncheck "Bools as Char" on Advanced options

Driver on

ODBC

Data Source Administrator
and then you have to create an operator for bool=int4

like this:

/* M$Access tratta il valore booleano come un intero

0=false o

-1=true

mentre invece PostgreSQL lo tratta come una stringa:
'true','t','1','y','yes','false','f','0','n','no'
Questo script crea l'operatore = e implicitamente <>

per bool e

int4

per compatibilita' con M$Access.
*/
drop operator = (bool,int4);
drop function MsAccessBool(bool,int4);

create function MsAccessBool(bool,int4)
returns bool
as ''
language 'internal';

create operator = (
leftarg=bool,
rightarg=int4,
procedure=MsAccessBool,
commutator='=',
negator='<>',
restrict=eqsel,
join=eqjoinsel
);

Some time ago I got this message and I tried to make

booleans work

with my
database, but there's been no way to do that. First of

all, during

the
"DROP MsAccessBool" my PostgreSQL database engine reports

that the

function
can't be dropped because it's an internal (built-in).
Second, it seems to replace it by creating the function (i

don't

know how it
could if the function already exists..) but then again

when I'm

updating a
record in a table with booleans it simply doesn't let me

do that.

I'm
desperately seeking help now.. I don't really know how to

solve this

problem.

Thanks in advance

Valerio Santinelli
tanis@mediacom.it

--

C'ya!

Valerio Santinelli a.k.a. TANiS
[tanis@mediacom.it]+:+[http://www.mediacom.it/~tanis]