Thread: Access 2K, views, rules not working...

Access 2K, views, rules not working...

From
"Fabrizio Mazzoni"
Date:
Hi all,

I have a problem to update a view from ms access 2k to potgresql. I am using
MS Access 2K, PostgreSQL 7.2b4, and ODBCPlus 1.0.0.0 to connect.

This is my db definition. (I have it simple for study purpososes)

create table one (
a int4 default nextval('sq1') primary key,   --already created sq1
b varchar(50),
c varchar(50)
);

create table two (
d int4 default nextval('sq2'), --already created sq2
e varchar(50),
f varchar(50),
foreign key d references one (a)
);

I also created a view. This because in access queries can be linked as views
from the server side..

create view v1 as
select one.*,two.* from one.two
where uno.a = two.d;

I linked the view in access with odbc as a table and then created th
necessary rules for update/delete/insert

create rule ins as on insert to v1 do instead (
insert into one....;
insert into two....;
);

create rule upd as on update to v1 do instead (
update one set.....where a = old.a;
update two set.....where d = old.d;
);

create rule del as on delete to v1 do instead (
delete from one where a = old.a;
delete from one where d = old.d;
);


My problem is that when i perform an update or delete from ms access, only
the values from table two get modified (a strange thing is that if i flip
the order of the sql statements of the rules then only table one gets
modified). The view returns the correct records because of the join, but it
seems that the rules are not executed correctly.

Is this an access problem or is it an odbc problem...    Any suggestions..?

Thanks in advance..

Fabrizio Mazzoni

www.macron.com
www.eteampoint.com
www.bolognafc.it
www.macrongolf.com
www.macronbaseball.com