Skip site navigation (1) Skip section navigation (2)

RE: serious problem with Access ODBC connections and user accounts

From: "Henshall, Stuart - WCP" <SHenshall(at)westcountrypublications(dot)co(dot)uk>
To: "'Markus Wagner'" <wagner(at)imsd(dot)uni-mainz(dot)de>, pgsql-interfaces <pgsql-interfaces(at)postgresql(dot)org>
Subject: RE: serious problem with Access ODBC connections and user accounts
Date: 2001-07-23 08:37:53
Message-ID: E2870D8CE1CCD311BAF50008C71EDE8E01F74636@MAIL_EXCHANGE (view raw or flat)
Thread:
Lists: pgsql-interfaces
Hello,
	Using MS Access 2000 if I link all the tables at once and don't save
the password when you first use a linked table it then brings up the ODBC
settings/password box. It then lets you use any of the tables linked at the
same time.
	However that isn't the way I do it. How I do it is to ask for a
username and password and the open an ODBC direct workspace like so:
csp = "ODBC;DRIVER={PostgreSQL};UID=" & unm & ";PWD=" & pd & ";DATABASE=" &
DBName & ";SERVER=" & DBSrvr &
";PORT=5432;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIO
NING=1;SHOWSYSTEMTABLES=0;CONNSETTINGS="
Set pg_bend = DBEngine.Workspaces(0).OpenDatabase("", False,
dbDriverNoPrompt, csp)
where unm is the username & pd is the password.
I then recreate all the tables I want (having previously having made sure
any linked tables are removed):
cs = "ODBC;DRIVER={PostgreSQL};DATABASE=" & DBName & ";SERVER=" & DBSrvr &
";PORT=5432;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIO
NING=1;SHOWSYSTEMTABLES=0;CONNSETTINGS="
rem (Note: No username and password on this connection string)
    Set td = CurrentDb.CreateTableDef("access_table _name")
    td.Connect = cs
    td.SourceTableName = "postgresql_table_name"
    CurrentDb.TableDefs.Append td
    CurrentDb.TableDefs.Refresh
I then leave the form open (but hidden) and also pass all the pass through
queries to it to be executed using pg_bend.openrecordset or pg_bend.execute
depending.
Hope this helps,
- Stuart
> -----Original Message-----
> From:	Markus Wagner [SMTP:wagner(at)imsd(dot)uni-mainz(dot)de]
> Sent:	Thursday, July 19, 2001 12:30 PM
> To:	pgsql-interfaces
> Subject:	serious problem with Access ODBC connections and user
> accounts
> 
> Hi,
> 
> we have a serious problem with connecting to Postgres from Access.
> We need to log user access to tables using triggers.
> When linking PG tables into the Access frontend with ODBC, Access stores
> user name and password for each table link.
> Even worse: There will be a database connection for each linked table.
> 
> We have two choices:
> 
> 1. create a postgres user for each frontend user
> 2. let all frontend (instances) connect with the same user account
> 
> Problem of 1.:
> 
> Since the user names and passwords are stored for each linked table, we
> cannot get a connection with the current Access user's name and pwd. We
> would have to make new links for all tables each time another user
> starts the application.
> 
> Problem of 2.:
> 
> We would do the following to get the Access user's name into our
> triggers:
> 
> - On application startup some code calls our function "myLogin",
>   which receives the user name, creates a temporary table
>   (which is different for each connection), and inserts the
>   user name into this table.
> 
> - Our triggers would lookup the user name from the temporary table.
> 
> This approach seems to be very useful, but there is one drawback:
> 
> Access makes a new connection for each table that is opened.
> So the temporary table created by the myLogin function would
> be visible only within one connection.
> 
> What can we do? We believe that these problems must exist in
> every project which uses PG on Linux and Access on NT as a frontend.
> There must be a solution. Please help.
> 
> Thank you,
> 
> Markus

pgsql-interfaces by date

Next:From: veronique DROUELLEDate: 2001-07-23 11:52:59
Subject: URGENT - PgAdmin
Previous:From: Eugene FokinDate: 2001-07-23 07:23:50
Subject: Re: libpgtcl doesn't use UTF encoding of TCL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group