By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

Deleting Relationships...

P: n/a
I'm using this code to delete all relationships in my mdb file

iFlag = 1
Do While iFlag <> 0
iFlag = 0
For Each rel In db.Relations
db.Relations.Delete rel.Name
iFlag = 1
Next rel
Loop

Just looping once only deletes one relation between two tables. I have to
repeat the loop to delete all relations between all tables. Is there some
outer container or collection that I can loop through? Also, is there an
SQL statement like "DROP RELATION..."?

Thanks.

Matthew Wells
MW****@NumberCruncher.com

Nov 12 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
Yick!

You cannot use a "For Each" if you are removing members of the collection.
Try counting backwards to do this.

For iRel = db.Relations.Count - 1 to 0 Step -1
db.Relations.Delete db.Relations(i).Name
Next iRel
--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.
"Matthew Wells" <MW****@NumberCruncher.com> wrote in message
news:9f******************@bignews6.bellsouth.net.. .
I'm using this code to delete all relationships in my mdb file

iFlag = 1
Do While iFlag <> 0
iFlag = 0
For Each rel In db.Relations
db.Relations.Delete rel.Name
iFlag = 1
Next rel
Loop

Just looping once only deletes one relation between two tables. I have to
repeat the loop to delete all relations between all tables. Is there some
outer container or collection that I can loop through? Also, is there an
SQL statement like "DROP RELATION..."?

Thanks.

Matthew Wells
MW****@NumberCruncher.com

Nov 12 '05 #2

P: n/a
"Matthew Wells" <MW****@NumberCruncher.com> wrote in
news:9f******************@bignews6.bellsouth.net:
I'm using this code to delete all relationships in my mdb file

iFlag = 1
Do While iFlag <> 0
iFlag = 0
For Each rel In db.Relations
db.Relations.Delete rel.Name
iFlag = 1
Next rel
Loop

Just looping once only deletes one relation between two tables. I have
to repeat the loop to delete all relations between all tables. Is there
some outer container or collection that I can loop through? Also, is
there an SQL statement like "DROP RELATION..."?


If I remember correctly, for each constructs use a counter and get confused
during deletes. That is, if there are two relationships and we delete the
first within a for each, the second becomes #1, and the for each construct
searches for relationship #2, does not find it and quits. If there are more
than two, only the odd numbered relationships are deleted.

Another way of deleting all relationships may be:

Sub RemoveRelations()
Dim dbs As Database
Set dbs = CurrentDb()
With dbs
Do While .Relations.Count > 0
.Relations.Delete .Relations(.Relations.Count - 1).Name
Loop
.Relations.Refresh
End With
Set dbs = Nothing
End Sub

This is très ancient code. I have not used it for years. Please, bear this
in mind if you decide to use it, or even criticize it.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #3

P: n/a
Mi************@Invalid.Com (Lyle Fairfield) wrote in
<Xn*******************@130.133.1.4>:
"Matthew Wells" <MW****@NumberCruncher.com> wrote in
news:9f******************@bignews6.bellsouth.ne t:
I'm using this code to delete all relationships in my mdb file

iFlag = 1
Do While iFlag <> 0
iFlag = 0
For Each rel In db.Relations
db.Relations.Delete rel.Name
iFlag = 1
Next rel
Loop

Just looping once only deletes one relation between two tables.
I have to repeat the loop to delete all relations between all
tables. Is there some outer container or collection that I can
loop through? Also, is there an SQL statement like "DROP
RELATION..."?
If I remember correctly, for each constructs use a counter and get
confused during deletes. That is, if there are two relationships
and we delete the first within a for each, the second becomes #1,
and the for each construct searches for relationship #2, does not
find it and quits. If there are more than two, only the odd
numbered relationships are deleted.

Another way of deleting all relationships may be:

Sub RemoveRelations()
Dim dbs As Database
Set dbs = CurrentDb()
With dbs
Do While .Relations.Count > 0
.Relations.Delete .Relations(.Relations.Count -
1).Name
Loop
.Relations.Refresh
End With
Set dbs = Nothing
End Sub

This is très ancient code. I have not used it for years. Please,
bear this in mind if you decide to use it, or even criticize it.


