Re: Is there a solution to this?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Siddharth

What is the constraint between the tables? Also, is it possible to have a
name with no address or vice versa? On a row in either table, are the name
or address columns NULLable? You don't spell these out in your question. If
you provide the DDL it would help. The engine probably can't tell what to
do with the VIEW you describe because and insert can introduce anomalies.
(INNER JOINS using a single column can imply a one side on the target table
in a one to many relationship. MDBs let you get away with that by making
dangerous assumptions.)

You don't say what version of SQL Server you are using, but you can use
INSTEAD OF triggers to spell out what to do on an INSERT using a VIEW. See
BOL.

Joe

"Siddharth Parekh" <skparekh@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:e%23$aAVbUFHA.3076@xxxxxxxxxxxxxxxxxxxxxxx
> Lets say I have two tables, table1 and table2 that have a one to one
relationship
>
> Table1 has two columns - number, name
> Table2 has two columns - number, address
>
> number is the primary key and is numeric format.
>
> If i combine these two using a View having a simple SELECT statement like
the following:
>
> SELECT table1.number, table1.name, table2.address
> FROM table1 INNER JOIN table2 ON table1.number=table2.number;
>
> This will give the output in the following format:
>
> NUMBER NAME ADDRESS
>
> The resulting recordset can be modified but if new records are added to
this, then it gives a write conflict. The same thing in a MDB works great.
>
> Any ideas how to overcome the write conflict?
>
> Thanks,
> Sid.


.

Relevant Pages

  • Re: Is there a solution to this?
    ... > Lets say I have two tables, table1 and table2 that have a one to one ... > The resulting recordset can be modified but if new records are added to ... then it gives a write conflict. ...
    (microsoft.public.access.adp.sqlserver)
  • Is there a solution to this?
    ... Lets say I have two tables, table1 and table2 that have a one to one relationship ... The resulting recordset can be modified but if new records are added to this, then it gives a write conflict. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Error When set form at Startup
    ... a reference to the Microsoft DAO Object Library. ... DIM of the conflict table. ... ' Open a recordset based on the conflict table. ...
    (microsoft.public.access.formscoding)
  • Re: Error When set form at Startup
    ... a reference to the Microsoft DAO Object Library. ... DIM of the conflict table. ... ' Open a recordset based on the conflict table. ...
    (microsoft.public.access.formscoding)
  • Speed of Code
    ... corresponding entries in Table2 and pull a couple of fields from Table2 into ... Table1 where those ITEMS are equal and the date is of the latest date. ... How I do this now is open table 1 as a recordset, ... When I reach the end of Table2 I use a DO LOOP to start table 1 at ...
    (microsoft.public.access.formscoding)