Deleting Relationships...

**Michael \(michka\) Kaplan [MS]** · Nov 12 '05, 02:36 PM

Re: Deleting Relationships.. .

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.Co unt - 1 to 0 Step -1
db.Relations.De lete db.Relations(i) .Name
Next iRel

--
MichKa [MS]

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

"Matthew Wells" <MWells@NumberC runcher.com> wrote in message
news:9ficb.2292 4$an.21088@bign ews6.bellsouth. net...[color=blue]
> 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.De lete 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
> MWells@NumberCr uncher.com
>
>
>[/color]

**Lyle Fairfield** · Nov 12 '05, 02:36 PM

Re: Deleting Relationships.. .

"Matthew Wells" <MWells@NumberC runcher.com> wrote in
news:9ficb.2292 4$an.21088@bign ews6.bellsouth. net:
[color=blue]
> 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.De lete 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..."?[/color]

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.Coun t > 0
.Relations.Dele te .Relations(.Rel ations.Count - 1).Name
Loop
.Relations.Refr esh
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)

**David W. Fenton** · Nov 12 '05, 02:37 PM

Re: Deleting Relationships.. .

MissingAddress@ Invalid.Com (Lyle Fairfield) wrote in
<Xns9400767C8AE 3FFFDBA@130.133 .1.4>:
[color=blue]
>"Matthew Wells" <MWells@NumberC runcher.com> wrote in
>news:9ficb.229 24$an.21088@big news6.bellsouth .net:
>[color=green]
>> 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.De lete 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..."?[/color]
>
>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.Coun t > 0
> .Relations.Dele te .Relations(.Rel ations.Count -
> 1).Name
> Loop
> .Relations.Refr esh
> 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.[/color]

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:
[color=blue]
> For iRel = db.Relations.Co unt - 1 to 0 Step -1
> db.Relations.De lete db.Relations(i) .Name
> Next iRel[/color]

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

**John Winterbottom** · Nov 12 '05, 02:37 PM

Re: Deleting Relationships.. .

"Matthew Wells" <MWells@NumberC runcher.com> wrote in message
news:9ficb.2292 4$an.21088@bign ews6.bellsouth. net...[color=blue]
> 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.De lete 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..."?
>[/color]

Maybe something like this::

Sub deleteRelations hips()

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 MSysRelationshi ps " _
& "where grbit = 0"

Set dbs = CurrentDb()
Set rst = dbs.OpenRecords et(s, , dbOpenForwardOn ly)

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

rst.Close
Set rst = Nothing
Set dbs = Nothing

End Sub

**Lyle Fairfield** · Nov 12 '05, 02:37 PM

Re: Deleting Relationships.. .

dXXXfenton@bway .net.invalid (David W. Fenton) wrote in
news:94007DE93d fentonbwaynetin vali@24.168.128 .86:
[color=blue]
> MissingAddress@ Invalid.Com (Lyle Fairfield) wrote in
><Xns9400767C8A E3FFFDBA@130.13 3.1.4>:
>[color=green]
>>"Matthew Wells" <MWells@NumberC runcher.com> wrote in
>>news:9ficb.22 924$an.21088@bi gnews6.bellsout h.net:
>>[color=darkred]
>>> 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.De lete 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..."?[/color]
>>
>>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.Coun t > 0
>> .Relations.Dele te .Relations(.Rel ations.Count -
>> 1).Name
>> Loop
>> .Relations.Refr esh
>> 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.[/color]
>
> 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:
>[color=green]
>> For iRel = db.Relations.Co unt - 1 to 0 Step -1
>> db.Relations.De lete db.Relations(i) .Name
>> Next iRel[/color]
>
> 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.[/color]

What do you think of this one?

Do Until db.Relations.Co unt = 0
db.Relations.De lete db.Relations(0)
Loop

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

**Michael \(michka\) Kaplan [MS]** · Nov 12 '05, 02:38 PM

Re: Deleting Relationships.. .

"Lyle Fairfield" <MissingAddress @Invalid.Com> wrote...

[color=blue]
> What do you think of this one?
>
> Do Until db.Relations.Co unt = 0
> db.Relations.De lete db.Relations(0)
> Loop[/color]

It does not allow for the fact that the delete could fail, and cause an
infinite loop. The original code I posted:
[color=blue]
> For iRel = db.Relations.Co unt - 1 to 0 Step -1
> db.Relations.De lete db.Relations(i) .Name
> Next iRel[/color]

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

--
MichKa [MS]

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

**Michael \(michka\) Kaplan [MS]** · Nov 12 '05, 02:38 PM

Re: Deleting Relationships.. .

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" <john_winterbot tom@hotmail.com > wrote in message
news:bksh0g$52c kn$1@ID-185006.news.uni-berlin.de...[color=blue]
> "Matthew Wells" <MWells@NumberC runcher.com> wrote in message
> news:9ficb.2292 4$an.21088@bign ews6.bellsouth. net...[color=green]
> > 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.De lete rel.Name
> > iFlag = 1
> > Next rel
> > Loop
> >
> > Just looping once only deletes one relation between two tables. I have[/color][/color]
to[color=blue][color=green]
> > repeat the loop to delete all relations between all tables. Is there[/color][/color]
some[color=blue][color=green]
> > outer container or collection that I can loop through? Also, is there[/color][/color]
an[color=blue][color=green]
> > SQL statement like "DROP RELATION..."?
> >[/color]
>
>
> Maybe something like this::
>
> Sub deleteRelations hips()
>
> 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 MSysRelationshi ps " _
> & "where grbit = 0"
>
> Set dbs = CurrentDb()
> Set rst = dbs.OpenRecords et(s, , dbOpenForwardOn ly)
>
> Do While Not rst.EOF
> dbs.Execute rst("a").Value, dbFailOnError
> rst.MoveNext
> Loop
>
> rst.Close
> Set rst = Nothing
> Set dbs = Nothing
>
> End Sub
>
>[/color]