When deleting items from a collection, I think it's better to use
the MichKa method, which is to use a counter and start from the
collection count with STEP -1:
For iRel = db.Relations.Count - 1 to 0 Step -1
db.Relations.Delete db.Relations(i).Name
Next iRel


The reason I suggest this is that there's less dependence on
looking up values from the collection properties. That is, in
MichKa's method, you lookup the collection count, and then operate
on a collection member by index number. In your example, you look
up the collection count once for the loop and then once for each
item in the loop.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #4

P: n/a
"Matthew Wells" <MW****@NumberCruncher.com> wrote in message
news:9f******************@bignews6.bellsouth.net.. .
I'm using this code to delete all relationships in my mdb file

iFlag = 1
Do While iFlag <> 0
iFlag = 0
For Each rel In db.Relations
db.Relations.Delete rel.Name
iFlag = 1
Next rel
Loop

Just looping once only deletes one relation between two tables. I have to
repeat the loop to delete all relations between all tables. Is there some
outer container or collection that I can loop through? Also, is there an
SQL statement like "DROP RELATION..."?

Maybe something like this::

Sub deleteRelationships()

Dim s As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

s = "select " & Chr$(34) & "alter table " & Chr$(34) _
& " & szObject & " & Chr$(34) & " drop constraint " _
& Chr$(34) & " & szRelationship " _
& " AS a FROM MSysRelationships " _
& "where grbit = 0"

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(s, , dbOpenForwardOnly)

Do While Not rst.EOF
dbs.Execute rst("a").Value, dbFailOnError
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Set dbs = Nothing

End Sub
Nov 12 '05 #5

P: n/a
dX********@bway.net.invalid (David W. Fenton) wrote in
news:94***************************@24.168.128.86:
Mi************@Invalid.Com (Lyle Fairfield) wrote in
<Xn*******************@130.133.1.4>:
"Matthew Wells" <MW****@NumberCruncher.com> wrote in
news:9f******************@bignews6.bellsouth.net :
I'm using this code to delete all relationships in my mdb file

iFlag = 1
Do While iFlag <> 0
iFlag = 0
For Each rel In db.Relations
db.Relations.Delete rel.Name
iFlag = 1
Next rel
Loop

Just looping once only deletes one relation between two tables.
I have to repeat the loop to delete all relations between all
tables. Is there some outer container or collection that I can
loop through? Also, is there an SQL statement like "DROP
RELATION..."?


If I remember correctly, for each constructs use a counter and get
confused during deletes. That is, if there are two relationships
and we delete the first within a for each, the second becomes #1,
and the for each construct searches for relationship #2, does not
find it and quits. If there are more than two, only the odd
numbered relationships are deleted.

Another way of deleting all relationships may be:

Sub RemoveRelations()
Dim dbs As Database
Set dbs = CurrentDb()
With dbs
Do While .Relations.Count > 0
.Relations.Delete .Relations(.Relations.Count -
1).Name
Loop
.Relations.Refresh
End With
Set dbs = Nothing
End Sub

This is très ancient code. I have not used it for years. Please,
bear this in mind if you decide to use it, or even criticize it.


When deleting items from a collection, I think it's better to use
the MichKa method, which is to use a counter and start from the
collection count with STEP -1:
For iRel = db.Relations.Count - 1 to 0 Step -1
db.Relations.Delete db.Relations(i).Name
Next iRel


The reason I suggest this is that there's less dependence on
looking up values from the collection properties. That is, in
MichKa's method, you lookup the collection count, and then operate
on a collection member by index number. In your example, you look
up the collection count once for the loop and then once for each
item in the loop.


What do you think of this one?

Do Until db.Relations.Count = 0
db.Relations.Delete db.Relations(0)
Loop

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #6

P: n/a
"Lyle Fairfield" <Mi************@Invalid.Com> wrote...

What do you think of this one?

Do Until db.Relations.Count = 0
db.Relations.Delete db.Relations(0)
Loop
It does not allow for the fact that the delete could fail, and cause an
infinite loop. The original code I posted:
For iRel = db.Relations.Count - 1 to 0 Step -1
db.Relations.Delete db.Relations(i).Name
Next iRel


