Thread: RE: serious problem with Access ODBC connections and user account s

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@imsd.uni-mainz.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