Delete Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a delete query, and when I run it, I get this error message "Could not
delete from specified tables".

When I click on Help, it tells me that my database is a read-only database.
I know that I don't have it opened as read-only.

Please help. Thanks.
 
Could you post the SQL of the delete query? The help is generic and may not
reflect the actual cause of the error.
 
Here is the SQL:

DELETE [2tblExpenses_from_Excel].*, [2tblExpenses_from_Excel].[Post?],
[2tblExpenses_from_Excel].ClientID, [2tblExpenses_from_Excel].[Emp No],
[2tblExpenses_from_Excel].ServNumber, [2tblExpenses_from_Excel].[Duplicate
Key]
FROM (((2tblExpenses_from_Excel LEFT JOIN CLIENTS ON
[2tblExpenses_from_Excel].ClientID = CLIENTS.CLNTOID) LEFT JOIN EMPLOYEE ON
[2tblExpenses_from_Excel].[Emp No] = EMPLOYEE.EMPNUMBE) LEFT JOIN SERVCODE ON
[2tblExpenses_from_Excel].ServNumber = SERVCODE.SERVNUMB) RIGHT JOIN
[3tblPosted_Timesheets_&_Expenses] ON [2tblExpenses_from_Excel].[Duplicate
Key] = [3tblPosted_Timesheets_&_Expenses].[Duplicate Key]
WHERE ((([2tblExpenses_from_Excel].[Post?])=Yes) AND
(([2tblExpenses_from_Excel].ClientID)=[CLIENTS]![CLNTOID]) AND
(([2tblExpenses_from_Excel].[Emp No])=[EMPLOYEE]![EMPNUMBE]) AND
(([2tblExpenses_from_Excel].ServNumber)=[SERVCODE]![SERVNUMB]) AND
(([2tblExpenses_from_Excel].[Duplicate
Key])=[3tblPosted_Timesheets_&_Expenses]![Duplicate Key]));
 
What I like to do in my delete query is to delete only the records where
"Duplicate Key" in the to-be-deleted table is not equal to the "Duplicate
Key" in another table.

So what I did was I did outer join between two tables, and <>[Another
table]![Duplicate Key] in the criteria.

Thanks.

AccessHelp said:
Here is the SQL:

DELETE [2tblExpenses_from_Excel].*, [2tblExpenses_from_Excel].[Post?],
[2tblExpenses_from_Excel].ClientID, [2tblExpenses_from_Excel].[Emp No],
[2tblExpenses_from_Excel].ServNumber, [2tblExpenses_from_Excel].[Duplicate
Key]
FROM (((2tblExpenses_from_Excel LEFT JOIN CLIENTS ON
[2tblExpenses_from_Excel].ClientID = CLIENTS.CLNTOID) LEFT JOIN EMPLOYEE ON
[2tblExpenses_from_Excel].[Emp No] = EMPLOYEE.EMPNUMBE) LEFT JOIN SERVCODE ON
[2tblExpenses_from_Excel].ServNumber = SERVCODE.SERVNUMB) RIGHT JOIN
[3tblPosted_Timesheets_&_Expenses] ON [2tblExpenses_from_Excel].[Duplicate
Key] = [3tblPosted_Timesheets_&_Expenses].[Duplicate Key]
WHERE ((([2tblExpenses_from_Excel].[Post?])=Yes) AND
(([2tblExpenses_from_Excel].ClientID)=[CLIENTS]![CLNTOID]) AND
(([2tblExpenses_from_Excel].[Emp No])=[EMPLOYEE]![EMPNUMBE]) AND
(([2tblExpenses_from_Excel].ServNumber)=[SERVCODE]![SERVNUMB]) AND
(([2tblExpenses_from_Excel].[Duplicate
Key])=[3tblPosted_Timesheets_&_Expenses]![Duplicate Key]));


John Spencer said:
Could you post the SQL of the delete query? The help is generic and may not
reflect the actual cause of the error.
Click to expand...
Click to expand...
 
I'm not sure here. I don't work with Excel tables that much (other than to
extract data from them). I don't normally update them. Is
2TblExpenses_From_Excel a linked Excel sheet or is it a table in Access?

If it is a linked EXCEL table, you may have to import the table into Access.
The ISAM may not handle deleting from EXCEL.

You might try this simple modification - which strips out the redundant
conditionals in the where clause- they should be handled in the joins. TEST
ON A COPY of your data.

DELETE DISTINCT ROW [EE].ClientID
FROM ((([2tblExpenses_from_Excel] as EE
INNER JOIN CLIENTS ON [EE].ClientID = CLIENTS.CLNTOID)
INNER JOIN EMPLOYEE ON [EE].[Emp No] = EMPLOYEE.EMPNUMBE)
INNER JOIN SERVCODE ON [EE].ServNumber = SERVCODE.SERVNUMB)
INNER JOIN [3tblPosted_Timesheets_&_Expenses] ON
[EE].[Duplicate Key] = [3tblPosted_Timesheets_&_Expenses].[Duplicate Key]
WHERE [EE].Post?]=Yes


AccessHelp said:
Here is the SQL:

DELETE [2tblExpenses_from_Excel].*, [2tblExpenses_from_Excel].[Post?],
[2tblExpenses_from_Excel].ClientID, [2tblExpenses_from_Excel].[Emp No],
[2tblExpenses_from_Excel].ServNumber, [2tblExpenses_from_Excel].[Duplicate
Key]
FROM (((2tblExpenses_from_Excel LEFT JOIN CLIENTS ON
[2tblExpenses_from_Excel].ClientID = CLIENTS.CLNTOID) LEFT JOIN EMPLOYEE
ON
[2tblExpenses_from_Excel].[Emp No] = EMPLOYEE.EMPNUMBE) LEFT JOIN SERVCODE
ON
[2tblExpenses_from_Excel].ServNumber = SERVCODE.SERVNUMB) RIGHT JOIN
[3tblPosted_Timesheets_&_Expenses] ON [2tblExpenses_from_Excel].[Duplicate
Key] = [3tblPosted_Timesheets_&_Expenses].[Duplicate Key]
WHERE ((([2tblExpenses_from_Excel].[Post?])=Yes) AND
(([2tblExpenses_from_Excel].ClientID)=[CLIENTS]![CLNTOID]) AND
(([2tblExpenses_from_Excel].[Emp No])=[EMPLOYEE]![EMPNUMBE]) AND
(([2tblExpenses_from_Excel].ServNumber)=[SERVCODE]![SERVNUMB]) AND
(([2tblExpenses_from_Excel].[Duplicate
Key])=[3tblPosted_Timesheets_&_Expenses]![Duplicate Key]));


John Spencer said:
Could you post the SQL of the delete query? The help is generic and may
not
reflect the actual cause of the error.
Click to expand...
Click to expand...
 
Back
Top