Programmatically Create a Relationship in Microsoft Access

Here is another of several Microsoft Access related posts I'll be sharing for handling a database interaction between 32 bit Microsoft Acces and a 64 bit session of Autodesk Revit 2011.

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