Forum Discussion
Using a Delete Query
I need to create a Delete Query to clear out the data from tables in my database. The description in my Professional Features book is sparse: "DELETE [table,*] FROM tableexpression WHERE Criteria"...
DELETE *
FROM YourTableNameGoesHere
That will delete every record from the table. Substitute the actual name of your table for "YourTableNameGoesHere"
Please be very sure that's what you want to do because you don't have a do-over.
If you wanted to delete some, but not all, records, you'd add a where clause:
DELETE *
FROM YourTableNameGoesHere
WHERE YourFieldNameGoesHere = SomeValueinEveryRecordToBeDeleted
Again, replace the place-holders with real names of the table and field and the value that determines is records are to be included.
George, I didn't mention I was using SQL for an OpenRecordSet command. I get an error every time run the command. Here's the code:
DbVW is the the DataBase, previouisly opened with no error.
pzSQL = DELETE FROM ["+TblNm+"];"
Set DbRS = DbVW.OpenOpenRecordset(pzSQL, dbOpenDynaset)
I get a ERROR 3219, Invalid Operation.
Any suggestions?
DbVW is the the DataBase, previouisly opened with no error.
pzSQL = DELETE FROM ["+TblNm+"];"
Set DbRS = DbVW.OpenOpenRecordset(pzSQL, dbOpenDynaset)
I get a ERROR 3219, Invalid Operation.
Any suggestions?
- Yes, OpenRecordset does just that. It only OPENS the recordset. After that you can add, edit or delete individual records from within that recordset. You have a couple of options. You can either set the parameter on a SELECT statement to open the recordset so that only the records to be deleted are available, and then individually delete each record in that subset, or use Currentdb.Execute to run the delete query directly. I'd rather go the query route rather than the delete one-by-one from a recordset route, but it may not make all that much difference depending on the number of records to be deleted.
- George, Thanks muchly again. Another example of "not the whole story!"... I did find an example of the Currentdb.Execute SQLStatement on the web. It was not covered in my 2007/Version 12 Access manual! Th existence of the EXECUTE instruction was a revelation! BTW, do you know the code to use for creating a Version 12 DB in the CreateDatabase Command? I've been using dbVersion30 forever and then upgrading the result when my DbCreator program finishes (I create a schema-like file defining the Tables and Contents and generate a matching Db but haven't updated the code in many years). The code might be ACE 12? These questions are a result of building a "maintenance tool".