How to remove relationship?

[FP1]

Hello,
I need to repeatedly copy an existing access/jet database and delete
some tables from it using ADODB and ADOX. (I'm linking to backend
databases) It has many relationships defined, which I need to sever (it
just wasn't working out) .


When I try to delete from the Keys collection, I get:

cannot delete this index or table. it is either the primary index or it
used in a relationship.

when I try to delete from Indexes collection, I get:

cannot delete this index or table. it is either the primary index or it
used in a relationship.

When I try to delete the table I get:

cannot delete this index or table. it is either the primary index or it
used in a relationship.

(I tried sql drop and alter, same results.)

I know SOMEBODY must have done this before! What's the secret?

code below. Thank you kindly for you help!

-----

Set tbl = cat.Tables(rs("table_name").Value)
'delete the keys first
Dim keysx As ADOX.keys

Set keysx = cat.Tables(tbl.name).keys
For kx = 0 To keysx.Count - 1
Debug.Print "dropping key " & keysx(kx).name
'cat.Tables(tbl.name).keys.Delete keysx(kx).name
Dim drop
cat.Tables(tbl.name).keys.Delete keysx(kx).name

'drop = "alter table " & tbl.name & " Drop constraint " & keysx
(kx).name
indb.Execute drop
Next

'delete the indexes
Dim indx As ADOX.Indexes
Set indx = cat.Tables(tbl.name).Indexes
For ix = 0 To indx.Count - 1
Debug.Print "dropping index " & indx(ix).name
cat.Tables(tbl.name).Indexes.Delete indx(ix).name
Next

cat.Tables.Delete (rs.Fields.Item("table_name"))
Debug.Print "dropping table " & rs("table_name")
GoTo next_tbl
tbl_error:
Debug.Print "Error deleting table " & Err.Description
next_tbl:
rs.MoveNext
Wend

 

[FP1]

My guess is that you have to follow a certain pattern to delete the
relationships. You can't go abritrary go on deleting relations. You
might have to think bottom up in a hierachical way. For instance if
you have clients and orders you'd first have to delete the orders to
be able to delete the clients.

The thought occurred to me. What I might need to do is a recursive routine
to find the lowest level and delete from the bottom up. Can anybody
confirm that this is the problem or is it somethingsimpler?

 

[Guest]

My guess is that you have to follow a certain pattern to delete the
relationships. You can't go abritrary go on deleting relations. You might
have to think bottom up in a hierachical way. For instance if you have
clients and orders you'd first have to delete the orders to be able to delete
the clients.

 

[Bob Quintal]

Hello,
I need to repeatedly copy an existing access/jet database and
delete
some tables from it using ADODB and ADOX. (I'm linking to backend
databases) It has many relationships defined, which I need to
sever (it just wasn't working out) .


When I try to delete from the Keys collection, I get:

cannot delete this index or table. it is either the primary index
or it used in a relationship.

when I try to delete from Indexes collection, I get:

cannot delete this index or table. it is either the primary index
or it used in a relationship.

When I try to delete the table I get:

cannot delete this index or table. it is either the primary index
or it used in a relationship.

(I tried sql drop and alter, same results.)

I know SOMEBODY must have done this before! What's the secret?

you have to go through the relations collection first, deleting any
relations that contain the table you wish to delete

Dim rels As relations
Dim rel As Relation
Set rels = CurrentDb.relations
For Each rel In rels
If rel.Table = mytable Or rel.ForeignTable = mytable Then
rels.Delete rel.Name
End If
Next rel

End Sub

Q

code below. Thank you kindly for you help!

-----

Set tbl = cat.Tables(rs("table_name").Value)
'delete the keys first
Dim keysx As ADOX.keys

Set keysx = cat.Tables(tbl.name).keys
For kx = 0 To keysx.Count - 1
Debug.Print "dropping key " & keysx(kx).name
'cat.Tables(tbl.name).keys.Delete keysx(kx).name
Dim drop
cat.Tables(tbl.name).keys.Delete keysx(kx).name

'drop = "alter table " & tbl.name & " Drop constraint " &
keysx
(kx).name
indb.Execute drop
Next

'delete the indexes
Dim indx As ADOX.Indexes
Set indx = cat.Tables(tbl.name).Indexes
For ix = 0 To indx.Count - 1
Debug.Print "dropping index " & indx(ix).name
cat.Tables(tbl.name).Indexes.Delete indx(ix).name
Next

cat.Tables.Delete (rs.Fields.Item("table_name"))
Debug.Print "dropping table " & rs("table_name")
GoTo next_tbl
tbl_error:
Debug.Print "Error deleting table " & Err.Description
next_tbl:
rs.MoveNext
Wend

 

[FP1]

you have to go through the relations collection first, deleting any
relations that contain the table you wish to delete

Dim rels As relations
Dim rel As Relation
Set rels = CurrentDb.relations
For Each rel In rels
If rel.Table = mytable Or rel.ForeignTable = mytable Then
rels.Delete rel.Name
End If
Next rel

End Sub

Q

Thanks for your input!

I figured out to delete the "keys" indexes from adox.catalog. The
problem was the database isn't local, so currentdb doesn't work and I
kept getting other errors trying to link through DAO.