Message-Id: <s18143ec.061@gwm.sc.edu>
Date: Thu, 28 Oct 2004 19:09:23 -0400
From: "Peter Bense" <Ptbense@gwm.sc.edu>
To: <pgsql-interfaces@postgresql.org>, <pgsql-odbc@postgresql.org>
Subject: ODBC / MS-Access... linking a ms-access DB to a view...
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

After spending some hours talking with the kind folks in #postgresql, I =
was unable to find someone who was able to point me in the right direction =
towards solving a data access problem between Postgresql, MS-Access 2000, =
and the PostgreSQL30 ODBC driver.

Here's my setup:
- Win2k Professional
- MS-Access 2000
- Postgresql-7.4.2 running on Gentoo Linux

After a considerable amount of netsleuthing, I finally able to find =
someone who had devised a way to implement Row Level Security with =
Postgres.  (As seen on: http://www.varlena.com/varlena/GeneralBits/77.php =
* scroll down to the "Proof of Concept" section...)

The basic idea is to create an additional userid field in your source =
table, then create a view of the source table which excludes the userid =
and apply rules for select, update, view and insert.

Here's a describe of a test view (which can be linked without problem in =
MSAccess), and the view that I created for my table (just in the testing =
phases now)
http://rafb.net/paste/results/gmYOkn43.html=20

This all seems to work without any problem whatsoever when I change the =
user and perform the select from the psql utility.  You can see how I have =
performed a test of this functionality here:
http://rafb.net/paste/results/rQHqmC51.html=20

Now, once I get to MS-Access, here is where stuff breaks:

1. In MS-Access 2000 * select File, Get External Data, Link Tables...
2. Select Files of Type ODBC Databases()
3. Select the postgresql datasource previously defined
4. Enter the database name, server name, port, username (in this case =
01BLUESHIELD) & password
5. The database connects fine (which indicates to me that there are no =
problems with the connection and/or permissions on the user account) and =
it shows all of the system schemas and 3 public tables.

The FIRST table is public.tblparticipant *> this is the view that I have =
created which, when selected, should only return the records (about 250 =
records) which are associated with the login (01BLUESHIELD) as per the =
rules mentioned earlier.  When selected, it returns an empty recordset, =
which makes NO sense, considering this code works fine from psql.

The SECOND table is the public.tblparticipants *> selecting from this =
table =3D access denied.  This makes sense given the privileges I =
established on the account.

The THIRD table is public.test *> this table (as shown in the link above) =
is a view of * in tblparticipants.  When I select this table, all 2406 =
records return normally, which indicates to me that the ODBC driver =
doesn't have any problem "handling" views as opposed to tables.

Does anyone have some insight as to what's going wrong or how to fix it?

I'd be greatly indebted.. maybe could even paypal someone a few bucks if =
they have some idea!


Kind regards,

./Peter T. Bense

Peter T. Bense (ptbense@gwm.sc.edu) - 803-777-9476
Database Administrator/Webmaster
Prevention Research Center
University of South Carolina