Thread: MS Access can't insert with ODBC connection to PostgreSQL

Hello, I downloaded the latest ODBC driver for PostgreSQL from
http://www.insightdist.com/psqlodbc/ version number 06.30.0245.  I got
this driver to work and show data from my database after editing the
pg_hba.conf file.  However, I can't insert new records in the normal
access way.  I can do these inserts when I connect to MS SQL server with
an ODBC driver in the same manner as above.  Is this typical? Or do I
have something configured wrong?  I unchecked both readonly checkboxes
in the ODBC config, but that had no effect.  I was able to do a manual
insert query by hardcoding it.

Please help,

Thanks much,

Matt O'Connor

Matthew wrote:

> Hello, I downloaded the latest ODBC driver for PostgreSQL from
> http://www.insightdist.com/psqlodbc/ version number 06.30.0245.  I got
> this driver to work and show data from my database after editing the
> pg_hba.conf file.  However, I can't insert new records in the normal
> access way.  I can do these inserts when I connect to MS SQL server with
> an ODBC driver in the same manner as above.  Is this typical? Or do I
> have something configured wrong?  I unchecked both readonly checkboxes
> in the ODBC config, but that had no effect.  I was able to do a manual
> insert query by hardcoding it.
>

 Access must know a tables key before it will allow updates.   Either make
sure "Recognize Unique Indexes" is check (under Driver Options) or manually
specify the key at link time.    Oid may be used as a key it it is
checked.     In any case you must relink the table to get Access to
recognize the your keys.

Matthew wrote:

> Hello, I downloaded the latest ODBC driver for PostgreSQL from
> http://www.insightdist.com/psqlodbc/ version number 06.30.0245.  I got
> this driver to work and show data from my database after editing the
> pg_hba.conf file.  However, I can't insert new records in the normal
> access way.  I can do these inserts when I connect to MS SQL server with
> an ODBC driver in the same manner as above.  Is this typical? Or do I
> have something configured wrong?  I unchecked both readonly checkboxes
> in the ODBC config, but that had no effect.  I was able to do a manual
> insert query by hardcoding it.
>

In addition to the datasource not being read-only, Access requires a unique
identifier to be able to update any records.  The driver, by default, does
not report unique identifiers automatically.  Either you must enable the
"recognize unique indexes" advanced driver option OR, if you are using
Access 95 or 97, you can manually select the unique identifier while linking
the table.

Hope that helps.

Byron