Loop through several tables and delete

Hi all i have code which loops through table and deletes the duplicate
records. This code does it for one table. How do i change it so it
goes through several tables?

On Error Resume Next

Dim db As DAO.Database, rst As DAO.Recordset
Dim strDupName As String, strSaveName As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_temperature")



If rst.BOF And rst.EOF Then
MsgBox "No records to process"
Else
rst.MoveFirst
Do Until rst.EOF
strDupName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2) &
rst.Fields(3)
If strDupName = strSaveName Then
rst.Delete
Else
strSaveName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2) &
rst.Fields(3)
End If
rst.MoveNext
Loop

Set rst = Nothing
Set db = Nothing

MsgBox "Deleted Duplicates"
' Call RestoreData
End If



How do i set it so it does it for several tables? It would save me
time copying the whole code and for each table. The changing factor is
that they are several different tables that have duplicate records.

regards

farouq

Re: Loop through several tables and delete

Put your table names in a table named tblData with one field, TableName.
See changes (air code) to code below.

Regards Kevin


Dim db As DAO.Database, rst As DAO.Recordset
Dim rsTables as DAO.RecordSet
Dim strDupName As String, strSaveName As String

Set db = CurrentDb()
Set rsTables = db.openrecordset(tblData)
rsTables.MoveFirst
Do While not rsTables.EOF
Set rst = db.OpenRecordset("tbl_temperature")
If rst.BOF And rst.EOF Then
MsgBox "No records to process"
Else
rst.MoveFirst
Do Until rst.EOF
strDupName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2) &
rst.Fields(3)
If strDupName = strSaveName Then
rst.Delete
Else
strSaveName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2) &
rst.Fields(3)
End If
rst.MoveNext
Loop
Loop
Set rst = Nothing
Set db = Nothing
MsgBox "Deleted Duplicates"
' Call RestoreData
End If



wrote in message
news:b106960c-9ee1-4e7d-8349-c3cb468f5896@a22g2000hsc.google groups.com...
> Hi all i have code which loops through table and deletes the duplicate
> records. This code does it for one table. How do i change it so it
> goes through several tables?
>
> On Error Resume Next
>
> Dim db As DAO.Database, rst As DAO.Recordset
> Dim strDupName As String, strSaveName As String
>
> Set db = CurrentDb()
> Set rst = db.OpenRecordset("tbl_temperature")
>
>
>
> If rst.BOF And rst.EOF Then
> MsgBox "No records to process"
> Else
> rst.MoveFirst
> Do Until rst.EOF
> strDupName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2) &
> rst.Fields(3)
> If strDupName = strSaveName Then
> rst.Delete
> Else
> strSaveName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2) &
> rst.Fields(3)
> End If
> rst.MoveNext
> Loop
>
> Set rst = Nothing
> Set db = Nothing
>
> MsgBox "Deleted Duplicates"
> ' Call RestoreData
> End If
>
>
>
> How do i set it so it does it for several tables? It would save me
> time copying the whole code and for each table. The changing factor is
> that they are several different tables that have duplicate records.
>
> regards
>
> farouq

Re: Loop through several tables and delete

One correction.

Need to change the line "Set rst = db.OpenRecordset("tbl_temperature")"
to be "Set rst = db.OpenRecordset(rsTables!TableName)"

