[AccessD] lowercase

Hi Pedro,
This should do what you want (make sure your project has a DAO reference
set):

Sub ConvertAllFieldsToLowercase()

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim strSQL As String
    
    'Loop through all tables in the current database
    Set db = CurrentDb()
    
    For Each tdf In db.TableDefs
        'Exclude system tables
        If Not (tdf.Name Like "MSys*") Then
            'Build an update query, adding all text/memo fields
            Debug.Print tdf.Name
            strSQL = ""
            
            For Each fld In tdf.Fields
                If fld.Type = dbText Or fld.Type = dbMemo Then
                    If strSQL <> "" Then
                        strSQL = strSQL & ", "
                    End If
                
                    strSQL = strSQL & "[" & fld.Name & "]=LCase([" &
fld.Name & "])"
                End If
            Next fld
            
            'Are there any text fields in this table?
            If strSQL <> "" Then
                'Execute the update query
                strSQL = "UPDATE [" & tdf.Name & "] SET " & strSQL & ";"
                db.Execute strSQL
            End If
        End If
    Next tdf
    
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing

End Sub

-----Original Message-----
From: pedro at plex.nl [mailto:pedro at plex.nl]
Sent: Wednesday, February 04, 2004 10:18 AM
To: AccessD at databaseadvisors.com
Subject: [AccessD] lowercase


Hello group,

what is the quickest way to set al caracters in all fields in all Tables in
a database to lowercase.
I can make a query an do all the fields with an LCase expression, but that
costs a lot of time. 

Any idea's

TIA

Pedro Janssen 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--------------------------------------------------------------------------------------------------------------------
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom
they are addressed.
If you have received this email in error please notify the
originator of the message. This footer also confirms that this
email message has been scanned for the presence of computer viruses.

Any views expressed in this message are those of the individual
sender, except where the sender specifies and with authority,
states them to be the views of Tappe Construction Co.

Scanning of this message and addition of this footer is performed
by SurfControl E-mail Filter software in conjunction with 
virus detection software.