Access Utility to Find a Field in MDB

Question

HI

I want to find a particular field, which exist in tables of a Access database. Is there is any utility to find this?

Answer 1

Yes you can do it VBA code. I have emailed you.

Public Function FindField(fieldname As String)


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

    Set db = DBEngine(0)(0)
    db.TableDefs.Refresh

    For Each td In db.TableDefs
        For Each fd In td.fields
            If fieldname = fd.Name Then
                Debug.Print td.Name
            End If
        Next
    Next
    db.Close

End Function

Answer 2

You can use ADO Schemas:

   Function ListTablesContainingField(SelectFieldName) As String
   ''Tables returned will include linked tables
   Dim cn As New ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim strTempList As String

       On Error GoTo Error_Trap

       Set cn = CurrentProject.Connection

       ''Get names of all tables that have a column called <SelectFieldName>
       Set rs = cn.OpenSchema(adSchemaColumns, _
       Array(Empty, Empty, Empty, SelectFieldName))

       ''List the tables that have been selected
       While Not rs.EOF
           ''Exclude MS system tables
           If Left(rs!Table_Name, 4) <> "MSys" Then
               strTempList = strTempList & "," & rs!Table_Name
           End If
           rs.MoveNext
       Wend

       ListTablesContainingField = Mid(strTempList, 2)

   Exit_Here:

       rs.Close
       Set cn = Nothing
       Exit Function

   Error_Trap:

       MsgBox Err.Description
       Resume Exit_Here
   End Function

See also: http://support.microsoft.com/kb/186246