Delete Query error: "Could not delete from specified tables" (1 Viewer)

I am attempting to archive and delete records from a timelog table using selected start [Startdate] and end date [EndDate] paramaters which are fields contained in a separate [StartEndDate] table.

I have created both an archive query and a delete query.
The Archive query works fine but when I execute the delete query I get a message: "Could not delete from specified tables"

The archive and delete vba code is as follows:

' Archive Data
'stDocName = "Archivehours"
'DoCmd.OpenQuery stDocName, acNormal, acEdit


'*******************
The SQL is as follows:
INSERT INTO ArchvedTimeLog
SELECT tblTimeLog.*
FROM tblTimeLog, StartEndDates
WHERE (((tblTimeLog.LogDate)>=[StartEndDates].[StartDate]
And (tblTimeLog.LogDate)<=[StartEndDates].[EndDate]));

'*******************
The archive action works fine.

'*******************
' Purge Active File
stDocName = "DeleteArchived_Hours"
DoCmd.OpenQuery stDocName, acNormal, acEdit


'*******************
The SQL is as follows:

DELETE DISTINCTROW tblTimeLog.*, tblTimeLog.LogDate
FROM tblTimeLog, StartEndDates
WHERE (((tblTimeLog.LogDate)>=[StartDate] And (tblTimeLog.LogDate)<=[EndDate]));

'*******************
The Table VIEW of this query looks perfect. However, upon execution, I get the message: "Could not delete from specified tables"

Any help would be greatly appreciated.
MBA

what if you manually delete a record from your concerned table?

I can manually delete a record with no problems. Note: Using Access 2010.
Thank you... MBA

DISTINCTROW is used to supress duplicates. If you need to use this then your query clearly does not offer a suitable candidate key.

Deletes become problematic once joins get involved. I usually do this kind of delete in two steps. Update one of the fields with an out of scope value then delete based on that flag.

We often get questions about problems with archiving from new developers who think they have a large table when it gets to a few tens of thousands of records. When a table passes a million records then it is starting to get substantial.

Moreover the dangerous process of moving records to another table is often under estimated and they assume the copy worked and nonchalently progress to the delete.

In most cases a simple "deleted" flag applied to the records is a simpler and safer option. With an index on the deleted field the performance is very similar.

BTW The Datasheet View of any query is a SELECT so does not necessarily work as an action query.

Galaxiom said:

DISTINCTROW is used to supress duplicates. If you need to use this then your query clearly does not offer a suitable candidate key.

Deletes become problematic once joins get involved. I usually do this kind of delete in two steps. Update one of the fields with an out of scope value then delete based on that flag.

We often get questions about problems with archiving from new developers who think they have a large table when it gets to a few tens of thousands of records. When a table passes a million records then it is starting to get substantial.

Moreover the dangerous process of moving records to another table is often under estimated and they assume the copy worked and nonchalently progress to the delete.

In most cases a simple "deleted" flag applied to the records is a simpler and safer option. With an index on the deleted field the performance is very similar.

BTW The Datasheet View of any query is a SELECT so does not necessarily work as an action query.

Click to expand...

Thanks so much for solving a huge problem for me. I'm not archiving, but trying to eliminate some unwanted records in a dataset, and your two-step technique worked perfectly!