Back To Top


MS Access VBA Delete Record Query For versions 2000, 2002, 2003, 2007

Go here To Delete Records in the Query Builder.
Follow the Delete a Table with VBA Link to delete a Table in VBA.
Go here To Delete a Field in VBA.

 

The VBA Delete Records Sample is available for download here in a zipped MS Access Database. The below picture (click to enlarge) shows the Single form and module.

MS Access VBA Delete Button 

The Actual VBA Code to Delete Records using criteria, you should be able to copy/paste right into a module:




Function
DeleteRecord( _
     
ByVal TableName As String , _
     
ByVal WhereField As String , _
     
ByVal WhereStrValue As String ) _
     
As Boolean

  
'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
   'Set references by Clicking Tools and Then References in the Code View window
   ' Deletes a record or records with a given WhereStrValue that exists in a field
   ' in table TableName.
   ' Accepts
   ' TableName: Name of table in which the field is located
   ' WhereField: Name of the field to look in
   ' WhereStrValue: Given Criteria
   ' Returns True on success, false otherwise
   'USAGE: DeleteField "TABLENAME", "WHEREFIELD", "WHERESTRVALUE"

  
On Error GoTo errhandler

  
Dim Db As DAO.Database
  
Dim strSql As String

  
'Create the Access VBA Delete Query from our Three Strings
  
strSql = "DELETE [" & TableName & "]." & WhereField & _
        
" FROM " & TableName & " WHERE ((([" & _
         TableName
& "]." & WhereField & ")= '" & WhereStrValue & "'));"

  
Set Db = CurrentDb()

  
'Print the SQL so we can paste into the Access query builder if there are errors
  
Debug.Print strSql

  
'Run the Access VBA Query SQL Code
  
Db.Execute strSql

  
'If no errors return true
  
DeleteRecord = True

ExitHere:

  
Set Db = Nothing

  
'Notify the user the process is complete. 
  
MsgBox "Delete Record Complete"

  
Exit Function

errhandler:
  
'There is an error return false
  
DeleteRecord = False

   With
Err
      MsgBox
"Error " & .Number & vbCrLf & .Description, _
            vbOKOnly
Or vbCritical, "DeleteRecord"
  
End With

   Resume
ExitHere

End Function



For the above VBA Delete Function in an usable Form with Delete Button, download the Sample database in ZIP format. The sample includes 100 records in which to test on.

You may post this tutorial on your website or in a forum. If you do please maintain a Link to Eraseve AP.



For further MS Access tutorials go here: MS Access tutorials

Go here To Check if Table exists in VBA.
Go here To Check if Field exists in VBA.

 

Did you find what you were looking for?
What would you suggest?
mail@eraserve.com
Your Name:
Your Message:
Your Email:

    

 

Custom Search