Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / March 2005

Tip: Looking for answers? Try searching our database.

Deleting records in one table that appear in another table

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
rjphillips - 11 Mar 2005 10:51 GMT
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
Marshall Barton - 11 Mar 2005 14:33 GMT
>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
[quoted text clipped - 55 lines]
>
>End Sub

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.

Signature

Marsh
MVP [MS Access]

Rich - 15 Mar 2005 09:35 GMT
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'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
[quoted text clipped - 87 lines]
> last name in a zip code area.  This is a very shaky
> assumption.
rjphillips - 15 Mar 2005 14:57 GMT
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!

> 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
[quoted text clipped - 101 lines]
> > last name in a zip code area.  This is a very shaky
> > assumption.
Marshall Barton - 15 Mar 2005 17:39 GMT
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.
Signature

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
[quoted text clipped - 11 lines]
>> >I have the following code but this seems to make Access 'Hang'.  Is this the
>> >best/correct way of performing this type of operation?
[snip]

>> I can't say why the RecordCount seems off, but your code
>> will run forever because you forgot the MoveNext in some of
[quoted text clipped - 27 lines]
>> last name in a zip code area.  This is a very shaky
>> assumption.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.