Skip site navigation (1) Skip section navigation (2)

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

From: "Zlatko Matic" <zlatko(dot)matic1(at)sb(dot)t-com(dot)hr>
To: "Jeff Eckermann" <jeff_eckermann(at)yahoo(dot)com>,<pgsql-general(at)postgresql(dot)org>, <pgsql-interfaces(at)postgresql(dot)org>
Subject: Re: [INTERFACES] calculated identity field in views, again...
Date: 2005-05-04 20:33:23
Message-ID: 003001c550e8$854f03f0$ea861dc3@zlatkovyfkpgz6 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-interfaces
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)
> 


In response to

pgsql-interfaces by date

Next:From: Ragnar HafstaĆ°Date: 2005-05-04 22:40:50
Subject: Re: [INTERFACES] calculated identity field in views,
Previous:From: Tom LaneDate: 2005-05-04 18:19:49
Subject: Re: PQescapeBytea & PQunescapeBytea

pgsql-general by date

Next:From: Peter WilsonDate: 2005-05-04 20:40:29
Subject: Re: Postgres vs Firebird?
Previous:From: Karsten HilbertDate: 2005-05-04 20:30:59
Subject: Re: Postgres vs Firebird?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group