Error message when deleting with attached SQL Server view

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I have an Access application that links to SQL Server. One part of
the application allows for building lists of contacts. As you're building
the list it stores the person id (ContactID) in a front end table. When I
try running this line of code

DELETE TLB.* FROM TLB LEFT JOIN
vwTLBProspectiveBuyerDevelopment ON TLB.ContactID =
vwTLBProspectiveBuyerDevelopment.ContactID

I get this error message "Could not delete from specified table". I'm sure
it's because I link to the SQL Server view "vwTLBProspectiveBuyerDevelopment"
to figure out which rows in the front-end temp table to delete.

Any way to rewrite this SQL to not have this problem?

Thanks
 
Why are you using a Left Join? this join will return all the records from
table TLB reardless to what you have in the view.
If you want to delete all the records that are in table TLB and exist in the
view then change the link to Inner Join

DELETE TLB.* FROM TLB INNER JOIN
vwTLBProspectiveBuyerDevelopment ON TLB.ContactID =
vwTLBProspectiveBuyerDevelopment.ContactID

If you want to delete all the records from TLB, then rempove the link to the
view

DELETE TLB.* FROM TLB

Another way to link the tables and delete the records from TLB will be with
a sub query

DELETE TLB.* FROM TLB INNER JOIN Where ContactID In (Select ContactID From
vwTLBProspectiveBuyerDevelopment)


In any case BAck Up your data first
 
Thanks for the response, In the actual sql; I have a where statement but
left it out because I get the same issue.
If I change it to a inner join I still receive the same message. I had
tried a subquery but it was just to slow, maybe I'll try to take out some
joins in my SQL Server view and see if access can then understand it
 
Try adding DISTINCTROW to the query

DELETE DISTINCTROW TLB.* FROM TLB INNER JOIN
vwTLBProspectiveBuyerDevelopment ON TLB.ContactID =
vwTLBProspectiveBuyerDevelopment.ContactID
 
Thanks, adding the distinctrow worked

Ofer Cohen said:
Try adding DISTINCTROW to the query

DELETE DISTINCTROW TLB.* FROM TLB INNER JOIN
vwTLBProspectiveBuyerDevelopment ON TLB.ContactID =
vwTLBProspectiveBuyerDevelopment.ContactID
Click to expand...
 
Back
Top