What is the cause of the following message:  ODBC connection to 'database' failed.

1. Either incorrect hostname,  port number, database name, user name, or password in the data source setup.
2. The postmaster not running with the -i option.
3. The pg_hba.conf file in $PGPATH/data directory is not configured to allow your host to connect.   (Note that if password authentication is required for your remote host, a password is required.  Unpassworded accounts can not connect.)

What data types does the driver support?
The driver provides full support for all PostgreSQL standard data types.   These are:  char, int2, int4, float4, float8, abstime, date datetime, text.

There is partial support for all other data types.   Examples of these: point, circle, box and arrays.    String support only is provided for these non-standard types.   In other words, they are returned as SQL_VARCHAR and can be displayed and updated like any other data type.   The resulting behavior will probably vary some with each application and data type.

In the case of int4[] and MS Access 97 it does an effective job.     The array can be displayed and updated cleanly.     Arithmetic is a little tricky.    MS Access complains about "t.a[1]" in the query builder.    It does not like the syntax and never sends it to the backend.

The work around is to choose the SQL Pass-thru option.  This will allow you to build expressions like "t.a[0] * t.a[1]".    The hassle is that every time you run the query in Access 97 it prompts you with a database connection dialog.

MS Excel in combination w/ MS Query may provide a better solution.  It passes every query through..   I try to stay away from the more exotic types if I know I am going to expose them to the public.

How do I get my application to recognize primary keys? 
SQLPrimaryKeys() is implemented in the driver.   As of the driver's release, however, there was no way to query the PostgreSQL system tables to discover a table's primary key.  Therefore the following convention was used.   The driver queries the system tables in search of a unique index named with the using "{table}_key".   Example:

create table foo  (id integer, name varchar(20));
create unique index foo_key on foo using btree(id);

Why does the PostgreSQL backend complain about running out of memory when some I browse tables in MS Access?
When an Access user specifies a key at link time, this key is used to generate queries to browse through the rows of the table.    When the key is multipart, the query looks something like:

--   This is a 3 part key
select ... from foo where
    (v1 = "?" AND v2 = "?" AND v3 ="?") OR        -- line 1
    (v1 = "?" AND v2 = "?" AND v3 ="?") OR        -- line 2
            ...
    (v1 = "?" AND v2 = "?" AND v3 ="?") OR        --  line 27
    (v1 = "?" AND v2 = "?" AND v3 ="?");              --  line 28
--  The question marks ar replaced with the key values

With MS Access, why can't I GROUP BY,  ORDER BY use WHERE on columns which are of type "text"?
Text fields are now being returned as SQL_LONGVARCHAR.  As a result MS Access treats these colomns as "Memo" types.  This good news and bad news.   The good news is that you can store up to the PostgreSQL block size limit  in a text column.  Currently, PostgreSQL has a tuple limit of just under 8k.
 

Can I use large objects or OLE?
Large objects are mapped to LONGVARBINARY in the driver to allow storing things like OLE objects in Microsoft Access.  Multiple SQLPutData and SQLGetData calls are usually used to send and retrieve these objects.  The driver creates a new large object and simply inserts its 'identifier' into the respective table.  However, since Postgres uses an 'Oid' to identify a Large Object, it is necessary to create a new Postgres type to be able to discriminate between an ordinary Oid and a Large Object Oid.  Until this new type becomes an official part of Postgres, it must be added into the desired database and looked up for each connection.  The type used in the driver is simply called "lo" and here is the command used to create it:

    create type lo (
        internallength=4,  externallength=10,
        input=int4in, output=int4out,
        default='',  passedbyvalue);

    create table employee (
        id integer,
        name varchar(30),
        picture lo
    )

Once this is done, simply use the new 'lo' type to define columns in that database.   When the driver sees an 'lo' type, it will handle it as SQL_LONGVARBINARY.

Another important note is that this new type is lacking in functionality.  It will not cleanup after itself on updates and deletes, thus leaving orphans around and using up extra disk space.  And currently, Postgres does not support the vacuuming of large objects.

It would not be too difficult to write a interim stand-alone cleanup process to run at some interval on the server.  It is only a matter of searching pg_attribute for lo data type columns and building a list of lo's by querying each table that contains lo's.  Then compare this list with with the xinv.* in pg_class.   The xinv.* with out a pointer are orphans and should be dropped.

Hopefully in the future, a real large object data type will be available as a base type.   But for now, it sure is fun to stick a Word document, Visio document, or AVI of a dancing baby into a database column, even if you will fill up your server's hard disk after a while!

Why when I join tables in MS Access the does it sometimes complain about a GROUP BY not being in the target list?
This message comes from the PostgreSQL backend.   Sometimes graphical query builders will generate unexpected queries in an atempt to optimize.  Often a single query will be broken into multiple queries.   The smaller queries will GROUB BY columns not in the target (projection).   The solution is to incrementally  include join columns which are not in the target list until the error is resolved.    Then, most graphical query builders will allow the user to hide the extranious columns from the visual field of the result.
 

Why does the browsing large tables lock the respective tables from updates?