Loop through several tables and delete

[farouqdin]

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

[KC-Mass]

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

<fa*******@hotmail.comwrote in message
news:b1**********************************@a22g2000 hsc.googlegroups.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

[farouqdin]

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