How do I access (no pun intended) t 2

[SBendBuckeye]

How do I access (no pun intended) the inter table relationships within code? Are they part of the tabledef collection? I need to write a quick and dirty procedure to hash alphabetic data because of privacy concerns and I want to ensure that I don't accidently hash the many side of a one to many relationship thereby trashing my data in the process.

If possible, a little bit of code examples would be helpful as I have never delved into this part of Access programatically before.

Thanks in advance for any help!

 

[JoanneM]

Sorry to be a dummy but what do you mean by hash exactly?

Msdn has this example for creating a relationship - I don't know if this is what you want
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

regards

jo

 

[BlackKnight]

Hi,
No, relations have their own collection and are a part of the database since they apply to tables. Here's an example from the NWind DB.

Dim dbsNorthwind As Database
Dim rstProducts As Recordset
Dim prpLoop As Property
Dim fldLoop As Field
Dim errLoop As Error

Set dbsNorthwind = OpenDatabase("Northwind.mdb&quot
Set rstProducts = dbsNorthwind.OpenRecordset("Products&quot

' Print a report showing all the different parts of
' the relation and where each part is stored.
With dbsNorthwind.Relations!CategoriesProducts
Debug.Print "Properties of " & .Name & " Relation"
Debug.Print " Table = " & .Table
Debug.Print " ForeignTable = " & .ForeignTable
Debug.Print "Fields of " & .Name & " Relation"
With .Fields!CategoryID
Debug.Print " " & .Name
Debug.Print " Name = " & .Name
Debug.Print " ForeignName = " & .ForeignName
End With
End With

Have a good one!
BK

 

[SBendBuckeye]

Thanks to both of you! I understand relationships and have defined them many times in the relationship window. Now I need to access them programatically for the first time.

Due to 3rd party considerations, I need to hash the data so I can send it out for testing. In other words 310 S Main might come back as 204 E Bend. That way I make the data unrecognizable without actually changing for testing purposes.

I want to spin through the relationships to make sure that I don't hash the many side of any relationships as that would break the relationship and trash my data. I understand that if I hash the one side of the relationship then cascading updates will take care of the many side automatically.

Have a great day!