Microsoft Access and PostgreSQL Interaction

Microsoft Access does not handle boolean values in a default-compatible fashion with a PostgreSQL backend. Several errors will occur, but are solved via the addition of both of the necessary operator= and operator<> functions to manage the conversion, plus properly configuring the ODBC connection.

Environment

This was tested with Microsoft Access 2003 and PostgreSQL 8.1 using the shipped-with ODBC drivers. The Access database utilizes linked tables to a system DSN for the PostgreSQL database running on the same machine.

Error #1
This problem appears in several different error formats. The first error I encountered was:

This error has also been seen on the usegroups as:

The two errors are equivilient for this issue.

Resolution
Resolving this error requires several alterations.

First, for the DSN Configuration, within the Options group select the Datasource options. Set the following options on Page 1:

And on Page 2:

At this point, please make sure you refresh the table links within Microsoft Access using the Linked Table Manager.

Now, in order to properly translate the boolean values between Microsoft Access and PostgreSQL we need to add the following stored procedures. These can be added by pasting directly into the ‘psql’ tool after logging into the database. Several of the posts out there neglect to include the

statement. This causes the second error:

Error #2

This error is particularly uninformative. Essentially, you encounter this error if you did not add the CREATE OPERATOR<> statement, but only the CREATE OPERATOR= statement as per several of the usenet posts. Translated, this error is saying that the expected stored procedure is not found — in this case, it’s looking for the negator operator.

Resolution (Continued)
To remove both of these errors, add the following stored procedure code

DROP OPERATOR = (bool, int4);
DROP OPERATOR <> (bool, int4);
DROP FUNCTION MsAccessBool (bool, int4);
DROP FUNCTION MsAccessBoolEq (bool, int4);
DROP FUNCTION MsAccessBoolNeq (bool, int4);
 
CREATE FUNCTION MsAccessBoolEq (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 FUNCTION MsAccessBoolNeq (bool, int4) RETURNS BOOL AS $$
BEGIN
   RETURN NOT MsAccessBoolEq($1, $2);
END;
$$ LANGUAGE 'plpgsql';
 
CREATE OPERATOR = (
   LEFTARG = BOOL,
   RIGHTARG = INT4,
   PROCEDURE = MsAccessBoolEq,
   COMMUTATOR = '=',
   NEGATOR = '<>',
   RESTRICT = EQSEL,
   JOIN = EQJOINSEL
);
 
CREATE OPERATOR <> (
   LEFTARG = BOOL,
   RIGHTARG = INT4,
   PROCEDURE = MsAccessBoolNeq,
   COMMUTATOR = '=',
   NEGATOR = '<>',
   RESTRICT = EQSEL,
   JOIN = EQJOINSEL
);