A question about views in sql server

[Mohamed Shafiee]

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.

 

[James Goodman]

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.

 

[Mohamed Shafiee]

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]

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.