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
Click to expand...

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...
Click to expand...

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.)
 
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.)
 
Top