Deleting records in 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
 
rjphillips said:
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
Click to expand...


I can't say why the RecordCount seems off, but your code
will run forever because you forgot the MoveNext in some of
the If statements.

However, even if you get this code to work, it will still
take so long to run that you will probably think it has
hung. You should do this kind of thing in a query so the
database engine can optimize the record retrieval and field
matching. Be sure to experiment on a copy of the real data.

Try something more like this air code:

strSQL = "DELETE E.* " _
& "FROM Electoral_register AS E " _
& "INNER JOIN AdultSampleList2004 AS L " _
& "ON E.First_Name = L.First_Name " _
& "AND E.Surname = L.Surname " _
& "AND E.Postcode = L.Postcode"
dbs.Execute strSQL, dbFailOnError
MsgBox dbs.RecordsAffected & " Records were deleted"
Set dbs = Nothing

If you have indexes on each of the fields used in the
comparison, the query will probably run a lot faster than if
there are no indexes.

Do those three fields make up the unique identifier for the
records in the table? Seems like you're relying on there
never being more then one person with the same first and
last name in a zip code area. This is a very shaky
assumption.
 
Thanks for the advice. I had been experimenting with SQL but I'm fairly new
to it and was getting a 'Must use an updatable query' error that I couldn't
seem to solve.

The assumption that there is never more than 1 person with the same name in
each postcode is fairly shaky, but as each postcode relates to a maximum of
80 households and there are no other unique identifiers for the records, it's
the best I can do. It should serve the purpose with the least amount of
error.

By the way, what does 'air code' mean?

Thanks
 
I've tried your code and indexing the fields to be searched and thats worked
a treat!

Thanks for your help Marshall. In other forums you can award points to
people who've provided help. Not in this one though, so you're just going to
have to be content with the knowledge that you've reduced my stress levels
and stopped me shouting at my computer!
 
Glad to hear you got it working.

Good luck with the potential name/zip conflicts. I'm afraid
luck is about all you've got going for you on this issue.
My wife runs into this kind of problem at some local shops
where there is another Margaret Barton with the same zip and
the two phone numbers are xxx-yyy-9665 and xxx-yyy-9656.

Air code is untested code that is just typed into a post,
not Copy/Pasted from a successful test program.
--
Marsh
MVP [MS Access]


Thanks for the advice. I had been experimenting with SQL but I'm fairly new
to it and was getting a 'Must use an updatable query' error that I couldn't
seem to solve.

The assumption that there is never more than 1 person with the same name in
each postcode is fairly shaky, but as each postcode relates to a maximum of
80 households and there are no other unique identifiers for the records, it's
the best I can do. It should serve the purpose with the least amount of
error.

By the way, what does 'air code' mean?

rjphillips said:
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?
Click to expand...
[snip]
Click to expand...
Marshall Barton said:
I can't say why the RecordCount seems off, but your code
will run forever because you forgot the MoveNext in some of
the If statements.

However, even if you get this code to work, it will still
take so long to run that you will probably think it has
hung. You should do this kind of thing in a query so the
database engine can optimize the record retrieval and field
matching. Be sure to experiment on a copy of the real data.

Try something more like this air code:

strSQL = "DELETE E.* " _
& "FROM Electoral_register AS E " _
& "INNER JOIN AdultSampleList2004 AS L " _
& "ON E.First_Name = L.First_Name " _
& "AND E.Surname = L.Surname " _
& "AND E.Postcode = L.Postcode"
dbs.Execute strSQL, dbFailOnError
MsgBox dbs.RecordsAffected & " Records were deleted"
Set dbs = Nothing

If you have indexes on each of the fields used in the
comparison, the query will probably run a lot faster than if
there are no indexes.

Do those three fields make up the unique identifier for the
records in the table? Seems like you're relying on there
never being more then one person with the same first and
last name in a zip code area. This is a very shaky
assumption.
Click to expand...
Click to expand...
 
Back
Top