is superior for that reason (but fwiw, no other).
--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.

Nov 12 '05 #7

P: n/a
This code will not do what people would reasonably expect it to -- the
relationships will not actually go away.
--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.
"John Winterbottom" <jo***************@hotmail.com> wrote in message
news:bk************@ID-185006.news.uni-berlin.de...
"Matthew Wells" <MW****@NumberCruncher.com> wrote in message
news:9f******************@bignews6.bellsouth.net.. .
I'm using this code to delete all relationships in my mdb file

iFlag = 1
Do While iFlag <> 0
iFlag = 0
For Each rel In db.Relations
db.Relations.Delete rel.Name
iFlag = 1
Next rel
Loop

Just looping once only deletes one relation between two tables. I have to repeat the loop to delete all relations between all tables. Is there some outer container or collection that I can loop through? Also, is there an SQL statement like "DROP RELATION..."?

Maybe something like this::

Sub deleteRelationships()

Dim s As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

s = "select " & Chr$(34) & "alter table " & Chr$(34) _
& " & szObject & " & Chr$(34) & " drop constraint " _
& Chr$(34) & " & szRelationship " _
& " AS a FROM MSysRelationships " _
& "where grbit = 0"

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(s, , dbOpenForwardOnly)

Do While Not rst.EOF
dbs.Execute rst("a").Value, dbFailOnError
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Set dbs = Nothing

End Sub

Nov 12 '05 #8

P: n/a
"Michael \(michka\) Kaplan [MS]" <mi*****@online.microsoft.com> wrote in
news:3f********@news.microsoft.com:
"Lyle Fairfield" <Mi************@Invalid.Com> wrote...

What do you think of this one?

Do Until db.Relations.Count = 0
db.Relations.Delete db.Relations(0)
Loop


It does not allow for the fact that the delete could fail, and cause an
infinite loop. The original code I posted:
For iRel = db.Relations.Count - 1 to 0 Step -1
db.Relations.Delete db.Relations(i).Name
Next iRel


is superior for that reason (but fwiw, no other).


That's interesting and I suppose that it brings to mind the dangers of
posting old code that one digs out of one's archives. Both these methods
are yours; one posted in this thread and one posted in November 1999.

The reason I posted this was to demonstrate the absurdity of calling any
method the "Poster's Name" method. The idea of looping through a collection
backwards, so to speak, has appeared in this newsgroup hundreds, maybe
thousands of times. In fact there is little that any regular poster here
could call his or her very 100% own. We all learn from one another, and
it's very easy for us to lose the genesis of our thoughts as we consider
and test and experiment. While I can state that I first learned about
LoadFromText and SaveAsText from you, and about exposing Class Modules from
Terry Kreft, and about Security form Pete Barnes, and about WizHook from
Peter Walker, and about the speed advantage of declaring and initializing
field objects when working with RecordSets from Dimitri Furman, there are
probably thousands of things that I learned from all of you, and scores
more, that I cannot remember or credit. And to each of these things I have
added my own creative thought and work, sometimes in response to need,
sometimes simply as matters of style. The collective wisdom of CDMA is just
that, Collective, and not the combination of discreet intellects.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #9

P: n/a

"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
"Michael \(michka\) Kaplan [MS]" <mi*****@online.microsoft.com> wrote in
news:3f********@news.microsoft.com:
"Lyle Fairfield" <Mi************@Invalid.Com> wrote...

What do you think of this one?

Do Until db.Relations.Count = 0
db.Relations.Delete db.Relations(0)
Loop
It does not allow for the fact that the delete could fail, and cause an
infinite loop. The original code I posted:
For iRel = db.Relations.Count - 1 to 0 Step -1
db.Relations.Delete db.Relations(i).Name
Next iRel


is superior for that reason (but fwiw, no other).


That's interesting and I suppose that it brings to mind the dangers of
posting old code that one digs out of one's archives. Both these methods
are yours; one posted in this thread and one posted in November 1999.

The reason I posted this was to demonstrate the absurdity of calling any
method the "Poster's Name" method. The idea of looping through a

