Simple way of storing Access booleans (Yes/No) fields

Hi,



PG version 7.4 and Access version 2003.



Its the old Access boolean issue which needs as simple an answer as
possible?





I have a client where many users were running off the same Access
database which obviously got corrupted and recently failed completely.



After finding a backup the data has been moved to a Postgresql DB
running on a Debian stable server.



The data was exported via ODBC and I've tidied up the
autonumber-to-sequence issue.  The original Access tables were renamed
to tablebnname_old and the new Postgresql tables have been set up as
linked tables with the original names.  Most of the application is
working fine.



Seems like only one issue remains.



On a couple of forms there are check boxes and radio buttons which
linked originally to Yes/No (i.e. Boolean) fields in the original
Access table.



I have a fairly free hand to sort this out - and there are only 4
tables which contain boolean fields and I can alter the Access
application as I see fit.



There are quite a few queries (dozens) but again I can ask them to cut
them down and re-write needed queries if necessary.



What is the simplest way forward?



What should the ODBC connection be set as?



Here are some possible scenarios.



1. Should I set the fields to be int2 data type and then set the ODBC
driver to not treat bools as char but treat -1 as true. 



Will queries written in Access then run correctly?  I thought I'd tried
this and it didn't work possibly because I did not relink the table.



2. If I simply uncheck the treat bools as char option will the data be
saved correctly as booleans - will the ODBC driver be ok with the data
- i.e. reading and writing.



3. Should I simply set the field as a char(1) and then in Access
somehow or other set the check boxes to save the data as 't' or 'f'. 
How would the control do with reading the data.



4. I understand there may be some extra functions which may be added to
PG to get Access play properly - is there a simple function which can
be added.  Is there a well documented, proven and established method to
acheive this.



5. Have these methods been 'tried and tested'











6. Change the check boxes to combo boxes - the form is horribly
cluttered but the following seems like a simple answer.

The way I have handled this is to avoid check boxes and use a combo box 

instead. I supply the values as True;1 ,False;0 and bind the field to the 

second value of each pair. To make things easier for data entry I hide the 

second column by giving it a width of 0".  In my DSN settings I check bool as 

char and uncheck true as -1. 

-- 

Adrian Klaver	





aklaver ( at ) comcast ( dot ) net

Re: Simple way of storing Access booleans (Yes/No) fields

This is a multi-part message in MIME format.

------=_NextPart_000_0004_01C6E2DA.DA811B80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,

Firstly, don't forget to use an Access 2003 native format database in =
your project, and not an Access 2000 format database. There are huge =
slowups when using the Access 2000 format. Don't ask me why!

For your specific problem, I would personnaly use an int2, and treat on =
the server everything that is different from 0 as true. This is quick, =
and with a little work in PLPGSQL, it might be clean too. Otherwise, you =
might give a try to the advanced options of the ODBC driver: I'm not =
sure they can help, but it's worth trying.

Regards,

---------------
Philippe Lang



________________________________

De : pgsql-odbc-owner@postgresql.org =
[mailto:pgsql-odbc-owner@postgresql.org] De la part de Kevin Bailey
Envoy=E9 : jeudi, 28. septembre 2006 00:22
=C0 : pgsql-odbc@postgresql.org
Objet : [ODBC] Simple way of storing Access booleans (Yes/No) fields
=09
=09
Hi,
=09
PG version 7.4 and Access version 2003.
=09
Its the old Access boolean issue which needs as simple an answer as =
possible?
=09
=09
I have a client where many users were running off the same Access =
database which obviously got corrupted and recently failed completely.
=09
After finding a backup the data has been moved to a Postgresql DB =
running on a Debian stable server.
=09
The data was exported via ODBC and I've tidied up the =
autonumber-to-sequence issue. The original Access tables were renamed =
to tablebnname_old and the new Postgresql tables have been set up as =
linked tables with the original names. Most of the application is =
working fine.
=09
Seems like only one issue remains.
=09
On a couple of forms there are check boxes and radio buttons which =
linked originally to Yes/No (i.e. Boolean) fields in the original Access =
table.
=09
I have a fairly free hand to sort this out - and there are only 4 =
tables which contain boolean fields and I can alter the Access =
application as I see fit.
=09
There are quite a few queries (dozens) but again I can ask them to cut =
them down and re-write needed queries if necessary.
=09
What is the simplest way forward?
=09
What should the ODBC connection be set as?
=09
Here are some possible scenarios.
=09
1. Should I set the fields to be int2 data type and then set the ODBC =
driver to not treat bools as char but treat -1 as true. =20
=09
Will queries written in Access then run correctly? I thought I'd tried =
this and it didn't work possibly because I did not relink the table.
=09
2. If I simply uncheck the treat bools as char option will the data be =
saved correctly as booleans - will the ODBC driver be ok with the data - =
i.e. reading and writing.
=09
3. Should I simply set the field as a char(1) and then in Access =
somehow or other set the check boxes to save the data as 't' or 'f'. =
How would the control do with reading the data.
=09
4. I understand there may be some extra functions which may be added to =
PG to get Access play properly - is there a simple function which can be =
added. Is there a well documented, proven and established method to =
acheive this.
=09
5. Have these methods been 'tried and tested'
=09
http://www.mail-archive.com/pgsql-docs@postgresql.org/msg015 63.html
=
http://community.seattleserver.com/viewtopic.php?p=3D8&sid=3 D3add118a6924=
da03531fcbbbcc2c3ca8
http://www.mrayyan.com/?p=3D42
=09
6. Change the check boxes to combo boxes - the form is horribly =
cluttered but the following seems like a simple answer.
=09
=09
The way I have handled this is to avoid check boxes and use a combo box =

