Delete records from one table that appear in another table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to remove records in one table that match those in another table.
I have the following code but this seems to make Access 'Hang'. Is this the
best/correct way of performing this type of operation? Any suggestions
gratefully received!

Also, .RecordCount returns a value of 363,975 but the table actually
contains 363,958 records. Any ideas as to why it should do this?

I'm using Access 97.


Private Sub cmdRemove_Click()

Dim dbs As Database, rstElectoral As Recordset, rstLastElectoral As
Recordset, first2005 As Field
Dim surname2005 As Field, electoral2004 As Field, first2004 As
Field, surname2004 As Field
Dim postcode2005 As Field, postcode2004 As Field, position As Long,
length As Long

Set dbs = CurrentDb
Set rstElectoral = dbs.OpenRecordset("Electoral_register")
Set rstLastElectoral = dbs.OpenRecordset("AdultSampleList2004")

Set first2005 = rstElectoral.Fields("First_Name")
Set surname2005 = rstElectoral.Fields("Surname")
Set postcode2005 = rstElectoral.Fields("Postcode")
Set first2004 = rstLastElectoral.Fields("FIRSTNAME")
Set surname2004 = rstLastElectoral.Fields("SURNAME")
Set postcode2004 = rstElectoral.Fields("POSTCODE")

'Access all records to get the RecordCount
rstElectoral.MoveLast
length = rstElectoral.RecordCount
rstElectoral.MoveFirst 'Move back to first records before looping

'Loop through first table and check each records against all records
in another table. If they match, delete the record in rstElectoral
For position = 1 To length Step 1
With rstLastElectoral
Do While Not rstLastElectoral.EOF
If surname2005 = surname2004 Then
If first2005 = first2004 Then
If postcode2005 = postcode2004 Then
rstElectoral.Delete
End If
End If
Else
rstLastElectoral.MoveNext
End If
Loop
rstLastElectoral.MoveFirst
rstElectoral.MoveNext
End With
Next position

rstElectoral.Close
rstLastElectoral.Close

End Sub
 
I have tried various SQL queries but I'm having no luck with them either.
I'm pretty new to SQL but it's not too complicated so I'm not sure what I'm
doing wrong! Again, any suggestions would be great.

Thanks
 
Rich

I wasn't referring to using SQL directly, but to using the query designer in
Access. First backup your database/file. Then build a Select query that
gets the records you'll want to delete, then convert it to a Delete query
(both doable from within the query design window).
 
I have tried to use the design view to turn a select query into a delete
query. The select query returns around 22500 records but the delete query
brings up a 'Use an updateable query error' which I can't seem to correct.
I'm pretty new to SQL which is why I've been using VB more than SQL.

I'll keep playing with the code and see what happens.

Thanks for your help.

Rich
 
I've also run into that with some queries. An "ugly but it works" solution
is to use the select query to create a new table (make table query) that
holds (only) the record IDs (primary keys) of the records you wish to
delete.

Then create a new query that links the "delete these" table records to your
main table, select the main table rows, and do your deleting. BACKUP FIRST!

Jeff Boyce
<Access MVP>
 
I've tried some SQL code suggested in another string and thats worked.

Thanks for all your help and suggestions.
 
Back
Top