Re: A question about views in sql server

Firstly, I dont think you will be able to physically add new records, but
updating is certainly possible.

You will need a PK field in both tables. You will need an FK field in one
table.

The cardinality of a relationship is governed by the type of index. If your
field is the FK field, & is not the PK, it will default to a non-clustered,
non-unique index, & subsequently the relationship will be interpreted as
1-M, rather than 1-1. The solution is to make your FK index unique. A unique
index does not need to be clustered, but Access will default to making a
unique index clustered if there is not already a clustered index on the
table.



--
Cheers,

James Goodman
"Mohamed Shafiee" <shafee_mohamed@xxxxxxxxxxx> wrote in message
news:efhWAkzYFHA.3040@xxxxxxxxxxxxxxxxxxxxxxx
> Actually I tried to do that without success.
> Could you please tell me how?
>
> Here is what I did:
> I set a relationship between two tables using the database diagram, which
> should be a one-to-one relationship and is a one-to-many relationship, and
> made a view which would combine the fields of both table. When I try to
> edit the records, the status bar says recordset is not updatable... and
> also new records cannot be added to this view. I have the primary key set
> in one table, and the foreign key set in the other table. I don't know how
> to make a one-to-one relationship in SQL server, and make an updatable
> view.
>
> Please help me.
>
> Shafiee.
>
> "James Goodman" <jamesATnorton-associates.co.ukREMOVE> wrote in message
> news:een136gYFHA.2420@xxxxxxxxxxxxxxxxxxxxxxx
>> In a word, yes!
>>
>> As long as you have PK/FK fields on both tables (so that both tables are
>> updateable individually), then this shouldnt be a problem.
>>
>> --
>> Cheers,
>>
>> James Goodman
>> "Mohamed Shafiee" <shafee_mohamed@xxxxxxxxxxx> wrote in message
>> news:OKx3c1gYFHA.3712@xxxxxxxxxxxxxxxxxxxxxxx
>>> Hi,
>>>
>>> I just want to find out if it is possible to create views in SQL server
>>> which can be updated in an adp project. Can we create a one-to-one
>>> relationship between two tables in SQL server using database diagram,
>>> and achieve an updateable view? How can we do this in MSAccess adp
>>> projects?
>>>
>>> Shafiee.
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: A question about views in sql server
    ... and the foreign key set in the other table. ... a one-to-one relationship in SQL server, ... > As long as you have PK/FK fields on both tables (so that both tables are ... >> Shafiee. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Write Conflict
    ... I have to download the latest service pack for SQL server. ... > Shafiee. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: How to add a database diagram by means of a script
    ... There's no scripting option available for a database diagram, ... Tibor Karaszi, SQL Server MVP ... "Abhishek Srivastava" wrote in message ... > I am having a script which creates the entire db schema. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL 2005 dbo user issue
    ... So there is no way for me to create a Database diagram on a SQL 2000 database ... using SQL Server management studio 2005 even though that database diagrams is ... > can't go the other way regardless of the database compatibility level. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL 2005 - Disappearing Relationships in DB Diagram
    ... database diagram using SQL Server 2005. ... are the foreign key constraints really disappearing ... David Portas, SQL Server MVP ... of any error messages. ...
    (comp.databases.ms-sqlserver)