Moved Access DB Queries to SQL Server Views Problem

[kayek]

I have an Access Database linked to many SQL Server Tables. Each of the Access Database Forms had a query for it's record source. To try to speed up our database, I replaced the Queries behind the forms with SQL Server Views.

This works great EXCEPT if update information in a forms that write to multiple tables. The following error occurrs

"not updatable because the modification affects multiple base tables."

Is there anything I can do to resolve this?

To fix the problem on some forms I created subforms so each form/subform just wrote to one table but I don't like doing this because each form has required fields that need to be entered and when I move things to a sub forms it just makes things more complicating for checking required fields have been updated. The required fields change depending on the status and type of account. HELP!

 

[lameid]

If you open the view in the appropriate SQL server tool (names radically different between versions), can you change the data?

If so continue here, otherwise I recommend one of the SQL forums... You might get lucky and get one of the guys that knows both if you keep posting here. When you post in the SQL server forum be sure to say you made a view and you can't get it to update in xyz SQL server tool. DO NOT MENTION ACCESS: about a third of them are two dense to realize it is an SQL server problem and will tell you to post in the Access forum withou having actually read your question.

 

[kayek]

If I make my updates to the data using my SQL View in Enterprise Manager I do not get any kind of error about can not update data in multiple tables. I only get this error when I update the data using Access Database linked SQL Server View. I get the error when using the access forms and if update directly to the view from the Access Database.

 

[lameid]

I would relink the view and specify the fields to uniquely identify the record again.

 

[cmmrfrds]

Can you paste in the update statement that you refer to here.
"If I make my updates to the data using my SQL View in Enterprise Manager".
OR did you update in the data grid in EM?