Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004

    Unanswered: How do I create a relationship between tables during run-time?

    Hi folks,

    As the title suggests, I need to know how to create a relationship between two tables during run-time in Access VBA. It might be worth noting that one the tables to be linked will be created during run-time (however, the table name and attributes will be known at run-time). I'm a bit of a novice on VB and SQL so go easy on that kinda stuff ok!!

    Many thanks in advance.

  2. #2
    Join Date
    Oct 2003
    Here is some code I sometimes use to create relationships. I store the column data in a table called TCAddField. I have included my comments of the parameter values which were defined in online help. If you need to make a relationship between more than 2 columns, then you will have to modify the code. rs!NewName is just a column in the table that I am now using for multiple purposes. It originally was created for holding the new name of a column that is to be renamed.

    Dim rel As Relation
    'For relationships, use the TCAddField columns as below:
    ' Code = unique relationship name
    ' dbName = the name of the database as per usual
    ' TableName = name of the primary table
    ' FieldName = name of the field in the primary table
    ' ForeignTable = name of the foreign table
    ' ForeignField = name of the field in the foreign table
    ' RelAttr = number for relationship attributes
    Set dbs = DBEngine.Workspaces(0).OpenDatabase(DataDir & rs!DbName)
    'Set relNew = dbs.CreateRelation(rs!Code, _
    rs!tablename, rs!NewName, dbRelationUpdateCascade)
    'dbRelationUnique 1 The relationship is one-to-one.
    'dbRelationDontEnforce 2 The relationship isn't enforced (no referential integrity).
    'dbRelationInherited 4 The relationship exists in a non-current database that contains the two linked tables.
    'dbRelationUpdateCascade 256 Updates will cascade.
    'dbRelationDeleteCascade 4096 Deletions will cascade.

    ' Find existing EmployeesOrders relation.
    On Error Resume Next
    For Each rel In dbs.Relations
    If rel.Table = rs!tablename And rel.ForeignTable = rs!ForeignTable Then
    'Error 3262 - Couldn't lock table...; currently in use by user 'Admin' on machine...
    dbs.Relations.Delete rel.Name
    End If
    Next rel

    Set rel = dbs.CreateRelation(rs!Code)
    rel.Table = rs!tablename ' Set Table property.
    rel.ForeignTable = rs!ForeignTable ' Set ForeignTable property.
    ' Set Relation object attributes to enforce referential integrity.
    If rs!RelAttr = 4352 Then
    rel.Attributes = dbRelationDeleteCascade And dbRelationUpdateCascade
    rel.Attributes = rs!RelAttr
    End If
    Set Fld = rel.CreateField(rs!FieldName) ' Create field in Fields collection of Relation.
    Fld.ForeignName = rs!ForeignField ' Provide name of foreign key field.
    rel.Fields.Append Fld ' Append field to Relation and Relation to database.
    dbs.Relations.Append rel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts