Run Delete Query on Linked Sharepoint List

[Fish521]

I haven't been able to find anything regarding this out there so hoping someone may be able to help out.
I have a list out on SharePoint that I link back into Access to query and run reports based off off. I have a query that runs to archive these records each month to a table. What I need to do after this archive is done is delete those records from the SharePoint list. I set up a delete query to run based upon the ID within the record but when I go to run the query I am getting an error stating "Could not delete from specified table". Not sure if I just have something wrong within my query or if it is due to being a linked SharePoint list.

DELETE [Tasks].*, [Tasks].ID
FROM [Tasks], [archive 201502]
WHERE ((([Tasks].ID)=[archive 201502]![ID]));

Any help would be great.

Thanks,

 

[Andrzejek]

WHat is the table's name you are deleting the records from?
[tt][Tasks][/tt] or [tt][archive 201502][/tt] or both?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Fish521]

Deleting from Tasks

 

[dhookom]

A quick test would be to see if you can edit anything in the linked list.

Also, there is no join in your delete query.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

If you just deleting from [tt]Tasks[/tt] table, your statement should look like:
[tt]
DELETE FROM Tasks
WHERE ...[/tt]

So I would try is to delete a specific (hard-coded) ID and see if it would work:
[tt]
DELETE FROM Tasks
WHERE ID = 1234 [/tt]



Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Fish521]

That runs fine the problem I'm having is that I need to delete the items from the archived list. So when I try to reference that table is where I am getting the error stating it cannot delete.

 

[dhookom]

Try this:

DELETE [Tasks].*
 FROM [Tasks]
 WHERE [Tasks].ID IN (SELECT ID FROM [archive 201502]);

Duane
Hook'D on Access
MS Access MVP

 

[Fish521]

Worked perfectly.
Thank you,

 

[Andrzejek]

Don't forget to click on "[blue]Great post? Star it![/blue]" link to show appreciation for Duane's help.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.