Loop through all tables in database and delete records if Name field is blank

cskehan
cskehan used Ask the Experts™
on
Is there a way to loop through all tables in a database and in the name field is blank delete the record.


I am using Access 2007
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
elaborate on <the name field is blank > 
is the name of the field "Name" ?
Top Expert 2016
Commented:
test this code

Sub delBlank()
Dim td As DAO.TableDef, db As DAO.Database, fld As DAO.Field
Set db = CurrentDb
For Each td In db.TableDefs
    If Not td.Name Like "msys*" Then
       
        For Each fld In db.TableDefs(td.Name).Fields
            If fld.Name = "Name" Then
            db.Execute "delete * from  [" & td.Name & "] where [" & fld.Name & "] is null"
            End If
        Next

    End If
Next

End Sub
cskehanCFO

Author

Commented:
Works perfectly.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial