MS Access Metadata

I'm performing a data cleansing operation on an access database. I have several duplicate records in a table that I want to consolidate down into one single record. In doing this I will need to update all references to the records that I will be consolidating.

If I know the column name that holds the record i开发者_开发问答d is there a way to find all of the tables in access that contain this column?

---

You can examine the TableDefs collection and determine which tables contain a field with a given name.

Public Sub TablesWithField(ByVal pName As String)
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strMsg As String
    Dim strName As String

On Error GoTo ErrorHandler

    Set db = CurrentDb
    For Each tdf In db.TableDefs
        strName = vbNullString
        'ignore system and temporary tables '
        If Not (tdf.name Like "MSys*" Or tdf.name Like "~*") Then
            strName = tdf.Fields(pName).name
            If Len(strName) > 0 Then
                Debug.Print tdf.name & ": " & pName
            End If
        End If
    Next tdf

ExitHere:
    On Error GoTo 0
    Set tdf = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    Select Case Err.Number
    Case 3265 'Item not found in this collection. '
        Resume Next
    Case Else
    strMsg = "Error " & Err.Number & " (" & Err.description _
        & ") in procedure TablesWithField"
    MsgBox strMsg
    GoTo ExitHere
    End Select
End Sub

---

Short answer: Yes. And there are many ways to skin that cat. Two ideas:

(1) Via VBA, make use of: Application.CurrentDb.TableDefs(i).Fields(j).Name

(2) Via Tools==>Analyze==>Documenter, make a report and then search its output (Publish it with MS Word).

---

Sorry, but Access isn't built like MS SQL Server or DB2 - the MSys* tables really aren't set up for querying table schemas like that. However, others have VBA based solutions that look useful.

---

You can use Schemas, not exactly a query, but similar:

Function ListTablesContainingField(SelectFieldName) As String
   'Tables returned will include linked tables
   'I have added a little error coding. I don't normally do that
   'for examples, so don't read anything into it :) 
   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