Determine if field name exists in two tables 1

[jkirkland]

I want to compare a table that contains 8 fields to all the tables in my database to determine which tables contain all 8 of those fields.

Unfortunately I can't visualize the code. What I have so far starts with:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim cpRst As DAO.Recordset 'Account Field Comparison Table
Dim cpFld As DAO.Field 'Account Table (Field Name)

Set db = CurrentDb
Set cpRst = db.OpenRecordset("tblCompareAcctFlds")

For Each tdf In db.TableDefs
If Left(tdf.Name, 3) = "HTE" Then
If tdf.SourceTableName = "" Then
For Each fld In tdf.Fields
Do While Not cpRst.EOF
If cpFld.Name <> fld.Name Then
End If
End If
Next tdf
End Sub

I assume I will need to enumerate the results of the field name comparison code but I don't know how to do this.

Any help is appreciated.
Thanks!

 

[flyover789]

jkirkland,

the code you posted looks very well for a start.

I'suggest, you create another table in your DB, with two fields: "tblname" and "fldname"
In your function you can add the results of your comparison to this table.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rsdocu as DAO.Recordset 'table to document the comparison results 
Dim cpRst As DAO.Recordset   'Account Field Comparison Table
Dim cpFld As DAO.Field       'Account Table (Field Name)
  
  Set db = CurrentDb
  Set cpRst = db.OpenRecordset("tblCompareAcctFlds")
  
    For Each tdf In db.TableDefs
        If Left(tdf.Name, 3) = "HTE" Then
            If tdf.SourceTableName = "" Then
                For Each fld In tdf.Fields
                    Do Until cpRst.EOF
                        If cpFld.Name <> fld.Name Then
                           rsdocu.addnew
                           rsdocu.fields(0).value = tdf.name
                           rsdocu.fields(1).value = "nomatch"
                           rsdocu.update
                        Else:
                           rsdocu.addnew
                           rsdocu.fields(0).value = tdf.name
                           rsdocu.fields(1).value = fld.Name
                           rsdocu.update
                        End If
                        loop
              End if
       End if
    Next tdf
End Sub

typed, not tested,

fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[jkirkland]

That works!

Thank you very much for the quick assistance.