Query error: "Could not delete from specified tables" 1

[rcoutts]

I have a query that attempts to delete all the records of a table that have fields that match those of a second table. I had this problem before and someone recommended that I was trying to delete records from more than one table. Before I figured out a way to eliminate the reference, but in this case I really need it. I'm a SQL newbee, but here's my code for what its worth:

   DELETE tblInvoiceOldVars.*, [tblInvoiceOldVars].[InvoiceNum], [tblInvoiceOldVars].[JobNumber]
   FROM tblInvoiceOldVars, tblInvoiceCalcs
   WHERE ((([tblInvoiceOldVars].[InvoiceNum])=[tblInvoiceCalcs]![InvoiceNum]) And (([tblInvoiceOldVars].[JobNumber])=[tblInvoiceCalcs]![JobNumber]));

tblInvoiceOldVars is the table I want to modify. If I get rid of the reference to tblInvoiceCalcs in the FROM line then it works fine, except that I get prompted for the field values in tblInvoiceCalcs, which obviously isn't what I want.

Thanks in advance,
Rich

 

[igk]

Hi,

One thing that I have noticed is that you don't have a table relationship between tables in your FROM statement.

 

[rcoutts]

Thanks for the post. Unfortunately, I get the same errors regardless of whether or not I add a relationship. E.g.,

   DELETE tblInvoiceOldVars.*, tblInvoiceOldVars.InvoiceNum, tblInvoiceOldVars.JobNumber
   FROM tblInvoiceCalcs LEFT JOIN tblInvoiceOldVars ON (tblInvoiceCalcs.JobNumber = tblInvoiceOldVars.JobNumber) AND (tblInvoiceCalcs.InvoiceNum = tblInvoiceOldVars.InvoiceNum)
   WHERE (((tblInvoiceOldVars.InvoiceNum)=[tblInvoiceCalcs]![InvoiceNum]) AND ((tblInvoiceOldVars.JobNumber)=[tblInvoiceCalcs]![JobNumber]));

doesn't work either. I get the same error.

 

[tlbroadbent]

Try this query. It should work unless one of the tables is not updateable. By chance, are any of the tables LINKED from another source such as SQL Server?

DELETE tblInvoiceOldVars.*
FROM tblInvoiceOldVars
INNER JOIN tblInvoiceCalcs
ON tblInvoiceOldVars.InvoiceNum=tblInvoiceCalcs.InvoiceNum
AND tblInvoiceOldVars.JobNumber=tblInvoiceCalcs.JobNumber; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[rcoutts]

Terry,

Thanks for the code. I ran it, but am getting the same error -- "Could not delete from the specified tables." I'm new to Access am an not really sure what you mean when you ask: "are any of the tables LINKED from another source such as SQL Server?" My database is pretty small (twenty or so tables) and contained in a single .mdb file, along with the Query I'm trying to write. I don't link to any outside tables or anything else. Another .mdb file links into some of my tables, but not to either of the two I'm working with on this Query.

As a test I ran the Query:

   DELETE tblInvoiceOldVars.*
   FROM tblInvoiceOldVars;

and it worked fine, but obviously deleted the whole table which isn't what I'm after. Not sure if that tells you anything.

-Rich

 

[tlbroadbent]

Check the UniqueRecords property of the query. Make sure it says, "Yes". Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.