MeadInKent
| Feedback | A query to Append new records | Using Queries |

Delete or Update data using an Access Query

Delete queries are useful, not only because they can be linked to a button on a form, thereby automating the deletion process, but also because they are much quicker than manually deleting the records in an open table.

Access 2010 Step by Step

 
UK more ...   USA more ...

There is nothing special about creating a delete query. A normal query can be created to select all of the records which are required for deletion. When you have carefully checked that the selection criteria are correct, use the Query Tools Design ribbon option Query Type | Delete to change the type.

The following example will delete all of the bookings records which relate to before 1 April 2005 and which have been fully paid. If your delete query is intended to delete all records and therefore no criteria are required, you must nevertheless add a field to the definition.

Delete Bookings : Delete Query (design) ox
   source table
Field:  Booking_Ref Date_InvPaid Date_Return  
Table: Bookings Bookings Bookings  
Delete: where where where  
Criteria:   Is Not Null <#01/04/2005#  
Or:        
 

If you just wish to preview the effect of running the query, click on the DataSheet View button. To make it perform its operation, either click on the Run button (in design mode) or Open the query.

When using Access to design a form, the button wizard omits the delete queries from the list of available items - i.e. only select, append and update queries are shown. Once the wizard has been completed (having temporarily used another query name) and a button has been created it is possible to edit the VB code linked to the OnClick event change the query name to the required delete query. The button will then function perform the required task.

If the dialog boxes which warn that you are about to delete records are considered to be unecessary in your database application, they can be switched off by editing the button on-click code and using the SetWarnings command.


Private Sub Btn_Del_Bkgs_Click()
On Error GoTo Err_Btn_Del_Bkgs_Click

Dim stDocName As String
DoCmd.SetWarnings False ' Turn warning dialog boxes off
DoCmd.Hourglass True ' Change cursor to hourglass symbol

stDocName = "Delete Bookings"
DoCmd.OpenQuery stDocName, acNormal, acEdit

DoCmd.Hourglass False
DoCmd.SetWarnings True

Exit_Btn_Del_Bkgs_Click:
Exit Sub

Err_Btn_Del_Bkgs_Click:
MsgBox Err.Description
Resume Exit_Btn_Del_Bkgs_Click

End Sub

Update Queries

An update query will amend data in existing records. It does not append new records to a table. For example a series of telephone numbers in a Customers table, may need to be updated with new area codes. The existing field values will be updated and overwritten. Having designed and tested a query, while still in design mode select the Query Type | Update ribbon option.

Update MilesToKm: Update Query (design) ox
   source data
Field: Booking_Ref Km_Start Km_Return  
Table: Bookings Bookings Bookings  
Update To: [Km_Start]/0.621 [Km_Return]/0.621  
Criteria:    
 

Another common use of an update query is to reset all of the values of a field back to a common value or to blank. To delete all of the values in a field, simply set its 'Update to:' criteria as "".

If you preview an Update Query you will only see the original values which are to be changed. The updated values are not shown until after the query has been run or opened.


file: acc_deleteupdate.htm Page last modified Mar14 MeadInKent.co.uk 2014