By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,953 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

Find TableName of related table

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Filips Benoit wrote:
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


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
Nov 13 '05 #2

P: n/a
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" <be***********@pandora.be> wrote in message
news:Ve*****************@phobos.telenet-ops.be...
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

Nov 13 '05 #3

P: n/a
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" <be***********@pandora.be> wrote in message
news:Ve*****************@phobos.telenet-ops.be...
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

Nov 13 '05 #4

P: n/a
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" <be***********@pandora.be> wrote in message
news:Ve*****************@phobos.telenet-ops.be...
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

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.