Delete record using VBA

[Coleman984]

It seems to be much easier to add something to an Access database than remove it. How can I remove an entry from an access database? I will know the primary key value for the record I want deleted.

 

[gemma-the-husky]

one way, among others in code is

currentdb.execute "delete * from sometable where somefield = somevalue"

if you can construct a delete query that takes a criteria/parameter (eg - a form reference)

then you can probably just

currentdb.execute "mydeletequery"

or

docmd.openquery "mydeletequery"

note that these two variations of running queries work similarly, but with slighhtly nuanced differences.

or you can select an item on a form, and just press the delete button ....


But why do you say it is easier to add, than remove data? The two actions are basically the same.

 

[GohDiamond]

To remove a record from a datasheet, click on the LEFTMOST border of the datasheet(Record Selector), to select it and press the DELETE Key on the keyboard. Forms also have Record Selectors if that property hasn't been set to 'NO'.

If a record doesn't delete because of related Tables (linked tables) then the problem could be that the 'cascade delete' property hasn't been set in the relationships window or the record is not the child record. Child records should be easily deleteable(is that a word) while a 'Parent' record is not as easy to delete if it has related child records without the 'cascade delete' setting, because that would cause ophan child records which Access doesn't like to have happen.

Cheers!
Goh

 

[Coleman984]

To remove a record from a datasheet, click on the LEFTMOST border of the datasheet(Record Selector), to select it and press the DELETE Key on the keyboard. Forms also have Record Selectors if that property hasn't been set to 'NO'.

If a record doesn't delete because of related Tables (linked tables) then the problem could be that the 'cascade delete' property hasn't been set in the relationships window or the record is not the child record. Child records should be easily deleteable(is that a word) while a 'Parent' record is not as easy to delete if it has related child records without the 'cascade delete' setting, because that would cause ophan child records which Access doesn't like to have happen.

Cheers!
Goh

Thanks for the info regarding parent child relationships. This table will have queries linked to it. Given that how would I delete the entry and ensure everything that was affected by this delete is corrected? Removing an entry will be needed as employees are fired.

 

[Coleman984]

It should be noted that this delete will occur from excel. This i did forget to mention.

I found this which is supposed to find the entry but when I use the below delete command it doesn't do anything (this is from excel remember). Would I be better trying to get this code from an excel forum? Not sure IMO it is a middle ground.

       With rs
            .Delete adAffectCurrent
       End With

Sub FindEmployee(strDBPath As String, _
               strTable As String, _
               strCriteria As String, _
               strDisplayField As String)
   
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   
   ' Open the connection.
   Set cnn = New ADODB.Connection
   With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open strDBPath
   End With
   
   Set rst = New ADODB.Recordset
   With rst
      ' Open the table by using a scrolling Recordset object.
      .Open Source:=strTable, _
            ActiveConnection:=cnn, _
            CursorType:=adOpenKeyset, _
            LockType:=adLockOptimistic

      ' Find the first record that meets the criteria.
      .Find Criteria:=strCriteria, SearchDirection:=adSearchForward

      ' Make sure record was found (not at end of file).
      If Not .EOF Then
         ' Print the first record and all remaining
         ' records that meet the criteria.
         Do While Not .EOF
            Debug.Print .Fields(strDisplayField).Value
            ' Skip the current record and find next match.
            .Find Criteria:=strCriteria, SkipRecords:=1
         Loop
      Else
         MsgBox "Record not found"
      End If
      ' Close the Recordset object.
      .Close
   End With

   ' Close connection and destroy object variables.
   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing
End Sub

 

[Coleman984]

I found this on the interwebs and while it works I wanted something more efficient.

       With rs
   Do
        If rs.Fields("EmployeeID") = EmpID Then
            rs.Delete
            rs.Update
        End If
        rs.MoveNext
    Loop Until rs.EOF
    End With

This was on the same site but the instruction is vague, I don't understand how to build or use this statement in my database. Can someone assist?

Where it says Table. that is supposed to my table name correct?
From Table again is my table correct?
Where ((Table.myfield)) is my table again

Ok I think that part I understand but what would be the rest of the structure for that sql statement like the setting of objects and the what not to get it to work.

strSQL = "DELETE Table.* FROM Table WHERE ((Table.MyField) = ""whatever"");"

db.Execute strSQL

 

[GohDiamond]

Well the sql seems to say: Delete all fields from a table named TABLE where the table's field named MyField equals the word "whatever", but I''m not sure you = need double double quotes there i.e. "" word ""

...as for running it from Excel you have to address the active database and that's beyond my ken ATM, so, I'll defer to a more knowledgeable soul at this point.

Cheers!
Goh

 

[spikepl]

Consider whether you really want to delete. On majority of occasions, in a DB you would rather mark a record as inactive or archived, rather than deleting it. Then the entire history still remains accessible, if needed.

 

[AccessBlaster]

I would use a macro to delete the current record...Wiz Bang! your done..