Results 1 to 6 of 6
  1. #1
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496

    creating a new relationship, syntax error

    i have the below code to REPLACE a relationship that was incorrectly defined


    however, it is resulting in the error: 424 'object required'
    (don't see that any required detail is missing...)

    ... Click image for larger version. 

Name:	2022-08-01_22-20-30.jpg 
Views:	24 
Size:	19.6 KB 
ID:	48403

    it may be late;
    i may be not paying attention to the obvious;
    it may be an Access / VBA quirk;
    NTL, any observations will be greatly appreciated in advance.

    m.


    Code:
      
    Function ReplaceRelationship(ByVal vFilePath As Variant, _
        strRelationName As String, _
        strTblName As String, _
        strForeignTblName As String, _
        strFld As String, _
        strForeignFld As String, _
        Optional ByVal intAttrib As Integer = -1) As Boolean
    
        Dim rel As DAO.Relation
        Dim fld As DAO.Field
        Dim relatingField As DAO.Field
        Dim relationUniqueName As String
    
        On Error Resume Next
    
        Set gsDbs = OpenDatabase(vFilePath)
            
        'delete the existing relationship
        gsDbs.Relations.Delete strRelationName
        
        'there may be redundant / parallel relationships (?) that could be in conflict with the new definition;
        'they need to also be deleted
        For Each rel In gsDbs.Relations
            With rel
                If .Table = strTblName And .ForeignTable = strForeignTbl Then 'check for left or right join relationships
                    For Each fld In .Fields
                        If fld.Name = strFld Then
                            gsDbs.Relations.Delete .Name
                        End If
                    Next
                End If
            End With
        Next
    
    'Create a new relation.
        
        'Specify relationship name:
        relationUniqueName = strTblName + "_" + strFld + _
                             "__" + strForeignTblName + "_" + strForeignFld
        
        'Specify attributes for cascading updates and deletes.
        If intAttrib = -1 Then
            intAttrib = dbRelationUpdateCascade + dbRelationDeleteCascade
        End If
        
        Set newRelation = gsDbs.CreateRelation(relationUniqueName, _
                                strTblName, strForeignTblName, intAttrib)
    
        'The field from the primary table.
        Set relatingField = newRelation.CreateField(strFld)
        'Matching field from the related table.
        relatingField.ForeignName = strForeignFld
        'Add the field to the relation's Fields collection.
        newRelation.Fields.Append relatingField
        
        'Add the relation to the database.
        db.Relations.Append newRelation
        
        If Err = 0 Then GoTo ExitRoutine
        
    ErrHandler:
        Debug.Print "Error handler enabled"
        MsgBox "Error handler enabled" _
               & vbCrLf _
               & vbCrLf & "module: ReplaceRelationships" _
               & vbCrLf & "an error occured in line: " & Erl & vbCrLf & "error no. " & Err.Number & ": " & Err.Description _
               & vbCrLf & "Primary Table: " & strTblName _
               & vbCrLf & "Foreign Table: " & strForeignTblName _
               & vbCrLf & "common field (pri:foreign) : " & strFld & " : " & strForeignFld
               
    ExitRoutine:
        ReplaceRelationship = True
        
        'Clean up
        Set fld = Nothing
        Set rel = Nothing
        Set gsDbs = Nothing
    '    Exit Function
    
    End Function
    Last edited by markjkubicki; 08-01-2022 at 09:56 PM. Reason: typo

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Comment out the 'On Error Resume Next' line and the code should stop with highlight at offending line.

  3. #3
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    WOW that helped enourmously

    db.Relations.Append newRelation
    ought to have been:
    gsDbs.Relations.Append newRelation


    HOWEVER, i still have an error (a different one)

    i've got the code defining integrity / update / delete attributes wrong (all should be selected)
    (to me) it looks right in code,
    but in execution... all i get is integrity (not update nor delete)

    again, any thoughts will be appreciated

    Code:
    'Create a new relation.
        
        'Specify relationship name:
        relationUniqueName = strTblName + "_" + strFld + _
                             "__" + strForeignTblName + "_" + strForeignFld
        
        'Specify attributes for cascading updates and deletes.
        If intAttrib = -1 Then
            intAttrib = dbRelationUpdateCascade + dbRelationDeleteCascade
        End If
        
        Set newRelation = gsDbs.CreateRelation(relationUniqueName, _
                                strTblName, strForeignTblName, intAttrib)
    
        'The field from the primary table.
        Set relatingField = newRelation.CreateField(strFld)
        'Matching field from the related table.
        relatingField.ForeignName = strForeignFld
        'Add the field to the relation's Fields collection.
        newRelation.Fields.Append relatingField
        
        'Add the relation to the database.
        gsDbs.Relations.Append newRelation
    Last edited by markjkubicki; 08-02-2022 at 06:04 AM. Reason: clarity

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    This may help. Here's code that works for me that I created 2 or 3 years ago.

    Code:
    '---------------------------------------------------------------------------------------
    ' Method : fcnCreateRelation
    ' Author : davegri
    ' Date   : 2/27/2021
    ' Purpose: Create 1 to many relationship between 2 tables
    '   PrimaryTname is Parent table name
    '   ForeignTnams is Child table name
    '   PrimaryFname is Primary key Field name in Parent table
    '   ForeignFname is Foreign key Field name in Child table pointing back to parent
    '
    '---------------------------------------------------------------------------------------------
    Public Function fcnCreateRelation( _
        PrimaryTname As String, _
        PrimaryFname As String, _
        ForeignTname As String, _
        ForeignFname As String) As Boolean
        On Error GoTo fcnCreateRelation_Error
        Dim db As DAO.Database
        Dim NewRelation As DAO.Relation
        Dim RelatingField As DAO.Field
        Dim RelationName As String
        fcnCreateRelation = False
        RelationName = PrimaryFname & "$$" & ForeignFname
        Set db = CurrentDb()
        Set NewRelation = db.CreateRelation(RelationName, PrimaryTname, ForeignTname, dbRelationDeleteCascade)
        Set RelatingField = NewRelation.CreateField(PrimaryFname)
        RelatingField.ForeignName = ForeignFname
        NewRelation.Fields.Append RelatingField
        db.Relations.Append NewRelation
        fcnCreateRelation = True
    fcnCreateRelation_EXIT:
        On Error Resume Next
        Set db = Nothing
        Set NewRelation = Nothing
        Set RelatingField = Nothing
        Exit Function
    fcnCreateRelation_Error:
        Select Case Err
            Case 3012       'already exists, ignore error
            Case Else
                Call fcnErrorMsg(Err.Number, Err.Description, " fcnCreateRelation of VBA Document Form_frmConversionDataset", , True)
        End Select
        Resume fcnCreateRelation_EXIT
    End Function

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Looking at your code, intAttrib should be Long integer.

    If intAttrib is being passed to the function as anything other than -1 it would explain why you get no attributes.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    There were errors with undeclared variables in your code. I strongly suggest that all code modules begin with these two lines:

    Code:
    Option Compare Database
    Option Explicit
    This is your code modified. I have included commented changes, but all might not be commented. I assume the code is contained in a database that is not the database being modified.

    Code:
    Option Compare Database
    Option Explicit
    
    
    Function ReplaceRelationship(ByVal vFilePath As Variant, _
        strRelationName As String, _
        strTblName As String, _
        strForeignTblName As String, _
        strFldPK As String, _
        strForeignFld As String, _
        Optional ByVal intAttrib As Integer = -1) As Boolean
    
    
        Dim rel As DAO.Relation
        Dim newrelation As DAO.Relation
        Dim fld As DAO.Field
        Dim relatingField As DAO.Field
        Dim relationUniqueName As String
        Dim gsdbs As DAO.Database       'this is necessary!
    
    
        'On Error Resume Next
    
    
        Set gsdbs = OpenDatabase(vFilePath)
            
        'delete the existing relationship
        'gsdbs.Relations.Delete strRelationName   'How do you know the name?  Not needed anyway.
        
        'there may be redundant / parallel relationships (?) that could be in conflict with the new definition;
        'they need to also be deleted
        For Each rel In gsdbs.Relations
                If rel.Table = strTblName And rel.ForeignTable = strForeignTblName Then 'check for left or right join relationships
                    For Each fld In rel.Fields
                        If fld.Name = strFldPK Then
                            gsdbs.Relations.Delete rel.Name
                            Exit For    'no use to iterate more, can only be one match, avoids an error
                        End If
                    Next
                End If
        Next
    'Stop
    'Create a new relation.
        
        'Specify relationship name:
        relationUniqueName = strTblName + "_" + strFldPK + _
                             "__" + strForeignTblName + "_" + strForeignFld
        
        'Specify attributes for cascading updates and deletes.
        If intAttrib = -1 Then
            intAttrib = dbRelationUpdateCascade + dbRelationDeleteCascade
        End If
        
        Set newrelation = gsdbs.CreateRelation(relationUniqueName, _
                                strTblName, strForeignTblName, intAttrib)   'fixed variable name
    
    
        'The field from the primary table.
        Set relatingField = newrelation.CreateField(strFldPK)
        'Matching field from the related table.
        relatingField.ForeignName = strForeignFld
        'Add the field to the relation's Fields collection.
        newrelation.Fields.Append relatingField
        
        'Add the relation to the database.
        gsdbs.Relations.Append newrelation
        gsdbs.Close
        If Err = 0 Then GoTo ExitRoutine
        
    ErrHandler:
        Debug.Print "Error handler enabled"
        MsgBox "Error handler enabled" _
               & vbCrLf _
               & vbCrLf & "module: ReplaceRelationships" _
               & vbCrLf & "an error occured in line: " & Erl & vbCrLf & "error no. " & Err.Number & ": " & Err.Description _
               & vbCrLf & "Primary Table: " & strTblName _
               & vbCrLf & "Foreign Table: " & strForeignTblName _
               & vbCrLf & "common field (pri:foreign) : " & strFldPK & " : " & strForeignFld
               
    ExitRoutine:
        ReplaceRelationship = True
        
        'Clean up
        Set fld = Nothing
        Set rel = Nothing
        Set gsdbs = Nothing
    '    Exit Function
    
    
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating Relationship
    By MdHaziq in forum Access
    Replies: 2
    Last Post: 01-19-2018, 08:00 AM
  2. Creating relationship, need help.
    By Calvinle in forum Access
    Replies: 3
    Last Post: 12-03-2014, 01:17 PM
  3. Creating a relationship between two tables
    By amyhannah in forum Database Design
    Replies: 2
    Last Post: 12-16-2013, 07:44 PM
  4. problems creating one to one relationship
    By alexthefourth in forum Access
    Replies: 5
    Last Post: 11-11-2013, 06:16 PM
  5. Replies: 5
    Last Post: 07-26-2013, 11:12 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums