Re: ODBC / MS-Access... linking a ms-access DB to a view...

Hello Peter,

I don't know what your exact problem is, but here is a list of points I would test if I were you:

1) Test with another driver version.
The old installer installs version 7.03.02.00, and there is snapshot with version 7.03.02.08. Make sure to use the
secondversion, or the driver snapshot that has just been released for PGSQL 8. This is version 8.00.00.02. It works
finefor me, with a 7.5.4 database as well. 

If you use the 7.xx driver, you have three drivers actually: use "Postgresql", and not the Unicode or Legacy drivers.

Here is the link: http://gborg.postgresql.org/project/psqlodbc/genpage.php?downloads

2) Test with another MS Office installation. Weird installation?
3) Test with the postgres/pgsql user. Permission problem?
4) Use a pass-through query instead of a linked view. Primary key problem?

Regards,

Philippe Lang

-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de Peter Bense
Envoyé : vendredi, 29. octobre 2004 01:09
À : pgsql-interfaces@postgresql.org; pgsql-odbc@postgresql.org
Objet : [ODBC] ODBC / MS-Access... linking a ms-access DB to a view...

After spending some hours talking with the kind folks in #postgresql, I was unable to find someone who was able to
pointme in the right direction towards solving a data access problem between Postgresql, MS-Access 2000, and the
PostgreSQL30ODBC 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
LevelSecurity with Postgres.  (As seen on: http://www.varlena.com/varlena/GeneralBits/77.php * scroll down to the
"Proofof Concept" section...) 

The basic idea is to create an additional userid field in your source table, then create a view of the source table
whichexcludes 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  

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

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
tome that there are no problems with the connection and/or permissions on the user account) and it shows all of the
systemschemas and 3 public tables. 

The FIRST table is public.tblparticipant *> this is the view that I have created which, when selected, should only
returnthe 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 = access denied.  This makes sense given
theprivileges 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
selectthis 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
Universityof South Carolina 


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster