How to delete duplicates from another table.
Q: John Haynes writes: I am trying to set up a standard query to delete duplicates. The tables consist of customers from 2 different companies company I and company N. I need to delete the customers in company I when there is a record in company N. I went to Microsoft’s website and did a search for what I needed to do and found an article (note from Helen: the link is no longer functional, so I am not listing it). This is instructions for deleting duplicate records with Access 2003. I followed the instructions and created the select query using group for the customer and max for the company then created the delete query. I can view the items to be deleted but when I try executing the query it tells me that “Could not delete from specified tables”. I checked with the internal help on the problem but did not find one that applied. The database is standalone and not marked as read-only and I have administrative rights to the file I do not have any other links to this table.
A: Usually when you can’t run an action query, it is because you are working with a non-updatable query. It might be easier to do this job in code, as in the code sample below, which searches for a matching record in tblOtherCompanies for each record in tblCompanies, and deletes any record in tblCompanies where a match is found:
Public Sub DeleteDupes()
Dim dbs As DAO.Database
Dim rstTable1 As DAO.Recordset
Dim rstTable2 As DAO.Recordset
Dim strSearch As String
Dim strCompany As String
Set dbs = CurrentDb
Set rstTable1 = dbs.OpenRecordset(“tblCompanies”, dbOpenDynaset)
Set rstTable2 = dbs.OpenRecordset(“tblOtherCompanies”, dbOpenDynaset)
Do While Not rstTable1.EOF
strCompany = Nz(rstTable1![CompanyName])
If strCompany <> “” Then
strSearch = “[CompanyName] = ” & Chr$(34) & strCompany & Chr$(34)
Debug.Print “Search string: ” & strSearch
rstTable2.FindFirst strSearch
If rstTable2.NoMatch = False Then
rstTable1.Delete
End If
rstTable1.MoveNext
End If
Loop
End Sub
