Solved

Delete query not working

Posted on 2011-09-13
5
327 Views
Last Modified: 2012-06-27
When trying to run the query below I get a "Could not delete from specified tables" message.    I'm using Access 2010 and SQL Server 2005.  [Facility Info] is a linked Excel spreadsheet.  I am trying to delete records in tblFacilites that are not in the linked spreadsheet.  Any ideas on how to do this would be great.  

Thanks,

DELETE  tblFacilities.* 
FROM [Facility info] RIGHT JOIN tblFacilities ON [Facility info].nhid = tblFacilities.nhid
WHERE ((([Facility info].nhid) Is Null));

Open in new window

0
Comment
Question by:eshurak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 36533509

DELETE  *
FROM  tblFacilities
Where tblFacilities.nhid Not In (Select [Facility info].nhid From [Facility info])
0
 
LVL 1

Expert Comment

by:csense
ID: 36534192
you don't need to specify any fields after DELETE (e.g. *) because DELETE deletes everything on that row.

So query should be:
DELETE
FROM  tblFacilities 
Where tblFacilities.nhid Not In (Select [Facility info].nhid From [Facility info])

Open in new window

0
 
LVL 1

Expert Comment

by:csense
ID: 36534227
OR:

DELETE 
FROM tblFacilities
	FROM tblFacilities AS a
		LEFT JOIN [Facility Info] AS b ON a.nhid = b.nhid
WHERE b.nhid IS NULL;

Open in new window

0
 
LVL 10

Expert Comment

by:ukerandi
ID: 36535395
Write below code in Query and run

Select *
FROM [Facility info] RIGHT JOIN tblFacilities ON [Facility info].nhid = tblFacilities.nhid
WHERE [Facility info].nhid) Is Null;

check are any data in the query,some time you are tring to delete records not in the baove criteria

then
run below code
Delete
FROM [Facility info] RIGHT JOIN tblFacilities ON [Facility info].nhid = tblFacilities.nhid
WHERE [Facility info].nhid) Is Null;
0
 
LVL 10

Expert Comment

by:ukerandi
ID: 36535404
I presume You are using Right Join b'cos "The RIGHT JOIN keyword returns all the rows from the right table (tblFacilities), even if there are no matches in the left table (Facility info)."

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question