MS Access find and replace text in column fields multiple tables

Question

I have an MS Access database with over 100 tables that conditionally need columns renamed.

Each table needs to be opened and any field names that contain the following string, "AAA_" needs to be replaced with "BBB_".

Is there a way to automate this process? I'm trying to avoid doing this exercise manually. I don't really know vba and I experimented with some update queries to no avail. When using the native query design functionality, it only seems to look at the corresponding records for a field name, but not the field name itself.

Thanks for any insight.

Answer 1

The following function could be used to do what you want. You need to adjust it your needs as the conditions you refer to are not clear

    Function renameField(tableName As String, fieldName As String, newFieldName As String) As Boolean

        On Error GoTo EH

        Dim db As DAO.Database
        Dim td As DAO.TableDef
        Dim fd As DAO.Field

        Set db = CurrentDb
        Set td = db.TableDefs(tableName)

        For Each fd In td.Fields
            If fd.Name = fieldName Then
                fd.Name = newFieldName
                renameField = True
                Exit For
            End If
        Next
        
        Exit Function
        
    EH:
        renameField = False
        
    End Function

An adjustment could look like that

 Sub renameFldsInAllTables()

    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim fd As DAO.Field
    Set db = CurrentDb
    
    For Each td In db.TableDefs
        For Each fd In td.Fields
            ' If Left(fd.Name, 4) = "AAA_" Then
            If InStr(1, fd.Name, "AAA_", vbTextCompare) Then
                fd.Name = Replace(fd.Name, "AAA_", "BBB_", 1, 1)
            End If
        Next
    Next

End Sub

Answer 2

This sounds like a very weird request. Why are you changing all kinds of field names in a database? It seems like you have a very poor, or weak, data design!!

Put this code behind a button on a form.

Private Sub Command1_Click()

Dim counter1 As Long
Dim counter2 As Long
Dim tbl As TableDef
Dim fld As Field
    For Each tbl In CurrentDb.TableDefs
    Debug.Print tbl.Name
        'If tbl.Name = Table Then
            For Each fld In tbl.Fields
            Debug.Print fld.Name
            If fld.Name = "Pictures" Then
                fld.Name = "Picture"
                Exit For
            End If
            Next
        Exit For
        'End If
    Next
End Sub