Delete Query not working

[ejstefl]

Hello all,

I'm having a problem running a delete query. The query is designed to remove any previously existing relationships in a many-to-many table when you import an account that already exists. I have 3 tables in the query:

1) tblTmpImport - this is a table of data I'm trying to import into the database.

2) tblAccounts - this is the table that holds all account information, and is linked to tblTmpImport on AccountNumber.

3) tblAccountsInvestmentFirms - this is a many to many table which links accounts to investment firms. It is linked to tblAccounts on AccountID. This is the table I'm trying to delete from.

When I run the query as a SELECT query, it appropriately selects all of the records I want to delete. However, when I change it to a DELETE query and run it, I recieve an error message stating "Could Not Delete from the specified table."

I've done some experimenting, and this problem only seems to occur when tblTmpImport is present. However, I need that table to limit the number of entries deleted from tblAccountsInvestmentFirms.

My current SQL is below:

DELETE tblAccountsInvestmentFirms.*
FROM tblTmpImport INNER JOIN (tblAccountsInvestmentFirms INNER JOIN tblAccounts ON tblAccountsInvestmentFirms.AccountID = tblAccounts.AccountID) ON tblTmpImport.AccountNumber = tblAccounts.AccountNumber;

Any help would be GREATLY appreciated.

Thanks,
Eric

 

[SQL_Hell]

Hi,

I may be barking up the wrong tree but try changing your code to this


DELETE tblAccountsInvestmentFirms.*
FROM tblTmpImport
INNER JOIN tblAccounts
ON tblTmpImport.AccountNumber =tblAccounts.AccountNumber
INNER JOIN tblAccountsInvestmentFirms
ON tblAccountsInvestmentFirms.AccountID = tblAccounts.AccountID

 

[ejstefl]

Hi,

I may be barking up the wrong tree but try changing your code to this


DELETE tblAccountsInvestmentFirms.*
FROM tblTmpImport
INNER JOIN tblAccounts
ON tblTmpImport.AccountNumber =tblAccounts.AccountNumber
INNER JOIN tblAccountsInvestmentFirms
ON tblAccountsInvestmentFirms.AccountID = tblAccounts.AccountID

When I try that code, I get the following syntax error:

Syntax error (missing operator) in query expression 'tblTmpImport.AccountNumber =tblAccounts.AccountNumber
INNER JOIN tblAccountsInvestmentFirms ON tblAccountsInvestmentFirms.AccountID = tblAccounts.AccountID'.

Sorry, but I'm not very experienced with writing SQL. Any other ideas? (Love the avatar, by the way!)

 

[SQL_Hell]

its here

DELETE tblAccountsInvestmentFirms.*


change to


DELETE tblAccountsInvestmentFirms