I've been asked several times recently how I am able to pragmatically create my table relationships in my Microsoft Access database setup utilities. As it turns out, the dao.Database object has a function built into it for doing just this but can be tricky to get it to actually work.
What you'll need to get this to work is an active dao.Database object, a primary table and primary key name, a foreign table and foreign key name, and a unique name for the relationship.
Sample code is provided below:
''' <summary> ''' Creates a relationship between two tables ''' </summary> ''' <param name="relName">Unique name for table relationship</param> ''' <param name="fkFieldName">Foreign Key Field Name</param> ''' <param name="pkFieldName">Primary Key Field Name</param> ''' <param name="TypeTableName">Top most table name</param> ''' <param name="InstTableName">Child table name</param> ''' <returns></returns> ''' <remarks></remarks> Public Function CreateRelationship(ByVal relName As String, _ ByVal fkFieldName As String, _ ByVal pkFieldName As String, _ ByVal TypeTableName As String, _ ByVal InstTableName As String) As Boolean Dim rel As dao.Relation Dim fld As dao.Field Try rel = m_DaoDB.CreateRelation(relName, TypeTableName, InstTableName, dao.RelationAttributeEnum.dbRelationUpdateCascade) 'GUID 'The field from the primary table. fld = rel.CreateField(pkFieldName) 'xxGUID 'Matching field from the related table. fld.ForeignName = fkFieldName 'primaryFieldName 'Add the field to the relation's Fields collection. rel.Fields.Append(fld) 'Add the relation to the database. m_DaoDB.Relations.Append(rel) Return True Catch ex As Exception Return False End Try End Function