Loop through several tables and delete

[farouqdin@hotmail.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.OpenRecordse t("tbl_temperat ure")



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]

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.openrecordse t(tblData)
rsTables.MoveFi rst
Do While not rsTables.EOF
Set rst = db.OpenRecordse t("tbl_temperat ure")
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



<farouqdin@hotm ail.comwrote in message
news:b106960c-9ee1-4e7d-8349-c3cb468f5896@a2 2g2000hsc.googl egroups.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.OpenRecordse t("tbl_temperat ure")
>
>
>
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@hotmail.com]

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.OpenRecordse t(Tbl_Duplicate s)
rsTables.MoveFi rst
Do While Not rsTables.EOF
Set rst = db.OpenRecordse t(rsTables!Tabl eName)
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