"KC-Mass" wrote in message
news:lu6dnWakraIFymbanZ2dnUVZ_gednZ2d@comcast.com...
> Put your table names in a table named tblData with one field, TableName.
> See changes (air code) to code below.
>
> Regards Kevin
>
>
> Dim db As DAO.Database, rst As DAO.Recordset
> Dim rsTables as DAO.RecordSet
> Dim strDupName As String, strSaveName As String
>
> Set db = CurrentDb()
> Set rsTables = db.openrecordset(tblData)
> rsTables.MoveFirst
> Do While not rsTables.EOF
> Set rst = db.OpenRecordset(rsTables!TableName)
> If rst.BOF And rst.EOF Then
> MsgBox "No records to process"
> Else
> rst.MoveFirst
> Do Until rst.EOF
> strDupName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2) &
> rst.Fields(3)
> If strDupName = strSaveName Then
> rst.Delete
> Else
> strSaveName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2) &
> rst.Fields(3)
> End If
> rst.MoveNext
> Loop
> Loop
> Set rst = Nothing
> Set db = Nothing
> MsgBox "Deleted Duplicates"
> ' Call RestoreData
> End If
>
>
>
> wrote in message
> news:b106960c-9ee1-4e7d-8349-c3cb468f5896@a22g2000hsc.google groups.com...
>> Hi all i have code which loops through table and deletes the duplicate
>> records. This code does it for one table. How do i change it so it
>> goes through several tables?
>>
>> On Error Resume Next
>>
>> Dim db As DAO.Database, rst As DAO.Recordset
>> Dim strDupName As String, strSaveName As String
>>
>> Set db = CurrentDb()
>> Set rst = db.OpenRecordset("tbl_temperature")
>>
>>
>>
>> If rst.BOF And rst.EOF Then
>> MsgBox "No records to process"
>> Else
>> rst.MoveFirst
>> Do Until rst.EOF
>> strDupName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2) &
>> rst.Fields(3)
>> If strDupName = strSaveName Then
>> rst.Delete
>> Else
>> strSaveName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2) &
>> rst.Fields(3)
>> End If
>> rst.MoveNext
>> Loop
>>
>> Set rst = Nothing
>> Set db = Nothing
>>
>> MsgBox "Deleted Duplicates"
>> ' Call RestoreData
>> End If
>>
>>
>>
>> How do i set it so it does it for several tables? It would save me
>> time copying the whole code and for each table. The changing factor is
>> that they are several different tables that have duplicate records.
>>
>> regards
>>
>> farouq
>
>

Re: Loop through several tables and delete

Hi i get an error msg saying "loop without do" It highlights the 2nd
loop

Dim db As DAO.Database, rst As DAO.Recordset
Dim rsTables As DAO.Recordset
Dim strDupName As String, strSaveName As String


Set db = CurrentDb()
Set rsTables = db.OpenRecordset(Tbl_Duplicates)
rsTables.MoveFirst
Do While Not rsTables.EOF
Set rst = db.OpenRecordset(rsTables!TableName)
If rst.BOF And rst.EOF Then
MsgBox "No records to process"
Else
rst.MoveFirst
Do Until rst.EOF
strDupName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2) &
rst.Fields(3)
If strDupName = strSaveName Then
rst.Delete
Else
strSaveName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2) &
rst.Fields(3)
End If
rst.MoveNext
Loop
Loop
Set rst = Nothing
Set db = Nothing
MsgBox "Deleted Duplicates"
' Call RestoreData
End If

many thanks for replying

Re: Loop through several tables and delete

wrote in message
news:be0a0065-ed03-4202-bd1e-ede2d0590b74@q27g2000prf.google groups.com...
> Hi i get an error msg saying "loop without do" It highlights the 2nd
> loop
>
> Dim db As DAO.Database, rst As DAO.Recordset
> Dim rsTables As DAO.Recordset
> Dim strDupName As String, strSaveName As String
>
>
> Set db = CurrentDb()
> Set rsTables = db.OpenRecordset(Tbl_Duplicates)
> rsTables.MoveFirst
> Do While Not rsTables.EOF
> Set rst = db.OpenRecordset(rsTables!TableName)
> If rst.BOF And rst.EOF Then
> MsgBox "No records to process"
> Else
> rst.MoveFirst
> Do Until rst.EOF
> strDupName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2) &
> rst.Fields(3)
> If strDupName = strSaveName Then
> rst.Delete
> Else
> strSaveName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2) &
> rst.Fields(3)
> End If
> rst.MoveNext
> Loop
> Loop
> Set rst = Nothing
> Set db = Nothing
> MsgBox "Deleted Duplicates"
> ' Call RestoreData
> End If
>
> many thanks for replying

You need an End If statement between the two loop statements, in order to
finish off:

> If rst.BOF And rst.EOF Then
> MsgBox "No records to process"
> Else