VBA code to check memo fields in all tables (1 Viewer)

aman · Feb 12, 2019

Hi Guys,

Is it possible to write vba code that will clear data from the memo fields from all the tables in the database.

One way will be writing queries for each table and updating memo fields to null. But this way if I have 50 tables in the database then I will need to write down 50 queries to clear out memo fields.

Is there any shortcut to that?

Thanks

Frothingslosh · Feb 12, 2019

You could write VBA code that cycles through every table, identifies any memo fields, then builds and executes SQL on the fly to delete the contents of those fields. Whether that's faster than just doing it by hand will depend on the number of tables and your skills at VBA.

Gasman · Feb 12, 2019

Shortcut?, I would not have thought so.

However you could read through all the tables in Tabledefs that do not start with a MSys
Inspect each field to see if the type is Long text (12) and then construct a SQL string with the fields and table name and execute if a memo field is found. ?

Ranman256 · Feb 12, 2019

Why would you have 50 tables?

Gasman · Feb 12, 2019

This should get you most of the way there.

Use the debug.print version to check it all looks good, run it against a test version of your db, and amend to suit.

HTH

Code:

Sub ClearMemo()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String
Dim blnMemo As Boolean

Set db = CurrentDb()

For Each tdf In db.TableDefs
    If Left(tdf.Name, 4) <> "MSys" Then
        blnMemo = False
        strSQL = "Update " & tdf.Name & " SET "
        For Each fld In tdf.Fields
            'Debug.Print fld.Name & " " & fld.Type
            If fld.Type = 12 Then ' Long Text
                blnMemo = True
                strSQL = strSQL & fld.Name & " = NULL, "
            End If
        Next
        If blnMemo Then
            strSQL = Left(strSQL, Len(strSQL) - 2)
            Debug.Print strSQL
            'db.Execute strSQL, dbFailOnError

        End If
    End If
Next

Set tdf = Nothing
Set db = Nothing

End Sub

Mark_ · Feb 12, 2019

If you don't mind sharing, why are you looking to empty all memo fields?

VBA code to check memo fields in all tables (1 Viewer)

aman

Registered User.

Frothingslosh

Premier Pale Stale Ale

Gasman

Enthusiastic Amateur

Ranman256

Well-known member

Gasman

Enthusiastic Amateur

Mark_

Longboard on the internet

Similar threads

Users who are viewing this thread