Find TableName of related table

Dear All,

Is ( how) it possible to find the tablename of the table that is related to
a field.

Field CLIENT_TITLE_ID is related to table TITLE

Thanks,

Filip

re: Find TableName of related table

Filips Benoit wrote:[color=blue]
> Dear All,
>
> Is ( how) it possible to find the tablename of the table that is related to
> a field.
>
> Field CLIENT_TITLE_ID is related to table TITLE[/color]

Do you mean in a query? See the DAO reference, Field object, properties.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

re: Find TableName of related table

This function lists the indexes of a table, and the fields involed in the
index.

If it is a Foreign index, you can use the properties to determine the
foreign table and field.

Function ShowIndexes(strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
Dim fld As DAO.Field

Set db = DBEngine(0)(0)
Set tdf = db.TableDefs(strTable)
For Each ind In tdf.Indexes
Debug.Print ind.Name, IIf(ind.Primary, "Primary", ""), _
IIf(ind.Foreign, "Foreign", ""), ind.Fields.Count
Debug.Print " Field(s): ";
For Each fld In ind.Fields
Debug.Print fld.Name;
Next
Debug.Print
Next

Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Function


--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Filips Benoit" <benoit.filips@pandora.be> wrote in message
news:VeBvd.35$Dh4.139336@phobos.telenet-ops.be...[color=blue]
> Dear All,
>
> Is ( how) it possible to find the tablename of the table that is related
> to
> a field.
>
> Field CLIENT_TITLE_ID is related to table TITLE
>
> Thanks,
>
> Filip
>
>[/color]

re: Find TableName of related table

I created this function now using MSysRelationships.

************************************************** **************************
************
Public Function FindFieldRelatedTable(ByVal strTableName As String, ByVal
strFieldName As String) As String
On Error GoTo errHandling

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = New ADODB.Connection
Set conn = CurrentProject.Connection

Set rs = New ADODB.Recordset
rs.Open "SELECT MSysRelationships.szReferencedObject FROM
MSysRelationships WHERE (((MSysRelationships.szObject)='" & strTableName &
"') AND ((MSysRelationships.szReferencedColumn)='" & strFieldName & "'));",
conn, adOpenForwardOnly, adLockReadOnly, adCmdText
FindFieldRelatedTable = rs.Fields(0).Value
rs.Close
Set rs = Nothing

Exit Function

errHandling:

MsgBox "Foutnr. " & Err.Number & " in functie FindFieldRelatedTable " &
Chr(13) & Err.Description
End Function
************************************************** **************************
******

"Filips Benoit" <benoit.filips@pandora.be> wrote in message
news:VeBvd.35$Dh4.139336@phobos.telenet-ops.be...[color=blue]
> Dear All,
>
> Is ( how) it possible to find the tablename of the table that is related[/color]
to[color=blue]
> a field.
>
> Field CLIENT_TITLE_ID is related to table TITLE
>
> Thanks,
>
> Filip
>
>[/color]

re: Find TableName of related table

Unless I am missing something, you may need to provide more info.

Is your question: How do I determine the name of the table(s) joined to
table TITLE, where the from/to join field is CLIENT_TITLE_ID?

If so, query the System table, MSysRelationships:

SELECT MSysRelationships.szObject
FROM MSysRelationships
WHERE (((MSysRelationships.szReferencedObject)="TITLE") AND
((MSysRelationships.szReferencedColumn)="CLIENT_TI TLE_ID"));

Tony D'Ambra
Web Site: aadconsulting.com
Web Blog: accessextra.net


"Filips Benoit" <benoit.filips@pandora.be> wrote in message
news:VeBvd.35$Dh4.139336@phobos.telenet-ops.be...[color=blue]
> Dear All,
>
> Is ( how) it possible to find the tablename of the table that is related
> to
> a field.
>
> Field CLIENT_TITLE_ID is related to table TITLE
>
> Thanks,
>
> Filip
>
>[/color]