Linked View: Could not delete from specified tables

D

DraguVaso

Hi,

I have a table (oas_dochead) for which I have made a View like this on my
SQL SERVER:
CREATE VIEW vw_oas_dochead
AS
SELECT *
FROM oas_dochead
WHERE (cmpcode = SUSER_SNAME())
WITH CHECK OPTION


I linked that View to my Access-application, and in that access-application
I try to delete the records from that View with a Delete-Query like this:
"DELETE FROM dbo_vw_oas_dochead". Unfortunately this gives me the error
"Could not dlete from specified tables".

I tryed alreaddy adding a "UniqueRecords" to my Delete Query, but that
doesn't change anything...

Does anybody knows how to do this? Or can't I delete from a linked View?

Thanks a lot in advance,

Pieter
 
B

Brian

DraguVaso said:
Hi,

I have a table (oas_dochead) for which I have made a View like this on my
SQL SERVER:
CREATE VIEW vw_oas_dochead
AS
SELECT *
FROM oas_dochead
WHERE (cmpcode = SUSER_SNAME())
WITH CHECK OPTION


I linked that View to my Access-application, and in that access-application
I try to delete the records from that View with a Delete-Query like this:
"DELETE FROM dbo_vw_oas_dochead". Unfortunately this gives me the error
"Could not dlete from specified tables".

I tryed alreaddy adding a "UniqueRecords" to my Delete Query, but that
doesn't change anything...

Does anybody knows how to do this? Or can't I delete from a linked View?

Thanks a lot in advance,

Pieter

In order for a linked view to be updatable Access needs to know the primary
key. If you didn't set this when you created the link, you should delete
the link and do it again.
 
D

DraguVaso

Hm I had done that, but when I checked it to be sure, I saw that the primary
key was disappeared...

I also found why: I have a routine that sometimes runs, to relink the tables
automaticaly to the DataBase (does the same thing as the Linked Table
Manager in the Tools -> Database Utilities).... On that moment the primary
key disappears...

Does anybody knows how I can prevent the Primary Key to disappear?

Thanks a lot alreaddy for putting me in the good direction,

Pieter
 
6

'69 Camaro

Hi, Pieter.

In addition to Brian's advice, several other things need to be considered to
enable data deletions from a SQL Server view. The first thing to consider
is the "WITH CHECK OPTION" that was used to create this view. One cannot
delete _any_ rows of the view when this option is specified -- which is what
you are attempting to do within Access.

Deleting data from SQL Server tables is best done with either stored
procedures or with triggers. A properly written stored procedure is the
more reliable method of the two. With these methods, any related records in
related tables can be dealt with when a record or column value is deleted.
You don't mention whether there are any related tables, but in most
relational databases this is often the case, so you need to check whether
deleting a specific record in the oas_dochead table would violate relational
integrity constraints.

You also don't mention which version of SQL Server is being used. SQL
Server 7.0 won't allow triggers on a view, so any triggers would have to be
assigned to the base tables, which can be tricky with related tables. SQL
Server 2000 allows triggers on views, so it's easier to use triggers more
reliably than in SQL Server 7.0.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
D

DraguVaso

Hi,

Unfortunately I must use the WITH CHECK OPTION, because people should be
able to delete recors, but ONLY records that they can see in the View...

Everything works fine now, except when I relink the Tables... Than I lose my
primary keys on the linked table (view) and it doesn't work anymore
afterwarths...
 
6

'69 Camaro

Hi, Pieter.
Unfortunately I must use the WITH CHECK OPTION, because people should be
able to delete recors, but ONLY records that they can see in the View...

I checked the documentation and SQL Server is using the ANSI SQL standard,
so using a DELETE query instead of an UPDATE query allows one to work around
the restriction I mentioned when using the WITH CHECK OPTION when creating
the view. However, one needs to be aware of the possibility of unexpected
results in data sets after rows are deleted from the view. This kind of bug
in a database application can be very difficult to track down for the
unwary. And most users don't notice "missing" records, so it could be quite
a while before the cause of incorrect data (based upon those "unexpected
results") in the database is discovered.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
H

Hugo Kornelis

In addition to Brian's advice, several other things need to be considered to
enable data deletions from a SQL Server view. The first thing to consider
is the "WITH CHECK OPTION" that was used to create this view. One cannot
delete _any_ rows of the view when this option is specified -- which is what
you are attempting to do within Access.

Hi Gunny,

PMFJI, but this is not correct. I admit that the wording in Books Online
is less than obvious. The effect of WITH CHECK OPTION is:

* For INSERT ==> check that the row(s) to be inserted will be visible in
the view.
* For UPDATE ==> check that the row(s) to be changed will remain visible
in the view.
* For DELETE ==> no limitations.

For example, if you create a view as

CREATE VIEW TestView
AS SELECT Name, Age
FROM Persons
WHERE Age BETWEEN 30 AND 40
WITH CHECK OPTION

Now, you can insert rows through this view, but only if the age is between
30 and 40. You can also change rows through this views, but you can't set
any age to less than 30 or more than 40. Without the WITH CHECK OPTION,
other ages could be specified in insert or update as well.

Of course, you can never use this view to update or delete rows where the
current age is not between 30 and 40, but that is unrelated to the WITH
CHECK OPTION. Those rows are simply invisible to this view, so they can't
be touched.

Best, Hugo
 
6

'69 Camaro

Hi, Hugo.

Please see my earlier reply with my correction to this post that you're also
correcting. The DELETE query is the work-around for using the UPDATE query
that attempts to remove rows from the VIEW, but instead results in the SQL
Server error message "The attempted insert or update failed because the
target VIEW either specifies WITH CHECK OPTION or spans a VIEW that
specifies WITH CHECK OPTION and one or more rows resulting from the
operation did not qualify under the CHECK OPTION constraint. The statement
has been terminated."

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top