Thread: connecting to postgres server from Access
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
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
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
------------------------------------------------------------
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
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