Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Delete query not working

Posted on 2011-09-13
5
Medium Priority
?
343 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 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

876 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