Question Updating SQL Server Views via MS Access front-end?

[tzvi]

Hello!

I have a "SQL View" link in access

if i try to change data on it... I get a messageas attached... but i know that no one is editing the file now... and i can also change data direct from "sql server manager" just not from access

If i need to redo the quiry in access this will be a pain for me...

what can i do so i can edit this view from access.

Thank you!​

 

[Mr. B]

If you have a "View" then it would be read only. If you need to change the data you would need to use an ODBC connection.

 

[mdlueck]

True SQL Server views are read-only. (As in their name... VIEW data, not change data.)

The easiest would be if you may (have permission) to Link to the SQL Server tables from Access.

External Data tab, Import, ODBC Database, Link to the source, and so on and so forth... what you end up with is an Access looking table link to the external database server connected via the ODBC driver.

 

[tzvi]

For you info... i managed to do what i wanted with the SQL view
it works only if you have a view of 1 table
not if a few tables are linked in the view

 

[mdlueck]

For you info... i managed to do what i wanted with the SQL view
it works only if you have a view of 1 table

Aparantely at least SQL Server supports Updatable Views with various restrictions:

CREATE VIEW (Transact-SQL)
Updatable Views
http://msdn.microsoft.com/en-us/library/ms187956.aspx

 

[Pat Hartman]

Access REQUIRES a unique index to allow a linked SQL Server table or view to be updateable. If you delete the current link and then add it back, Access will ask if you want to define a unique index. Be verrrrrrrrrrry careful with this. If you don't include all the necessary columns to define uniqueness, you could corrupt the tables if you update via the view.