Removal of repeated values between two tables with VBA

Asked

Viewed 58 times

-1

I need help with the elimination of equal values between two tables.

I have two tables with several different fields, they have a common field called cellular, one table has the number in the format 55119xxxxx and the other has it in the format +55119xxxxx. My goal is to eliminate records where cell phones are equal (from both tables). I made a code in VBA to delete the records in which these fields have the same value (independent of the contents of the other fields), see below:

set rstb1 = currentDb.openrecordset("SELECT * FROM tabela1") 'Recordset da primeira tabela
set rstb2 = currentDb.openrecordset("SELECT * FROM tabela2") 'Recordset da segunda tabela

Do While Not rstb1.EOF
    Do While Not rstb2.EOF
        If rstb1.Fields("celular") = Right(rstb2.Fields("celular"), 13) Then
        rstb1.Delete
        rstb2.Delete
        rstb1.MoveNext
        End If
        rstb2.MoveNext
    Loop
    rstb1.MoveNext
    rstb2.MoveFirst
Loop

Note: cell phones only repeat once.

The code works, but it takes a long time to perform the removals, since each table has more than 30,000 records, which would give about 9 million repetitions in the above loop. I need a faster way to do these removals.

sql vba table ms-access

1 answer

-1

I would try to use:

Application.ScreenUpdate = False
Application.Calculation  = xlCalculationManual

At the beginning of the code and:

Application.ScreenUpdate = True
Application.Calculation  = xlCalculationAutomatic

At the end of it.

Browser other questions tagged sql vba table ms-access

You are not signed in. Login or sign up in order to post.