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"! I want to renumber the RecordIDs (1st field, Long) in my DB, which got fouled. The procedure I'd like to follow is to COPY the DB, then use the Delete Query to empty the tables, leaving the structure, then copy the original table data into the Copied DB renumbered. I do not understand what [table,*} or tableexpression is supposed to mean in this context. I can see starting with "DELETE tablename FROM" but can't figure out what goes next! My everyday SQL statement looks like ""SELECT * FROM [" + zpTableToAddTo + "]"." Can I get away with "DELETE tablename" ?
7 Replies
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?- 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, that is an excellent response. I don't need to be concerned about messing up the database because it's a copy of the original. It cleared up the [table,*] quandary from the Visual Basic Manual and problem of what tableexpression means vs-a-vs table.
Not that Microsoft's documentation is a bit on the foggy side.