**Lyle Fairfield** · Nov 12 '05, 02:38 PM

Re: Deleting Relationships.. .

"Michael $michka$ Kaplan [MS]" <michkap@online .microsoft.com> wrote in
news:3f7247f0$1 @news.microsoft .com:
[color=blue]
> "Lyle Fairfield" <MissingAddress @Invalid.Com> wrote...
>
>[color=green]
>> What do you think of this one?
>>
>> Do Until db.Relations.Co unt = 0
>> db.Relations.De lete db.Relations(0)
>> Loop[/color]
>
> It does not allow for the fact that the delete could fail, and cause an
> infinite loop. The original code I posted:
>[color=green]
>> For iRel = db.Relations.Co unt - 1 to 0 Step -1
>> db.Relations.De lete db.Relations(i) .Name
>> Next iRel[/color]
>
> is superior for that reason (but fwiw, no other).[/color]

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)

**XMVP** · Nov 12 '05, 02:38 PM

Re: Deleting Relationships.. .

"Lyle Fairfield" <MissingAddress @Invalid.Com> wrote in message
news:Xns9400E2B FC3701FFDBA@130 .133.1.4...[color=blue]
> "Michael $michka$ Kaplan [MS]" <michkap@online .microsoft.com> wrote in
> news:3f7247f0$1 @news.microsoft .com:
>[color=green]
> > "Lyle Fairfield" <MissingAddress @Invalid.Com> wrote...
> >
> >[color=darkred]
> >> What do you think of this one?
> >>
> >> Do Until db.Relations.Co unt = 0
> >> db.Relations.De lete db.Relations(0)
> >> Loop[/color]
> >
> > It does not allow for the fact that the delete could fail, and cause an
> > infinite loop. The original code I posted:
> >[color=darkred]
> >> For iRel = db.Relations.Co unt - 1 to 0 Step -1
> >> db.Relations.De lete db.Relations(i) .Name
> >> Next iRel[/color]
> >
> > is superior for that reason (but fwiw, no other).[/color]
>
> 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[/color]
collection[color=blue]
> 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[/color]
from[color=blue]
> 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[/color]
just[color=blue]
> that, Collective, and not the combination of discreet intellects.
>
> --
> Lyle
> (for e-mail refer to http://ffdba.com/contacts.htm)[/color]

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

**John Winterbottom** · Nov 12 '05, 02:38 PM

Re: Deleting Relationships.. .

"Michael (michka) Kaplan [MS]" <michkap@online .microsoft.com> wrote in
message news:3f724828$1 @news.microsoft .com...[color=blue]
> This code will not do what people would reasonably expect it to -- the
> relationships will not actually go away.
>
>
> --[/color]

Thanks for the correction.

**Matthew Wells** · Nov 12 '05, 02:39 PM

Re: Deleting Relationships.. .

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
MWells@NumberCr uncher.com

"Matthew Wells" <MWells@NumberC runcher.com> wrote in message
news:9ficb.2292 4$an.21088@bign ews6.bellsouth. net...[color=blue]
> 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.De lete 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
> MWells@NumberCr uncher.com
>
>
>[/color]

**David W. Fenton** · Nov 12 '05, 02:39 PM

Re: Deleting Relationships.. .

MissingAddress@ Invalid.Com (Lyle Fairfield) wrote in
<Xns9400E2BFC37 01FFDBA@130.133 .1.4>:
[color=blue]
>"Michael $michka$ Kaplan [MS]" <michkap@online .microsoft.com>
>wrote in news:3f7247f0$1 @news.microsoft .com:
>[color=green]
>> "Lyle Fairfield" <MissingAddress @Invalid.Com> wrote...
>>
>>[color=darkred]
>>> What do you think of this one?
>>>
>>> Do Until db.Relations.Co unt = 0
>>> db.Relations.De lete db.Relations(0)
>>> Loop[/color]
>>
>> It does not allow for the fact that the delete could fail, and
>> cause an infinite loop. The original code I posted:
>>[color=darkred]
>>> For iRel = db.Relations.Co unt - 1 to 0 Step -1
>>> db.Relations.De lete db.Relations(i) .Name
>>> Next iRel[/color]
>>
>> is superior for that reason (but fwiw, no other).[/color]
>
>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. . . .[/color]

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

**Lyle Fairfield** · Nov 12 '05, 02:39 PM

Re: Deleting Relationships.. .

dXXXfenton@bway .net.invalid (David W. Fenton) wrote in
news:9401945BFd fentonbwaynetin vali@24.168.128 .74:
[color=blue]
> 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.[/color]

this kind of posting

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

**Tony Toews** · Nov 12 '05, 02:42 PM

Re: Deleting Relationships.. .

"XMVP" <access_morons@ hotmail.com> wrote:
[color=blue]
>Hey, you forgot to mention what you learned from me: Not to have
>discussions with ***holes.[/color]

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

Tony's Main Microsoft Access Page

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Hint's, Tips and FAQ

Deleting Relationships...

Deleting Relationships...

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment