Thread: Using PostgreSQL and Access?
Since the PostgreSQL (CygWin) mailing list seems to be quite dead, I'm
posting this one here.
My question is: Has any one successfully used PostgreSQL with Access? I have
read the Accces-FAQ, but I still have major problems: When I create a form
and change / add some data in there, I always get errors, f.e. "Cannot add
duplicate index" (although I did just a minor change), but also other error
messages, or data conversion errors ("Cannot convert XID to int4"). The
error messages change, if I try other settings in ODBC driver, but it
doesn't work either. Also I get the error messages about the unique index
when I add a new record as described in the ODBC FAQ, but I don't agree with
the "workaround" by using a timestamp. That one is really weird, and it
really should work without.
Maybe PostgreSQL (or the ODBC driver) should have a "compatibility" mode to
work with more forgiving frontends. I don't see, why f.e. a boolean column
gives an error, if you do something like "boolean_col = 0".
Access does work well with other databases over ODBC (okay, SQL Server
works, but also MySQL), so why doesn't PostgreSQL?
Regards
Markus
I've been using an MS Access front-end with Postgres on Digital Unix, Redhat and Mandrake with fairly good results. We started on v6.5.3 and just upgraded to 7.1.3. I download the ODBC driver from odbc.postgresql.org and haven't had any problems. The duplicate error could possibly because Access cannot identify a unique record. A 'many' table in a one to many relationship could cause this problem if the foreign key is 'keyed' rather than a unique key (which I implement similar to Access' auto number). You may want to show the OID column (Advanced Settings). If you would like, I have prepared an 'ODBC installation manual' that I have in PDF format that contains graphics/screenshots for the individual settings. Please contact me directly if you would like to obtain this file. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Kevin Drewiske '01 CE MSOE Webmaster Team: http://www.msoe.edu/ KJDesign Website Development: http://www.drewiske.com/KJDesign/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -----Original Message----- From: Markus Meyer [SMTP:meyer@mesw.de] Sent: Wednesday, October 24, 2001 18:29 To: PostgreSQL (General) Mailing List Subject: Using PostgreSQL and Access? Since the PostgreSQL (CygWin) mailing list seems to be quite dead, I'm posting this one here. My question is: Has any one successfully used PostgreSQL with Access? I have read the Accces-FAQ, but I still have major problems: When I create a form and change / add some data in there, I always get errors, f.e. "Cannot add duplicate index" (although I did just a minor change), but also other error messages, or data conversion errors ("Cannot convert XID to int4"). The error messages change, if I try other settings in ODBC driver, but it doesn't work either. Also I get the error messages about the unique index when I add a new record as described in the ODBC FAQ, but I don't agree with the "workaround" by using a timestamp. That one is really weird, and it really should work without. Maybe PostgreSQL (or the ODBC driver) should have a "compatibility" mode to work with more forgiving frontends. I don't see, why f.e. a boolean column gives an error, if you do something like "boolean_col = 0". Access does work well with other databases over ODBC (okay, SQL Server works, but also MySQL), so why doesn't PostgreSQL? Regards Markus ---------------------------------------------------- Sign Up for NetZero Platinum Today Only $9.95 per month! http://my.netzero.net/s/signup?r=platinum&refcd=PT97
If Access changes / inserts a record it tries to refetch the thing
after that operation has finished. If it hasn't got a unique ID to try
to find the record it will give errors like "row deleted" and maybe
your index-errors.
Try switching on Datasource...OID-options....Show Column and Fake
index.
arian.
On Thu, 25 Oct 2001 00:59:22 +0000 (UTC), meyer@mesw.de ("Markus
Meyer") wrote:
>Since the PostgreSQL (CygWin) mailing list seems to be quite dead, I'm
>posting this one here.
>
>My question is: Has any one successfully used PostgreSQL with Access? I have
>read the Accces-FAQ, but I still have major problems: When I create a form
>and change / add some data in there, I always get errors, f.e. "Cannot add
>duplicate index" (although I did just a minor change), but also other error
>messages, or data conversion errors ("Cannot convert XID to int4"). The
>error messages change, if I try other settings in ODBC driver, but it
>doesn't work either. Also I get the error messages about the unique index
>when I add a new record as described in the ODBC FAQ, but I don't agree with
>the "workaround" by using a timestamp. That one is really weird, and it
>really should work without.
>
>Maybe PostgreSQL (or the ODBC driver) should have a "compatibility" mode to
>work with more forgiving frontends. I don't see, why f.e. a boolean column
>gives an error, if you do something like "boolean_col = 0".
>
>Access does work well with other databases over ODBC (okay, SQL Server
>works, but also MySQL), so why doesn't PostgreSQL?
>
>Regards
>
>
>Markus
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
_______________________
Arian Prins
_______________________
prinsarian
apetale
zonnet
dot
nl
Here is the routine that is needed in the backend for xid to int4
comparisons (for row versionig):
create function int4eq(xid,int4)
returns bool
as ''
language 'internal';
create operator = (
leftarg=xid,
rightarg=int4,
procedure=int4eq,
commutator='=',
negator='<>',
restrict=eqsel,
join=eqjoinsel
);
Also here's a little snippet which might help with bools.
For what its worth MS Access bools are int2's and I tend just to use int2's
instead of bools (0 false, anything else true. Use <>False rather than =True
if you do this as true has a definite value of -1).
DROP OPERATOR = (bool, int4);
DROP FUNCTION MsAccessBool (bool, int4);
CREATE FUNCTION MsAccessBool (bool, int4) RETURNS BOOL AS '
BEGIN
IF $1 ISNULL THEN
RETURN NULL;
END IF;
IF $1 IS TRUE THEN
IF $2 <> 0 THEN
RETURN TRUE;
END IF;
ELSE
IF $2 = 0 THEN
RETURN TRUE;
END IF;
END IF;
RETURN FALSE;
END;
' LANGUAGE 'plpgsql';
CREATE OPERATOR = (
LEFTARG = BOOL,
RIGHTARG = INT4,
PROCEDURE = MsAccessBool,
COMMUTATOR = '=',
NEGATOR = '<>',
RESTRICT = EQSEL,
JOIN = EQJOINSEL
);
Also if you are using Access 97 it can be funny about accepting that things
it can't index can be indexed by the backend. Another possible problem is
that text translates the memo as default (unless changed in the ODBC driver)
and access is a bit funny about what it'll do with memo's (eg IIRC it won't
join on them).
FYI: What happens when Access tries to update is that it uses a where clause
specfying everything in the row if you don't use row versioning. This can
cause problems with float precision, etc.....
Hope something in this lot helps.
- Stuart
> -----Original Message-----
> From: prinsarian@zonnet.nl [SMTP:prinsarian@zonnet.nl]
> Sent: Thursday, October 25, 2001 9:41 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Using PostgreSQL and Access?
>
> If Access changes / inserts a record it tries to refetch the thing
> after that operation has finished. If it hasn't got a unique ID to try
> to find the record it will give errors like "row deleted" and maybe
> your index-errors.
>
> Try switching on Datasource...OID-options....Show Column and Fake
> index.
>
> arian.
>
>
> On Thu, 25 Oct 2001 00:59:22 +0000 (UTC), meyer@mesw.de ("Markus
> Meyer") wrote:
>
> >Since the PostgreSQL (CygWin) mailing list seems to be quite dead, I'm
> >posting this one here.
> >
> >My question is: Has any one successfully used PostgreSQL with Access? I
> have
> >read the Accces-FAQ, but I still have major problems: When I create a
> form
> >and change / add some data in there, I always get errors, f.e. "Cannot
> add
> >duplicate index" (although I did just a minor change), but also other
> error
> >messages, or data conversion errors ("Cannot convert XID to int4"). The
> >error messages change, if I try other settings in ODBC driver, but it
> >doesn't work either. Also I get the error messages about the unique index
> >when I add a new record as described in the ODBC FAQ, but I don't agree
> with
> >the "workaround" by using a timestamp. That one is really weird, and it
> >really should work without.
> >
> >Maybe PostgreSQL (or the ODBC driver) should have a "compatibility" mode
> to
> >work with more forgiving frontends. I don't see, why f.e. a boolean
> column
> >gives an error, if you do something like "boolean_col = 0".
> >
> >Access does work well with other databases over ODBC (okay, SQL Server
> >works, but also MySQL), so why doesn't PostgreSQL?
> >
> >Regards
> >
> >
> >Markus
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
> _______________________
> Arian Prins
> _______________________
> prinsarian
> apetale
> zonnet
> dot
> nl
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
Dear Kevin, many thanks for your reply. It would really be fine, if you could send me this 'ODBC installation manual'. I have been working with Access for many years, but I'm just getting started using PostgreSQL. Because the databases of some of my customers are constantly growing, I want to install them PostgreSQL as a new backend. Which tool do you use for importing data / table definitions into Access? I've found a script on the net for that purpose, but it doesn't seem to work well, so I'm likely to write my own script and maybe write a patch for the ODBC driver to accomplish an "Access compatibility mode". Everything will, of course, be put into the public domain. Some goals that I would like to accomplish, are: - Making editing behave more like Access (serial keys are created on first keystroke etc.) - Make the ODBC driver to make Access use the correct primary index (this _must_ be possible!) - Making queries act more like Access does (case-insensitive, but correct uppercase / lowercase is returned - Have a nice installation program on Windoze, that does all the cygwin/psql installation stuff, just as MySQL has - Whatever is necessary to make PostgreSQL a _real_ alternative to SQL server on Windoze Maybe I'm just dreaming with some of these points, and maybe my work would be useless because there are already good tools out there that I don't know of. Any suggestions, donations, help? ;-) Markus > -----Ursprungliche Nachricht----- > Von: Kevin J. Drewiske [mailto:drewiskk@msoe.edu] > Gesendet: Donnerstag, 25. Oktober 2001 05:22 > An: 'Markus Meyer'; PostgreSQL (General) Mailing List > Betreff: RE: Using PostgreSQL and Access? > > > I've been using an MS Access front-end with Postgres on Digital Unix, > Redhat and Mandrake with fairly good results. We started on v6.5.3 and > just upgraded to 7.1.3. > > I download the ODBC driver from odbc.postgresql.org and haven't had any > problems. The duplicate error could possibly because Access cannot > identify a unique record. A 'many' table in a one to many relationship > could cause this problem if the foreign key is 'keyed' rather > than a unique > key (which I implement similar to Access' auto number). You may want to > show the OID column (Advanced Settings). > > If you would like, I have prepared an 'ODBC installation manual' that I > have in PDF format that contains graphics/screenshots for the individual > settings. Please contact me directly if you would like to obtain this > file. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > Kevin Drewiske '01 CE > MSOE Webmaster Team: http://www.msoe.edu/ > KJDesign Website Development: http://www.drewiske.com/KJDesign/ > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > > -----Original Message----- > From: Markus Meyer [SMTP:meyer@mesw.de] > Sent: Wednesday, October 24, 2001 18:29 > To: PostgreSQL (General) Mailing List > Subject: Using PostgreSQL and Access? > > Since the PostgreSQL (CygWin) mailing list seems to be quite dead, I'm > posting this one here. > > My question is: Has any one successfully used PostgreSQL with Access? I > have > read the Accces-FAQ, but I still have major problems: When I create a form > and change / add some data in there, I always get errors, f.e. "Cannot add > duplicate index" (although I did just a minor change), but also > other error > messages, or data conversion errors ("Cannot convert XID to int4"). The > error messages change, if I try other settings in ODBC driver, but it > doesn't work either. Also I get the error messages about the unique index > when I add a new record as described in the ODBC FAQ, but I don't agree > with > the "workaround" by using a timestamp. That one is really weird, and it > really should work without. > > Maybe PostgreSQL (or the ODBC driver) should have a > "compatibility" mode to > work with more forgiving frontends. I don't see, why f.e. a boolean column > gives an error, if you do something like "boolean_col = 0". > > Access does work well with other databases over ODBC (okay, SQL Server > works, but also MySQL), so why doesn't PostgreSQL? > > Regards > > > Markus > > > ---------------------------------------------------- > Sign Up for NetZero Platinum Today > Only $9.95 per month! > http://my.netzero.net/s/signup?r=platinum&refcd=PT97 >