[RESOLVED] How do I create a many-to-many Relation?

[Mark@SF]

I need to bind a DataGridView to a many-to-many Relation.



I know how to create both of the one-to-many Relations:

'VendorTypes and VendorMembers
dsMain.Relations.Add("VendorTypes_VendorMembers", dsMain.Tables("VendorTypes").Columns("RecID"), dsMain.Tables("VendorMembers").Columns("VendorTypeID"))
fkc = dsMain.Relations("VendorTypes_VendorMembers").ChildKeyConstraint
fkc.DeleteRule = Rule.Cascade
fkc.UpdateRule = Rule.Cascade
fkc.AcceptRejectRule = AcceptRejectRule.None

'Vendors and VendorMembers
dsMain.Relations.Add("Vendors_VendorMembers", dsMain.Tables("Vendors").Columns("RecID"), dsMain.Tables("VendorMembers").Columns("VendorID"))
fkc = dsMain.Relations("Vendors_VendorMembers").ChildKeyConstraint
fkc.DeleteRule = Rule.Cascade
fkc.UpdateRule = Rule.Cascade
fkc.AcceptRejectRule = AcceptRejectRule.None

I know how to bind a DataGrid view to a Relation, for example:

With bsFuel
    .DataSource = Nothing
    .DataMember = "Vehicle_Fuel"    '...bind to relationship (before setting the DataSource property, which fires the PositionChanged() event of the BindingSource)
    .DataSource = bsVehicles    '...fires the BindingSource's PositionChanged() event
    .Sort = "VehicleID, Odometer"
End With
If flgDebug Then Debug.WriteLine("bsFuel datasource has " & bsFuel.Count & " items.")

dgvFuel.DataSource = bsFuel

How do I create a many-to-many Relation and use it for binding a DataGridView?

For example, I have a form that is bound to the VendorTypes DataTable via a BindingSource (bsVendorTypes). When the bsVendorTypes.PositionChanged method is called, I want the Vendors DataGridView (dgvVendors) to automatically show the Vendors that are related to the current VendorType.



In the above example, I am populating a temporary DataTable (dtTemp) via a temporary DataAdapter (dad) and then binding Vendors DataGridView (dgvVendors) to dtTemp DataTable.

strSQL = "SELECT Vendors.* " &
    "FROM Vendors INNER JOIN VendorMember ON Vendors.RecID = VendorMember.VendorID " &
    "WHERE (VendorMember.VendorTypeID = " & lngVendorID & ") " &
    "ORDER BY Vendors.VendorName, Vendors.VendorCity, Vendors.VendorState"

If con.State <> ConnectionState.Open Then con.Open()
Using dad As New OleDb.OleDbDataAdapter(strSQL, con)
    dad.Fill(dtTemp)
End Using
con.Close()

dgvVendors.DataSource = dtTemp

lblGridInfo.Text = "Vendors (" & dgvVendors.Rows.Count & CStr(IIf(dgvVendors.Rows.Count = 1, " item", " items")) & "):"

I want to use the many-to-many Relation instead of reading the data from the underlying MS Access database via a DataAdapter.