HowTo Link Table With VBA Code ?

[MeTwo]

I would like to link 2 tables with MS ACCESS VBA code, is some one could give me a program example ?

Ex :
-------- --------
| ID_0 |--------->| ID_1 |
| ... | | ... |
| ... | | ... |
-------- --------
I could do that with the mouse but I don't know how to do with VBA, please help me !!!!!!!!!!!!!

 

[MichaelRed]

[red]Below copied and pasted from HELP.[/red]




Relations Collection


A Relations collection contains stored Relation objects of a Database object (Microsoft Jet databases only).






Remarks

You can use the Relation object to create new relationships and examine existing relationships in your database. To add a Relation object to the Relations collection, first create it with the CreateRelation method, and then append it to the Relations collection with the Append method. This will save the Relation object when you close the Database object. To remove a Relation object from the collection, use the Delete method.

To refer to a Relation object in a collection by its ordinal number or by its Name property setting, use any of the following syntax forms:

Relations(0)

Relations("name&quot

Relations![name]

'_________________________________________________________
CreateRelation Method Example

This example uses the CreateRelation method to create a Relation between the Employees TableDef and a new TableDef called Departments. This example also demonstrates how creating a new Relation will also create any necessary Indexes in the foreign table (the DepartmentsEmployees Index in the Employees table).

Sub CreateRelationX()

Dim dbsNorthwind As Database
Dim tdfEmployees As TableDef
Dim tdfNew As TableDef
Dim idxNew As Index
Dim relNew As Relation
Dim idxLoop As Index

Set dbsNorthwind = OpenDatabase("Northwind.mdb&quot

With dbsNorthwind
' Add new field to Employees table.
Set tdfEmployees = .TableDefs!Employees
tdfEmployees.Fields.Append _
tdfEmployees.CreateField("DeptID", dbInteger, 2)

' Create new Departments table.
Set tdfNew = .CreateTableDef("Departments&quot

With tdfNew
' Create and append Field objects to Fields
' collection of the new TableDef object.
.Fields.Append .CreateField("DeptID", dbInteger, 2)
.Fields.Append .CreateField("DeptName", dbText, 20)

' Create Index object for Departments table.
Set idxNew = .CreateIndex("DeptIDIndex&quot
' Create and append Field object to Fields
' collection of the new Index object.
idxNew.Fields.Append idxNew.CreateField("DeptID&quot
' The index in the primary table must be Unique in
' order to be part of a Relation.
idxNew.Unique = True
.Indexes.Append idxNew
End With

.TableDefs.Append tdfNew

' Create EmployeesDepartments Relation object, using
' the names of the two tables in the relation.
Set relNew = .CreateRelation("EmployeesDepartments", _
tdfNew.Name, tdfEmployees.Name, _
dbRelationUpdateCascade)

' Create Field object for the Fields collection of the
' new Relation object. Set the Name and ForeignName
' properties based on the fields to be used for the
' relation.
relNew.Fields.Append relNew.CreateField("DeptID&quot
relNew.Fields!DeptID.ForeignName = "DeptID"
.Relations.Append relNew

' Print report.
Debug.Print "Properties of " & relNew.Name & _
" Relation"
Debug.Print " Table = " & relNew.Table
Debug.Print " ForeignTable = " & _
relNew.ForeignTable
Debug.Print "Fields of " & relNew.Name & " Relation"

With relNew.Fields!DeptID
Debug.Print " " & .Name
Debug.Print " Name = " & .Name
Debug.Print " ForeignName = " & .ForeignName
End With

Debug.Print "Indexes in " & tdfEmployees.Name & _
" TableDef"
For Each idxLoop In tdfEmployees.Indexes
Debug.Print " " & idxLoop.Name & _
", Foreign = " & idxLoop.Foreign
Next idxLoop

' Delete new objects because this is a demonstration.
.Relations.Delete relNew.Name
.TableDefs.Delete tdfNew.Name
tdfEmployees.Fields.Delete "DeptID"
.Close
End With

End Sub


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[SetBuilder]

The way I do this, is first do it using a query, and the query builder.

Then click on view, and then SQL. That will give you the sql code to create the relationship.