Thread: connecting to postgres server from Access

connecting to postgres server from Access

From
Markus Wagner
Date:
Hi,

I'm running a postgres db server on my linux machine. Someone else would like 
to build his frontends with MS Access and use the postgres server as backend. 
He installed the Pg/ODBC-Driver linked to at the pg web site. But when he 
tries to link in some table he always gets an error message about wrong 
authentication. I inserted his IP address in the pg_hba.conf file and created 
a linux user account for him as well as a pg user account within "template1". 
So he could log in and use "createdb" to create his own db. I tried to set 
his password within pgaccess, but pgaccess claims about wrong input (""). 
Leaving the password field empty doesn't help to get the connection.  Then I 
set his password with "ALTER USER" in pgsql. But there still is no connection 
from access to pg yet.

Could someone help?

Thanks,

Markus


Re: connecting to postgres server from Access

From
Joel Burton
Date:
On Wed, 10 Jan 2001, Markus Wagner wrote:

> Hi,
> 
> I'm running a postgres db server on my linux machine. Someone else would like 
> to build his frontends with MS Access and use the postgres server as backend. 
> He installed the Pg/ODBC-Driver linked to at the pg web site. But when he 
> tries to link in some table he always gets an error message about wrong 
> authentication. I inserted his IP address in the pg_hba.conf file and created 
> a linux user account for him as well as a pg user account within "template1". 
> So he could log in and use "createdb" to create his own db. I tried to set 
> his password within pgaccess, but pgaccess claims about wrong input (""). 
> Leaving the password field empty doesn't help to get the connection.  Then I 
> set his password with "ALTER USER" in pgsql. But there still is no connection 
> from access to pg yet.

First of all, questions about interfacing to PostgreSQL are better
directed to pgsql-interfaces list. You might not get much help posting to
the sql list.


What kind of authorization are you using in pg_hba.conf? (can you post the
line from pg_hba.conf for him)

What error message does he get in Access?

Can he connect to the database from another Linux/Unix machine via
psql? (or, from his Windows machine using a PostgreSQL-compatible command
interface, like isql)?

There's a FAQ on PostgreSQL + Access at www.scw.org/pgaccess. It may help
as well.

Good luck,
-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington



Re: connecting to postgres server from Access

From
Markus Wagner
Date:
Hi Joel,

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".

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 queryERROR: Organisation: Permission denied (#1)"

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

Please help,

Markus

Joel Burton wrote:
> 
> On Wed, 10 Jan 2001, Markus Wagner wrote:
> 
> > Hi,
> >
> > I'm running a postgres db server on my linux machine. Someone else would like
> > to build his frontends with MS Access and use the postgres server as backend.
> > He installed the Pg/ODBC-Driver linked to at the pg web site. But when he
> > tries to link in some table he always gets an error message about wrong
> > authentication. I inserted his IP address in the pg_hba.conf file and created
> > a linux user account for him as well as a pg user account within "template1".
> > So he could log in and use "createdb" to create his own db. I tried to set
> > his password within pgaccess, but pgaccess claims about wrong input ("").
> > Leaving the password field empty doesn't help to get the connection.  Then I
> > set his password with "ALTER USER" in pgsql. But there still is no connection
> > from access to pg yet.
> 
> First of all, questions about interfacing to PostgreSQL are better
> directed to pgsql-interfaces list. You might not get much help posting to
> the sql list.
> 
> What kind of authorization are you using in pg_hba.conf? (can you post the
> line from pg_hba.conf for him)
> 
> What error message does he get in Access?
> 
> Can he connect to the database from another Linux/Unix machine via
> psql? (or, from his Windows machine using a PostgreSQL-compatible command
> interface, like isql)?
> 
> There's a FAQ on PostgreSQL + Access at www.scw.org/pgaccess. It may help
> as well.
> 
> Good luck,
> --
> Joel Burton   <jburton@scw.org>
> Director of Information Systems, Support Center of Washington

-- 
------------------------------------------------------------
Markus Wagner
Institut fuer Medizinische Statistik und Dokumentation
Johannes-Gutenberg-Universitaet
55101 Mainz
Tel.: 06131-17-5062         E-Mail: wagner@imsd.uni-mainz.de
------------------------------------------------------------


Re: connecting to postgres server from Access

From
Joel Burton
Date:
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



Re: connecting to postgres server from Access

From
Markus Wagner
Date:
Hi Joel!

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

Ok, my fault...

> > "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.)

This might be an important hint. In the system ODBC configuration I
found that the connection was configured read-only, two times. I changed
both to not to be read-only. But nothing changed.

Then I found under "Protocol" three alternatives: "6.5/6.4", "6.3",
"6.2". So if this is the pg version then I have a problem, because I am
using pg 7.0.

> 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 also tried that. No changes.

> 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.

I tried it. The command "show tables" lists my tables. But when making a
"SELECT * FROM <sometable>" I get "relation <sometable> does not exist".
Very strange...

> 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.

Seems to be a pg problem?

Markus