Aravind Ranganathan
asked on
Access Delete Query Not Working
I am working on a query to delete from an existing linked table where the Batch Id is all the same. So my query is as following :
i am using an explicit parameter called BatchID and when i pass 1 into it i want it to delete all the records so that i can load the next batch next time, but when i run the query i am getting could not delete from specified tables?? i am not sure why??
i am using an explicit parameter called BatchID and when i pass 1 into it i want it to delete all the records so that i can load the next batch next time, but when i run the query i am getting could not delete from specified tables?? i am not sure why??
Your SQL should read like the following:
DELETE BatchID from yourtablename WHERE BatchID = 1
DELETE BatchID from yourtablename WHERE BatchID = 1
Assuming you are manually entering the Batch ID
SELECT dbo_WorkTable1.*
FROM dbo_WorkTable1
WHERE dbo_WorkTable1.BatchID=[En
SELECT dbo_WorkTable1.*
FROM dbo_WorkTable1
WHERE dbo_WorkTable1.BatchID=[En
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Tusitala : i dont have any relationships on this table just want to delete it as the user passes the value into it.
My Query
PARAMETERS BatchID Int;
DELETE dbo_WorkTable1.*, dbo_WorkTable1.[Batch ID]
FROM dbo_WorkTable1
WHERE (((dbo_WorkTable1.[Batch ID])=[BatchID]));
My Query
PARAMETERS BatchID Int;
DELETE dbo_WorkTable1.*, dbo_WorkTable1.[Batch ID]
FROM dbo_WorkTable1
WHERE (((dbo_WorkTable1.[Batch ID])=[BatchID]));
ASKER
@Jim Dettman (Microsoft MVP/ EE MVE)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Again ...
Assuming you are manually entering the Batch ID
SELECT dbo_WorkTable1.*
FROM dbo_WorkTable1
WHERE dbo_WorkTable1.BatchID=[En
Assuming you are manually entering the Batch ID
SELECT dbo_WorkTable1.*
FROM dbo_WorkTable1
WHERE dbo_WorkTable1.BatchID=[En
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes the same table is on sql server, the table does not have a primary key because it is not unique, each night i load a file into sql server and i assign a batch id for those files, so next day user opens the work table fixes the issue on the datasheet and runs an append query to move the data from work table to archive table. now i want the user to delete that data using the batch id. this is where i am stuck. can you please provide me with any work around.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Guys your answers really showed me how to trouble shoot an query.
Check for any relationship constraints. Also remove any other fields from your query as there may be dependencies on other tables.