Solved

Delete query not working

Posted on 2011-09-13
5
324 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
  • 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

821 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