Referencing a field name in a table

[George Too]

Hello all,
With code, I want to search several tables for a specific field name. If the field name doesn't exist in a table move on to the next. Is this possible? If so, what would the code look like?

Thanks,
George

 

[Mile-O]

How's this?

Public Function GetField(ByVal strField As String) As String

    Dim tdf As TableDef
    Dim fld As Field
    Dim strTables As String
    
    For Each tdf In CurrentDb.TableDefs
        For Each fld In tdf.Fields
            If fld.Name = strField Then
                strTables = strTables & fld.Name & " found in " & tdf.Name & vbCrLf
            End If
        Next
    Next
 
    GetField = strTables

End Function

Call it like this:

MsgBox GetField("yourFieldName")

 

[dcx693]

Just for kicks, I adapted Mile's code for use with ADOX:


Public Function GetField(ByVal strField As String) As String

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim fld As ADOX.Column
Dim strTables As String

 Set cat = New ADOX.Catalog
 cat.ActiveConnection = CurrentProject.Connection

 For Each tbl In cat.Tables
  If tbl.Type = "TABLE" Then
   For Each fld In tbl.Columns
    If fld.Name = strField Then
     strTables = strTables & fld.Name & " found in " & tbl.Name & vbCrLf
    End If
   Next fld
  End If
 Next tbl

 GetField = strTables

End Function

 

[George Too]

Thanks guys, both look good and I'll implement the code as soon as I have the time.
Thanks again.
George