how to remove a relationship in the back end? (RLi)

[Guest]

Hello all, i have a question about vba and changing relationships between
tables in a back end database.
I defined a one-to-many relationship between two tables in the back end (i
think i should not have done that :-( ) and the back end is distributed to
clients.

Now i want to remove this relationship because it prevents me of entering
records without that key to the other table. I want to do that with vba in
the front end.
I know how to add / change table fields in the back end (with
dbBackend.Execute SQLstr) but can anyone tell me how to remove such a
relationship? (at the time i did build the relationship with the graphical
relationship editor of access2003, so i don't know how to refer to it....it
does not seem to have a name?)

Can anyone help me?

 

[Bob Quintal]

Hello all, i have a question about vba and changing relationships
between tables in a back end database.
I defined a one-to-many relationship between two tables in the
back end (i think i should not have done that :-( ) and the back
end is distributed to clients.

Now i want to remove this relationship because it prevents me of
entering records without that key to the other table. I want to do
that with vba in the front end.
I know how to add / change table fields in the back end (with
dbBackend.Execute SQLstr) but can anyone tell me how to remove
such a relationship? (at the time i did build the relationship
with the graphical relationship editor of access2003, so i don't
know how to refer to it....it does not seem to have a name?)

Can anyone help me?

You will need to open the back end in a workspace, then use the
relations collection to find the applicable one, and delete it.

Dim wrkjet as workspace
dim bend as databvase
Dim rels As relations
Dim rel As Relation
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
set bend = wrkjet.opendatabase("c:\backend\whatever.mdb")
Set rels = bend.relations
For Each rel In rels
If (rel.Table = mytable And rel.ForeignTable = mytable2) _
OR (rel.Table = mytable2 And rel.ForeignTable = mytable) Then
rels.Delete rel.Name
End If
Next rel

End Sub