468,161 Members | 2,028 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,161 developers. It's quick & easy.

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
Apr 8 '08 #1
2 3515
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

Apr 8 '08 #2
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
Apr 8 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by dSchwartz | last post: by
16 posts views Thread by fniles | last post: by
1 post views Thread by gcdp | last post: by
reply views Thread by kamranasdasdas | last post: by
reply views Thread by gcreed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.