Relationship Names

[accessNator]

I have written some VB code when I create tables, I establish relationships to it. I can see the relationships when I view them in the Relationships view.

But what I need to know when I manually created relationships with other tables not using VB code. What is the name of the relationship name between those tables? In case I need to delete the tables using vb code, I need to know the Relationship name between the tables I established manually.

Also when viewing the Relationship diagram, even with the relationships I created via vb code, I cant find the name I created previously using vb code.

Thoughts?

 

[wiklendt]

i do not know of any situation where you would need to create then delete relationships in a database... not if it's set up correctly.

would you care to explain WHY you are doing this? perhaps we can come to a better/easier solution?

 

[accessNator]

i do not know of any situation where you would need to create then delete relationships in a database... not if it's set up correctly.

would you care to explain WHY you are doing this? perhaps we can come to a better/easier solution?

Basically, I have a working access application but if I need to modify my code in a development access application that uses the same tables from another database, I cant just import the tables. It tells me that in a nutshell to import the table from another database, I have to delete the relationship in my development access application.

 

[wiklendt]

ok. i take it your DB is not split?

you could simply link to a different database on open. i do this for a developmental DB i'm working on at the moment. in my startup form, i have code to link my FE to my live BE (on the server), but if the live BE is not available (e.g., on my laptop with no server access) then it links instead to my local "dummy" backend. this of course, can be any backend you like, so long as you specify in the code.

this is the code i use on the form's "on open" event, see if it will help you:

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

    ' ------------------------------------------------------------------
    ' linking code adapted from namliam's code on AWF
    ' http://www.access-programmers.co.uk/forums/showthread.php?t=180962
    ' ------------------------------------------------------------------
    Dim tbl As TableDef
    Dim x As Long, MaxX As Long
    Dim tblDB As String

    Me.Visible = False
        
    tblDB = myFolder & "\ORDERS_ReferenceLabs_be.mdb"
    
    If Dir(tblDB) = "" Then ' local BE not found, use server BE.
        tblDB = "\\wm-icpmr\Data2\SHARED\Cidmls\Molecular Biology\Orders\ORDERS_ReferenceLabs_be.mdb"
    End If
    
    MaxX = 1 ' first count all attached tables
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then MaxX = MaxX + 1
    Next tbl
    x = 1 ' Now update them
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then
'            tblDB = myFolder & Mid(tbl.Connect, InStr(1, tbl.Connect, "\"))
            If tbl.Connect <> ";Database=" & tblDB Then
                Me.Visible = True
                Me.Repaint
                tbl.Connect = ";Database=" & tblDB
                tbl.RefreshLink
            End If
            x = x + 1
        End If
        Me.Fill.Width = x / MaxX * Me.FillTo.Width
    Next tbl
    If Me.Visible Then
        Me.lblWait.Caption = "Done... Opening login form."
        Me.Repaint
        MaxX = Timer + 2
        Do While Timer <= MaxX
        Loop
    End If
    
    DoCmd.OpenForm "frmLogin"
    DoCmd.Close acForm, Me.Name

Exit_Form_Open:
    Exit Sub

Err_Form_Open:

    Msg = "Error # " & Str(Err.Number) & Chr(13) & " (" & Err.Description & ")"
    Msg = Msg & Chr(13) & "in Form_frmWAIT | Form_Open"
    MsgBox Msg, vbOKOnly, "RLS Order Records", Err.HelpFile, Err.HelpContext
    Resume Exit_Form_Open
    
End Sub
Function myFolder()
On Error GoTo Err_myFolder

    myFolder = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))

Exit_myFolder:
    Exit Function

Err_myFolder:

    Msg = "Error # " & Str(Err.Number) & Chr(13) & " (" & Err.Description & ")"
    Msg = Msg & Chr(13) & "in Form_frmWAIT | myFolder"
    MsgBox Msg, vbOKOnly, "RLS Order Records", Err.HelpFile, Err.HelpContext
    Resume Exit_myFolder
End Function

 

[accessNator]

ok. i take it your DB is not split?

you could simply link to a different database on open. i do this for a developmental DB i'm working on at the moment. in my startup form, i have code to link my FE to my live BE (on the server), but if the live BE is not available (e.g., on my laptop with no server access) then it links instead to my local "dummy" backend. this of course, can be any backend you like, so long as you specify in the code.

this is the code i use on the form's "on open" event, see if it will help you:

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

    ' ------------------------------------------------------------------
    ' linking code adapted from namliam's code on AWF
    ' http://www.access-programmers.co.uk/forums/showthread.php?t=180962
    ' ------------------------------------------------------------------
    Dim tbl As TableDef
    Dim x As Long, MaxX As Long
    Dim tblDB As String

    Me.Visible = False
        
    tblDB = myFolder & "\ORDERS_ReferenceLabs_be.mdb"
    
    If Dir(tblDB) = "" Then ' local BE not found, use server BE.
        tblDB = "\\wm-icpmr\Data2\SHARED\Cidmls\Molecular Biology\Orders\ORDERS_ReferenceLabs_be.mdb"
    End If
    
    MaxX = 1 ' first count all attached tables
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then MaxX = MaxX + 1
    Next tbl
    x = 1 ' Now update them
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then
'            tblDB = myFolder & Mid(tbl.Connect, InStr(1, tbl.Connect, "\"))
            If tbl.Connect <> ";Database=" & tblDB Then
                Me.Visible = True
                Me.Repaint
                tbl.Connect = ";Database=" & tblDB
                tbl.RefreshLink
            End If
            x = x + 1
        End If
        Me.Fill.Width = x / MaxX * Me.FillTo.Width
    Next tbl
    If Me.Visible Then
        Me.lblWait.Caption = "Done... Opening login form."
        Me.Repaint
        MaxX = Timer + 2
        Do While Timer <= MaxX
        Loop
    End If
    
    DoCmd.OpenForm "frmLogin"
    DoCmd.Close acForm, Me.Name

Exit_Form_Open:
    Exit Sub

Err_Form_Open:

    Msg = "Error # " & Str(Err.Number) & Chr(13) & " (" & Err.Description & ")"
    Msg = Msg & Chr(13) & "in Form_frmWAIT | Form_Open"
    MsgBox Msg, vbOKOnly, "RLS Order Records", Err.HelpFile, Err.HelpContext
    Resume Exit_Form_Open
    
End Sub
Function myFolder()
On Error GoTo Err_myFolder

    myFolder = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))

Exit_myFolder:
    Exit Function

Err_myFolder:

    Msg = "Error # " & Str(Err.Number) & Chr(13) & " (" & Err.Description & ")"
    Msg = Msg & Chr(13) & "in Form_frmWAIT | myFolder"
    MsgBox Msg, vbOKOnly, "RLS Order Records", Err.HelpFile, Err.HelpContext
    Resume Exit_myFolder
End Function

You are correct. The DB is not split...possible in the near future I will do so. I appreciate the code you gave. I will look it over and modify it on my end. I appreciate the solution. This is great.

Thanks again!

 

[wiklendt]

You are correct. The DB is not split...possible in the near future I will do so. I appreciate the code you gave. I will look it over and modify it on my end. I appreciate the solution. This is great.

Thanks again!

yeah, that code was a life saver.

someone else on the forums has code to change the linked tables on the "on click" of a button on a form. i think it might have been DCrake, but i could be wrong? i suppose you could adapt the above code for a button click anyhow...