instead. I supply the values as True;1 ,False;0 and bind the field to =
the=20
second value of each pair. To make things easier for data entry I hide =
the=20
second column by giving it a width of 0". In my DSN settings I check =
bool as=20
char and uncheck true as -1.=20
--=20
Adrian Klaver=09
=09
=09
aklaver ( at ) comcast ( dot ) net

There are however dozens of queries and many of them use booleans which =
then may not work - however, if needed I can ask the client to remove =
the unneeded queries and I could then re-write the existing queries to =
take into account the new field.
=09
Maybe I should create the field as int2 and in the combo box have the =
bound fields as -1 (label True) and 0 (label False). Maybe this way the =
existing Access queries would work ok without changes?
=09
=09
=09
=09
Any thoughts would be gratefully received.
=09
Kevin
=09


------=_NextPart_000_0004_01C6E2DA.DA811B80
Content-Type: application/x-pkcs7-signature;
name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="smime.p7s"

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIII/zCCAocw
ggHwoAMCAQICEEdYYafa1wkm0CP4DOffAiswDQYJKoZIhvcNAQEEBQAwYjEL MAkGA1UEBhMCWkEx
JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNV BAMTI1RoYXd0ZSBQ
ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA2MDYyMjE3MzE0NloX DTA3MDYyMjE3MzE0
NlowZzENMAsGA1UEBBMETGFuZzERMA8GA1UEKhMIUGhpbGlwcGUxFjAUBgNV BAMTDVBoaWxpcHBl
IExhbmcxKzApBgkqhkiG9w0BCQEWHHBoaWxpcHBlLmxhbmdAYXR0aWtzeXN0 ZW0uY2gwgZ8wDQYJ
KoZIhvcNAQEBBQADgY0AMIGJAoGBAOfvCPREDwfIbzsNRoyBDoSOWfI2NNWd RRMJXuj5ldF22gSQ
6MiXFUjt4HlRIwPb8x13hrv89GW/SmLjFThxf7NKZ8dPTZzp4ZlvrQBRAYhS uTQOJ8azyzruZu54
Ak7fa3pz26Q3B8kg4knZBz3O9Q4K1tK8AEdjNQpyDg/iDdzjAgMBAAGjOTA3 MCcGA1UdEQQgMB6B
HHBoaWxpcHBlLmxhbmdAYXR0aWtzeXN0ZW0uY2gwDAYDVR0TAQH/BAIwADAN BgkqhkiG9w0BAQQF
AAOBgQAAzz+I6MrhWjbfWSgngW/DthHvXiTR6x0cCeb4T5mBb5dgueqgLnrh /2blsbCQuLWh0URe
/1l/QQ2dY2elG3PEDPv9cCl/LcwsCsE1Nj3bGM4gdVx9crYvTw5dyyUuDq1K ShV3uziE+o5Lyfx4
qWRYd6/yIBDVarohdpL8J0DJczCCAy0wggKWoAMCAQICAQAwDQYJKoZIhvcN AQEEBQAwgdExCzAJ
BgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0ZXJuIENhcGUxEjAQBgNVBAcTCUNh cGUgVG93bjEaMBgG
A1UEChMRVGhhd3RlIENvbnN1bHRpbmcxKDAmBgNVBAsTH0NlcnRpZmljYXRp b24gU2VydmljZXMg
RGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBD QTErMCkGCSqGSIb3
DQEJARYccGVyc29uYWwtZnJlZW1haWxAdGhhd3RlLmNvbTAeFw05NjAxMDEw MDAwMDBaFw0yMDEy
MzEyMzU5NTlaMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBD YXBlMRIwEAYDVQQH
EwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYD VQQLEx9DZXJ0aWZp
Y2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVy c29uYWwgRnJlZW1h
aWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0 ZS5jb20wgZ8wDQYJ
KoZIhvcNAQEBBQADgY0AMIGJAoGBANRp19SwlGRbcelH2AxRtupykbCEXn0t DY97Et+FJXUodDpC
LGMnn5V7S+9+GYcdhuqj3bnOlmQawhRuRKx85o/oTQ9xH0A4pgCjh3j2+ZSG Xq3qwF5269kUo11u
enwMpUtVfwYZKX+emibVars4JAhqmMex2qOYkf152+VaxBy5AgMBAAGjEzAR MA8GA1UdEwEB/wQF
MAMBAf8wDQYJKoZIhvcNAQEEBQADgYEAx+ySfk749ZalZ2IqpPBNEWDQb41g WGGsJrtSNVwIzzD7
qEqWih9iQiOMFw/0umScF6xHKd+dmF7SbGBxXKKs3Hnj524ARx+1DSjoAp3k mv0T9KbZfLH43F8j
JgmRgHPQFBveQ6mDJfLmnC8Vyv6mq4oHdYsM3VGEa+T40c53ooEwggM/MIIC qKADAgECAgENMA0G
CSqGSIb3DQEBBQUAMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVy biBDYXBlMRIwEAYD
VQQHEwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgw JgYDVQQLEx9DZXJ0
aWZpY2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUg UGVyc29uYWwgRnJl
ZW1haWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRo YXd0ZS5jb20wHhcN
MDMwNzE3MDAwMDAwWhcNMTMwNzE2MjM1OTU5WjBiMQswCQYDVQQGEwJaQTEl MCMGA1UEChMcVGhh
d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBl cnNvbmFsIEZyZWVt
YWlsIElzc3VpbmcgQ0EwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAMSm PFVzVftOucqZWh5o
wHUEcJ3f6f+jHuy9zfVb8hp2vX8MOmHyv1HOAdTlUAow1wJjWiyJFXCO3cnw K4Vaqj9xVsuvPAsH
5/EfkTYkKhPPK9Xzgnc9A74r/rsYPge/QIACZNenprufZdHFKlSFD0gEf6e2 0TxhBEAeZBlyYLf7
AgMBAAGjgZQwgZEwEgYDVR0TAQH/BAgwBgEB/wIBADBDBgNVHR8EPDA6MDig NqA0hjJodHRwOi8v
Y3JsLnRoYXd0ZS5jb20vVGhhd3RlUGVyc29uYWxGcmVlbWFpbENBLmNybDAL BgNVHQ8EBAMCAQYw
KQYDVR0RBCIwIKQeMBwxGjAYBgNVBAMTEVByaXZhdGVMYWJlbDItMTM4MA0G CSqGSIb3DQEBBQUA
A4GBAEiM0VCD6gsuzA2jZqxnD3+vrL7CF6FDlpSdf0whuPg2H6otnzYvwPQc UCCTcDz9reFhYsPZ
Ohl+hLGZGwDFGguCdJ4lUJRix9sncVcljd2pnDmOjCBPZV+V2vf3h9bGCE6u 9uo05RAaWzVNd+NW
IXiC3CEZNd4ksdMdRv9dX2VPMYIC+DCCAvQCAQEwdjBiMQswCQYDVQQGEwJa QTElMCMGA1UEChMc
VGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3Rl IFBlcnNvbmFsIEZy
ZWVtYWlsIElzc3VpbmcgQ0ECEEdYYafa1wkm0CP4DOffAiswCQYFKw4DAhoF AKCCAdgwGAYJKoZI
hvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDYwOTI4MDY0 ODIyWjAjBgkqhkiG
9w0BCQQxFgQUZpK9O3t0MYwencDR/j7k412jOTYwZwYJKoZIhvcNAQkPMVow WDAKBggqhkiG9w0D
BzAOBggqhkiG9w0DAgICAIAwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcwDQYI KoZIhvcNAwICASgw
BwYFKw4DAhowCgYIKoZIhvcNAgUwgYUGCSsGAQQBgjcQBDF4MHYwYjELMAkG A1UEBhMCWkExJTAj
BgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMT I1RoYXd0ZSBQZXJz
b25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhBHWGGn2tcJJtAj+Azn3wIrMIGH BgsqhkiG9w0BCRAC
CzF4oHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0 aW5nIChQdHkpIEx0
ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5n IENBAhBHWGGn2tcJ
JtAj+Azn3wIrMA0GCSqGSIb3DQEBAQUABIGAHLbnyQo4sfHGiMEefu3PhuzQ xKvhGsfgIWLHyqrc
/GvH1RnUBNqDksxQjcrCF4aVN06RMTP4D9DA1wc5jlYfXoOlhiUA5wKc/ef/ XN+MaujuyAQgfra8
haMJv7NADoYuRzR9S6Uqs6aunfZCWo+EHhc+t7x/rQXiBV6N7ihTl60AAAAA AAA=

