Re: [INTERFACES] calculated identity field in views, again...

Hello. Thanks for answers...
After considering all proposed, I think that it is probably possible to give
MS Acces some composite primary keys while linking views as tables, in order
to help Access not to fall into "#deleted#", but it would take some extra
time to experiment with every view.
In meantime, I successfully implemented solution with local tables. Append
queries based on pass-through queries are triggered and local tables are
refreshed. It seems to be fast and reliable...
Thank you anyway, maybe I will try something with views next time...

----- Original Message -----
From: "Jeff Eckermann" <jeff_eckermann(at)yahoo(dot)com>
To: "Zlatko Matic" <zlatko(dot)matic1(at)sb(dot)t-com(dot)hr>;
<pgsql-general(at)postgresql(dot)org>; <pgsql-interfaces(at)postgresql(dot)org>
Sent: Wednesday, May 04, 2005 6:01 PM
Subject: Re: [GENERAL] [INTERFACES] calculated identity field in views,
again...

> --- Zlatko Matic <zlatko(dot)matic1(at)sb(dot)t-com(dot)hr> wrote:
>> I asked this question several weeks ago, but nobody
>> proposed a solution, so
>> I am repeating the same question again...
>> I have an MS Access front-end for a database on
>> PostgreSQL.
>> I could use pass-through queries as record sources
>> for reports and it works
>> fine...
>> Unfortunately, MS Access doesn't allow pass-through
>> queries to be records
>> sources for subforms.
>
> Unless you use unbound form/controls. Which means
> handling everything in code, which might work out best
> for you, depending on what you want (this is
> effectively equivalent to the VB-only option which
> someone else mentioned).
>
>> Therefore I tried to base subforms on regular JET
>> queries on linked tables.
>> It was too slow...
>> Then I tried to base subforms on DAO recordset code
>> generated from
>> pass-through QueryDef objects. Although it worked,
>> it was very unstable...
>>
>> Now it seems to me that POstgreSQL views are the
>> best solution, but Access
>> considers views as tables (!) and needs column with
>> unique values.
>
> AFAIK a composite key (combination of several columns)
> should work ok for a primary key for Access. When
> linking to the view, just select the columns you want
> to use. Or are you saying that you tried this, and it
> didn't work?
>
> Alternatively, you could try including in your view
> definition the oid column for each of the constituent
> tables. If I understand right, oids are globally
> unique within your database. This assumes that you
> have created your tables with oids, which may not be
> the case.
>
> Basing a subform on a mult-table join sounds like odd
> database design. Perhaps if you can explain more
> about what you are trying to do, people can offer more
> suggestions.
>
>> All those views are complicated queries on several
>> tables, so I can't use
>> any table's column as primary key. I need a
>> calculated column in the view
>> that Access will consider as primary key column.
>> In regular tables, I use bigserial field, but how
>> can I create calculated
>> bigserial column in a view ?
>>
>> Thanks.
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 9: the planner will ignore your desire to choose
>> an index scan if your
>> joining column's datatypes do not match
>>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>