Defining fields when creating a relationship

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

H
I am trying to create a relationship between two existing tables
How do I define the fields from the two tables
Dim dbs As Database, rel As Relatio
Set rel = dbs.CreateRelation("xxxx"
rel.Table = "Table1
rel.ForeignTable = "Table2
rel.Attributes = dbRelationDeleteCascade And dbRelationUpdateCascad

' Define field from Table - Nam
' Define field from ForeignTable - ForeignNam

dbs.Relations.Append re

All the sample help topics create a new field before defining the ForeignName
Thanks in advance for any suggestion
 
David,

Public Sub CreateRelation(strRelName As String, _
strSrcTable As String, strSrcField As String, _
strDestTable As String, strDestField As String)

Dim dbs As Database
Dim fld As DAO.Field
Dim rel As DAO.Relation
Dim varRel As Variant

Set dbs = CurrentDb
On Error Resume Next

'Check if the relationship already exists.
'If so, delete it.
If IsObject(dbs.Relations(strRelName)) Then
dbs.Relations.Delete strRelName
End If

'Create the relation object
Set rel = dbs.CreateRelation(strRelName, _
strSrcTable, _
strDestTable)

rel.Attributes = dbRelationLeft Or _
dbRelationUpdateCascade Or _
dbRelationDeleteCascade

'Append the field(s) involved in the relationship
Set fld = rel.CreateField(strSrcField)
fld.ForeignName = strDestField

'Append the field to the relation's Fields collection
rel.Fields.Append fld

'Append the relation to the Database's Relations collection
dbs.Relations.Append rel
'Refresh the Relations collection
dbs.Relations.Refresh

Set rel = Nothing
Set fld = Nothing
Set dbs = Nothing
End Sub

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Back
Top