Updating a view based on multiple tables?

[Guest]

I have a linked view based on four tables in SQL Server that I use as the
RecordSource for a form in Access, which I am using as the front-end for my
database. I figured out how to make the records updatable by created a data
definition query that defined a unique index for the view (the PK of one of
my tables).

However, if I try to update records from more than one table, I get an ODBC
error. The same thing happens if I try to delete a record. The error looks
like this:

"View or function 'dbo.MyView' is not updatable because the modification
affects multiple base tables. (#4405)"

When I try to add records I get an ODBC error telling me that there is a
conflict with the PK field (which is called BookID). The error looks like
this:

"Cannot insert explicit value for identity column in table 'Book' when
IDENTITY_INSERT is set to OFF. (#544)"

Although I defined the PK field as an identity column in SQL Server, for
some reason when I linked the view to Access it made this field a Number
type, as opposed to Autonumber, which I would have preferred.


So, I have a few questions:

1) Is it possible to update and delete records based on more than one table
in a form, and if so, how is this done?

2) Is it possible to insert a record into SQL Server from a form in Access,
and if so, how is this done?

3) Is there a way to get Access to recognize my PK identity column as an
Autonumber data type, and if so, how?


If I have not provided enough information to answer these questions, please
let me know and I will do my best to provide whatever information is
necessary.

Any information you could give me would be appreciated.

Thanks,

Chris

 

[c120898901]

I have a linked view based on four tables in SQL Server that I use as the
RecordSource for a form in Access, which I am using as the front-end for
my
database. I figured out how to make the records updatable by created a
data
definition query that defined a unique index for the view (the PK of one
of
my tables).

However, if I try to update records from more than one table, I get an
ODBC
error. The same thing happens if I try to delete a record. The error
looks
like this:

"View or function 'dbo.MyView' is not updatable because the modification
affects multiple base tables. (#4405)"

When I try to add records I get an ODBC error telling me that there is a
conflict with the PK field (which is called BookID). The error looks like
this:

"Cannot insert explicit value for identity column in table 'Book' when
IDENTITY_INSERT is set to OFF. (#544)"

Although I defined the PK field as an identity column in SQL Server, for
some reason when I linked the view to Access it made this field a Number
type, as opposed to Autonumber, which I would have preferred.


So, I have a few questions:

1) Is it possible to update and delete records based on more than one
table
in a form, and if so, how is this done?

2) Is it possible to insert a record into SQL Server from a form in
Access,
and if so, how is this done?

3) Is there a way to get Access to recognize my PK identity column as an
Autonumber data type, and if so, how?


If I have not provided enough information to answer these questions,
please
let me know and I will do my best to provide whatever information is
necessary.

Any information you could give me would be appreciated.

Thanks,

Chris