Re: [SQL] connecting to postgres server from Access

On Thu, 11 Jan 2001, Markus Wagner wrote:

> first of all, I looked at the mailing list list at th pg web site and I
> did not find a list named "pgsql-interfaces".

Hmmm. I see it at

http://www.postgresql.org/users-lounge/index.html

Perhaps you looked elsewhere, or you saw an old mirror? (In any event, you
can get to the pgsql-interfaces archive at

http://www.postgresql.org/mhonarc/pgsql-interfaces/

which is great place to catch up on all the ODBC/Access questions of the
past.)

> In pg_hba.conf I added one line at the bottom with the IP of the client:
>
> host all 134.93.64.47 255.255.255.255 trust
>
> In Access, after installing the pg db as system DSN source, linking to
> it and selecting a table and attributes, I get:
>
> "cannot create index for the selected field"
>
> Then the linked table appears in the tables tab in Access. When trying
> to open it, I get ("Organisation" is the table to be linked):
>
> "ODBC error:
> error while executing the query
> ERROR: Organisation: Permission denied (#1)"

Haven't seen this exact error before.

Are you sure that you found and turned off BOTH read-only options in the
ODBC driver configuration? (I'm just wondering if this "can't
create" error is a strange read-only twist.)

Also, you might try to take the error at face value: try to create an
index on the Org field using psql, and see if you run across any errors.

> I have searched for Windows pg tools but I didn'*t find one.

The nicest Windows tool is pgAdmin, a fantastic tool to administer almost
every aspect of your PG database under Windows. You can find it at
www.greatbridge.org. It can be a *bit* tricky to install, as it has a few
prerequisites, but I think you'd find it worth your time.

Slimmer but less featureful is Zeos Database Explorer, available at

http://www.zeos.dn.ua/eng/index.html

This is basically a GUI version of psql.

Either of these tools will let you try your query out from the Windows
box, and will help us figure out whether this is a PG problem, an
ODBC problem, an Access problem, etc.

My advice?

Get a Windows querying tool *other* than Access to just test the basics of
the ODBC connection, and post your results from that.

Also, you should compare your ODBC setup to the basic setup described in
the Pg+Access FAQ at www.scw.org/pgaccess, and write to the interfaces-
list with your settings, as well as a small pg_dump of your database so
that people can try to recreate your problem.

Good luck!

JB