collection backwards, so to speak, has appeared in this newsgroup hundreds, maybe
thousands of times. In fact there is little that any regular poster here
could call his or her very 100% own. We all learn from one another, and
it's very easy for us to lose the genesis of our thoughts as we consider
and test and experiment. While I can state that I first learned about
LoadFromText and SaveAsText from you, and about exposing Class Modules from Terry Kreft, and about Security form Pete Barnes, and about WizHook from
Peter Walker, and about the speed advantage of declaring and initializing
field objects when working with RecordSets from Dimitri Furman, there are
probably thousands of things that I learned from all of you, and scores
more, that I cannot remember or credit. And to each of these things I have
added my own creative thought and work, sometimes in response to need,
sometimes simply as matters of style. The collective wisdom of CDMA is just that, Collective, and not the combination of discreet intellects.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

Hey, you forgot to mention what you learned from me: Not to have
discussions with assholes.

Nov 12 '05 #10

P: n/a
"Michael (michka) Kaplan [MS]" <mi*****@online.microsoft.com> wrote in
message news:3f********@news.microsoft.com...
This code will not do what people would reasonably expect it to -- the
relationships will not actually go away.
--


Thanks for the correction.
Nov 12 '05 #11

P: n/a
Thanks for the help everyone. I had a brain freeze about deleting elements
from a collection renumbering the elements. Deleting backwards is the best
(and simplest) thing I've seen.

Matthew Wells
MW****@NumberCruncher.com

"Matthew Wells" <MW****@NumberCruncher.com> wrote in message
news:9f******************@bignews6.bellsouth.net.. .
I'm using this code to delete all relationships in my mdb file

iFlag = 1
Do While iFlag <> 0
iFlag = 0
For Each rel In db.Relations
db.Relations.Delete rel.Name
iFlag = 1
Next rel
Loop

Just looping once only deletes one relation between two tables. I have to
repeat the loop to delete all relations between all tables. Is there some
outer container or collection that I can loop through? Also, is there an
SQL statement like "DROP RELATION..."?

Thanks.

Matthew Wells
MW****@NumberCruncher.com

Nov 12 '05 #12

P: n/a
Mi************@Invalid.Com (Lyle Fairfield) wrote in
<Xn*******************@130.133.1.4>:
"Michael \(michka\) Kaplan [MS]" <mi*****@online.microsoft.com>
wrote in news:3f********@news.microsoft.com:
"Lyle Fairfield" <Mi************@Invalid.Com> wrote...

What do you think of this one?

Do Until db.Relations.Count = 0
db.Relations.Delete db.Relations(0)
Loop


It does not allow for the fact that the delete could fail, and
cause an infinite loop. The original code I posted:
For iRel = db.Relations.Count - 1 to 0 Step -1
db.Relations.Delete db.Relations(i).Name
Next iRel


is superior for that reason (but fwiw, no other).


That's interesting and I suppose that it brings to mind the
dangers of posting old code that one digs out of one's archives.
Both these methods are yours; one posted in this thread and one
posted in November 1999.

The reason I posted this was to demonstrate the absurdity of
calling any method the "Poster's Name" method. . . .


Well, I wasn't meaning to imply that MichKa invented it, since I
knew that answer before I read his post, having seen it posted int
he newsgroup many times and having used it in code many times. I
just used the shorthand to refer to MichKa's post in this thread as
opposed to the method in your post.

Reading into it any implication that the posting person had some
kind of trademark on the concept is just perverse, Lyle, and it's
the kind of thing that caused me to killfile your old email
address.

The rest of you post, that I've deleted, is just so much griping on
the basis of a complaint that never should have been registered, so
if you keep up this kind of posting, you'll go back in the
killfile.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #13

P: n/a
dX********@bway.net.invalid (David W. Fenton) wrote in
news:94***************************@24.168.128.74:
The rest of you post, that I've deleted, is just so much griping on
the basis of a complaint that never should have been registered, so
if you keep up this kind of posting, you'll go back in the
killfile.


this kind of posting

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #14

P: n/a
"XMVP" <ac***********@hotmail.com> wrote:
Hey, you forgot to mention what you learned from me: Not to have
discussions with ***holes.


Another posting by Don P Mellon.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.