[AccessD] Cycle thru All Tables in DB

After playing with the code, I came up with the following, which does
exactly what I needed.
 
Public Sub CycleThruTables()
    Dim td As TableDef
    Dim fld As Field
    Dim i As Integer
 
    For Each td In CurrentDb.TableDefs
        If Left(td.Name, 4) <> "MSys" Then 'not a system table
            i = 0
            For Each fld In td.Fields
                Set fld = td.Fields(i)
                DoCmd.RunSQL ("UPDATE [" & td.Name & "] SET [" &
fld.Name & "] = Null WHERE [" & fld.Name & "] = ''")
                i = i + 1
            Next
        End If
    Next
    
    Set td = Nothing
    Set fld = Nothing
End Sub

 
Mark Boyd 
Senior Consultant 
Enterprise Risk Services 
Deloitte & Touche LLP 
  
Tel: +1 215 405 5576
mboyd at deloitte.com 
www.deloitte.com <http://www.deloitte.com/>  
 

________________________________

From: Boyd, Mark Thomas (US - Philadelphia) 
Sent: Tuesday, November 29, 2005 1:02 PM
To: 'Access Developers discussion and problem solving'
Subject: Cycle thru All Tables in DB


I'm sure this is possible, but have never coded it.
I have 50 tables, each containing 50 fields.  Some of the fields contain
empty strings.  I need to cycle thru every table, and update all empty
strings to null.
 
Is there a way to cycle thru each table, and cycle thru each field
record by record, and update to null where necessary?
I want to avoid using specific table and field names.
 
Any ideas?
Thanks.
 
Mark Boyd 
Senior Consultant 
Enterprise Risk Services 
Deloitte & Touche LLP 
  
Tel: +1 215 405 5576
mboyd at deloitte.com 
www.deloitte.com <http://www.deloitte.com/> 


This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law.  If you are not the intended recipient, you should delete this message. 


Any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. [v.E.1]