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

    Unanswered: URGENT!! Create table relationships during VBA runtime

    Hi there folks,

    As the title suggests, this is a really urgent issue for an assignment of mine thats due in a couple of weeks!

    I need some code, or an object that will let me create a relationship between two tables (one created at run-time via SQL), and another already implemented in the system, all during run-time of the system.

    Again this is a real important issue, someone tried to help me earlier posting the following code, which unfortunately doesn't work, as I suspect it depends on external module's etc. Feel free to look at the code, adjust so that it's self supporting!

    '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
    Else
    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


    Many many thanks to anyone that can help me in this matter. Links to other pages, modules, code, anything would be greatly appreciated!!!

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Lookup "TableDef" in the Access help file.

    Choose the topic "About working with data and database objects using Visual Basic code" as a good starting point. The help file actually has simple code to create a table.

    Then, lookup "Connect Property" and the topic "Connect Property (DAO)"

    Have fun

Posting Permissions

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