------=_NextPart_000_0004_01C6E2DA.DA811B80--

Re: Simple way of storing Access booleans (Yes/No) fields

In Postgresql 8.0 I wrote the following functions, which will
probably work in 7.4 as well.
They have removed the bool problem completely. My ODBC settings are:
Bools as Char UNCHECKED
TRUE is -1 UNCHECKED

I am using Access 2000, with a lot of checkboxes and boolean fields in
the front end with no problems

Sim
set search_path=3Dpg_catalog;

create or replace function inttobool(num int,val bool) returns bool as
$$
begin
if num=3D0 and not val then
return true;
elsif num<>0 and val then=20
return true;
else return false;
end if;
end;
$$ language 'plpgsql';
create or replace function inttobool(val bool, num int) returns bool as
$$
begin
return inttobool(num,val);
end;
$$ language 'plpgsql';
create or replace function notinttobool(val bool, num int) returns bool a=
s
$$
begin
return not inttobool(num,val);
end;
$$ language 'plpgsql';
create or replace function notinttobool(num int, val bool) returns bool a=
s
$$
begin
return not inttobool(num,val);
end;
$$ language 'plpgsql';

CREATE OPERATOR =3D (
leftarg =3D integer,
rightarg =3D boolean,
procedure =3D inttobool,
commutator =3D =3D,
negator =3D !=3D
);
CREATE OPERATOR =3D (
leftarg =3D boolean,
rightarg =3D integer,
procedure =3D inttobool,
commutator =3D =3D,
negator =3D !=3D
);
CREATE OPERATOR <> (
leftarg =3D integer,
rightarg =3D boolean,
procedure =3D notinttobool,
commutator =3D <>,
negator =3D =3D
);
CREATE OPERATOR <> (
leftarg =3D boolean,
rightarg =3D integer,
procedure =3D notinttobool,
commutator =3D <>,
negator =3D =3D
);


