Removing a relationship

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to add the ability to import a lot of data from another database.
I've created a form and allow the user to select the database to import from.
Now since the imported data is supposed to replace the existing data, I need
to remove all the existing data. That I can do, except for the fact that
there are 2 tables whose data should not be removed, but which have
relationships to the other data that will be removed. So I need to break
those relationships before removing the data, and then recreate them after
the data has been imported.

How can that be done in vba? I've been looking at ADO and ADOX objects and
methods, but haven't figured out exactly what needs to be done to remove and
then recreate a relationship. Can someone point me to a source that explains
how to do this? Thanks!
 
Since you want the related data to remain anyway, why not just remove the
"allow cascade delete related records" option (and perhaps the "enforce
referential integrity" option) on the relationship? I'm not sure that you
need -or want- to remove and restore the relationship.
 
Bagger said:
I need to add the ability to import a lot of data from another database.
I've created a form and allow the user to select the database to import from.
Now since the imported data is supposed to replace the existing data, I need
to remove all the existing data. That I can do, except for the fact that
there are 2 tables whose data should not be removed, but which have
relationships to the other data that will be removed. So I need to break
those relationships before removing the data, and then recreate them after
the data has been imported.

How can that be done in vba? I've been looking at ADO and ADOX objects and
methods, but haven't figured out exactly what needs to be done to remove and
then recreate a relationship. Can someone point me to a source that explains
how to do this? Thanks!
Click to expand...


Here's some DAO code I've used to deal with this kind of
situation for a single key table with no multi-field
relations:

Dim rltn As DAO.Relation, rltnfld As DAO.Field
Dim Rname(20), Rattributes(20), Rtbl(20), Rfld(20), _
Rforeigntbl(20), Rforeignfld(20)
Dim j As Integer, k As Integer

Set SrvDb = DBEngine(0).OpenDatabase(ServerPath)
j = 0
For k = SrvDb.Relations.Count - 1 To 0 Step -1
With SrvDb.Relations(k)
If .Table = "Quotes" Or .ForeignTable = "Quotes"
Then
Rname(j) = .Name
Rattributes(j) = .Attributes
Rtbl(j) = .Table
Rfld(j) = .Fields(0).Name
Rforeigntbl(j) = .ForeignTable
Rforeignfld(j) = .Fields(0).ForeignName
SrvDb.Relations.Delete .Name
j = j + 1
End If
End With
Next k

' import code goes here

For k = 0 To j - 1
Set rltn = SrvDb.CreateRelation(Rname(k), Rtbl(k), _
Rforeigntbl(k), Rattributes(k))
Set rltnfld = rltn.CreateField(Rfld(k))
rltnfld.ForeignName = Rforeignfld(k)
rltn.Fields.Append rltnfld
SrvDb.Relations.Append rltn
Next k
 
Back
Top