"Specify the table containing the records you want to delete."

[MRG55]

Hi,

I have created a new Training DB in Access 2007 (MS Office 2010). As a housekeeping tool I have created append queries that copy the Training, Qualification and Employee records to appropriate archive tables and this works fine.

Problem is in creating the Delete Queries for each to remove them from the main DB. Each delete query uses 2 tables - Employee which identifies the who the records belong to via Resigned "Yes/No" - I use Yes as the criteria. The other table is the table from which the records are deleted.

It is probably something really silly but all I get is the above error and I have spent too many hours playing without result. I did complete a short course some years ago but am not conversant with Visual Basic or Syntax code.

I hope I have attached a copy of one of the queries.

Many thanks

 

[Galaxiom]

Unless you have a vast number of records (like a million) you would be better off leaving the records in the table and adding a boolean field to flag the record as deleted.

Then change the queries to optionally show the records that are not "deleted".

Much simpler.

 

[MRG55]

Thanks for the quick reply. Is that really easier than a creating a Delete Query? - everything I have read on creating Delete queries indicate it is not hard - it just doesn't work. I guess the total amount of records would only be around 3000 so maybe that would be the way to go but I wouldn't know where to start. How would I go about creating something like that?

I originally set up the DB in Access 2003 and everything worked OK but using the same query format in 2007 hasn't worked for the append or delete queries - the append queries I managed to work out.

 

[Galaxiom]

Not particularly easier than a delete query when you have already set up the database.

However it is a lot safer and easier to manage. With the append and delete you have two tables to manage and you would want to be sure the append worked before you deleted. With that kind of system you would not want to let ordinary users do the delete. It is more something you do as a special admin archiving task after a backup.

The easiest way to impliment it after the fact is use a master query.

TURN OFF NAME AUTO CORRECT (Essential or the next step will wreck everything.
Rename the table.
Add a Boolean (Y/N) field to the table say called "IsDeleted".
Create a query based on the table with the criteria on IsDeleted = False.
Save this query to the original table name.

Everything will now work as before except your queries, forms and reports will be looking at the master query instead of the table. Any record with IsDeleted ticked will be left out.

Records can be "deleted" using whatever criteria you like in a query. They can be restored by just unticking the deleted box.

You can even add a bound checkbox to the forms that allows a user to "delete" the record. They won' be able to get it back once they requery or close the form but the admin will by unticking the box in the table. Or you could provide a form just for that purpose.

 

[MRG55]

Thanks for that - I'll have a look over the next couple of days and see what happens.