____________________________________________________________ _____________=
_______

Hi,

PG version 7.4 and Access version 2003.

Its the old Access boolean issue which needs as simple an answer as
possible?


I have a client where many users were running off the same Access
database which obviously got corrupted and recently failed completely.

After finding a backup the data has been moved to a Postgresql DB
running on a Debian stable server.

The data was exported via ODBC and I've tidied up the
autonumber-to-sequence issue.=A0 The original Access tables were renamed
to tablebnname_old and the new Postgresql tables have been set up as
linked tables with the original names.=A0 Most of the application is
working fine.

Seems like only one issue remains.

On a couple of forms there are check boxes and radio buttons which
linked originally to Yes/No (i.e. Boolean) fields in the original
Access table.

I have a fairly free hand to sort this out - and there are only 4
tables which contain boolean fields and I can alter the Access
application as I see fit.

There are quite a few queries (dozens) but again I can ask them to cut
them down and re-write needed queries if necessary.

What is the simplest way forward?

What should the ODBC connection be set as?

Here are some possible scenarios.

1. Should I set the fields to be int2 data type and then set the ODBC
driver to not treat bools as char but treat -1 as true. 

Will queries written in Access then run correctly?=A0 I thought I'd tried
this and it didn't work possibly because I did not relink the table.

2. If I simply uncheck the treat bools as char option will the data be
saved correctly as booleans - will the ODBC driver be ok with the data
- i.e. reading and writing.

3. Should I simply set the field as a char(1) and then in Access
somehow or other set the check boxes to save the data as 't' or 'f'.=A0
How would the control do with reading the data.

4. I understand there may be some extra functions which may be added to
PG to get Access play properly - is there a simple function which can
be added.=A0 Is there a well documented, proven and established method to
acheive this.

5. Have these methods been 'tried and tested'

http://www.mail-archive.com/pgsql-docs@postgresql.org/msg015 63.html
http://community.seattleserver.com/viewtopic.php?p=3D8 sid=3D3add118a6924=
da03531fcbbbcc2c3ca8
http://www.mrayyan.com/?p=3D42

6. Change the check boxes to combo boxes - the form is horribly
cluttered but the following seems like a simple answer.

The way I have handled this is to avoid check boxes and use a combo box=20
instead. I supply the values as True;1 ,False;0 and bind the field to the=
=20
second value of each pair. To make things easier for data entry I hide th=
e=20
second column by giving it a width of 0". In my DSN settings I check boo=
l as=20
char and uncheck true as -1.=20
--=20
Adrian Klaver=09


aklaver ( at ) comcast ( dot ) net


There are however dozens of queries and many of them use booleans which
then may not work - however, if
needed I can ask the client to remove the unneeded queries and I could
then re-write the existing queries to take into account the new field.

Maybe I should create the field as int2 and in the combo box have the
bound fields as -1 (label True) and 0 (label False).=A0 Maybe this way
the existing Access queries would work ok without changes?




Any thoughts would be gratefully received.

Kevin


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings