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