Delete Relation from VB - incomplete job

[dwhitman]

I'm trying to delete all the relations to a table ("voters") in preparation for deleting it. I see a code fragment in this thread that is supposed to do the job:

http://www.access-programmers.co.uk/forums/showthread.php?t=33182

...and the code I wrote before searching seems pretty equivalent. Here's what I came up with:

Sub deleteRelations()

Dim db As DAO.Database
Dim n As DAO.Relation

Set db = CurrentDb

    For Each n In db.Relations
        If n.Table = "voters" Or n.ForeignTable = "voters" Then
            db.Relations.Delete n.Name
        End If
    Next n
Set db = Nothing
End Sub

And it sort of works. When run, it deletes some of the relations to that table "voters". But not all of them. If you run it again, it deletes a few more before stopping prematurely. Eventually if you run it enough times, all the relations do get deleted. But for the life of me, I can't figure out why it's taking several iterations to do the job.

I'm wondering if the delete operation somehow messes up the "For Each" construct, so that it thinks it's gone through the whole set when it hasn't? I tried inserting db.Relations.Refresh statements in several different places with no obvious change.

Any ideas?

 

[dwhitman]

Thanks!

Pat, your explanation makes perfect sense. Let me give that a try.

 

[dwhitman]

Yep, it works

Pat, thanks again, your suggestion worked perfectly.

In case it could help someone else down the line, here's my code implementing your idea:

Sub deleteRelations()

Dim db As DAO.Database
Dim i As Integer

Set db = CurrentDb

For i = db.Relations.Count - 1 To 0 Step -1
    If db.Relations(i).Table = "voters" Or db.Relations(i).ForeignTable = "voters" Then
        db.Relations.Delete db.Relations(i).Name
    End If
Next i
Set db = Nothing
End Sub

 

[raskew]

Hi -

You can pull the delete relations code out of this application. It works.

Sub DeleteTableTest3(pMyTable As String)
' This procedure deletes specified tables and any
' existing relationships the tables are participating
' in.
' Used inappropriately, it will have a devastating
' effect on an application.
' For safety's sake, I've commented-out the actual
' commands (they follow debug.print in every case).
' Once you've examined the code and are ready to go,
' remove the comments.

Dim db      As Database
Dim td      As TableDef
Dim Test    As String
Dim tName   As String
Dim thisrel As Relation

    Set db = CurrentDb
    
    On Error Resume Next
    
    tName = pMyTable
    '**********
    'Does table tName exist?
    'If true, delete it;
    '**********
    
      Test = db.TableDefs(tName).name
      If Err <> 3265 Then
         '**********
         ' Since the delete action will fail if the
         ' table is participating in any relation, first
         ' find and delete existing relations for table.
         '**********
         For Each thisrel In db.Relations
            If thisrel.Table = tName Or thisrel.ForeignTable = tName Then
               Debug.Print tName & " | " & thisrel.name
               'db.Relations.Delete thisrel.Name
            End If
         Next thisrel
         '**********
         ' Now, we're ready to delete the table.
         '**********
         Debug.Print tName & " will be deleted"
         'docmd.SetWarnings False
         'docmd.DeleteObject acTable, tName
         'docmd.SetWarnings True
      End If
    'End If
    db.Close
    Set db = Nothing
End Sub

HTH - Bob

Added: Should have looked at your reference first. The above is just an updated version of that reference.

 

[raskew]

Pat -

Just did a test of the For Each statement, just to make sure. It in fact alleviates the number/loop problem you spoke of.

Best Wishes - Bob

 

[dwhitman]

Pat -

Just did a test of the For Each statement, just to make sure. It in fact alleviates the number/loop problem you spoke of.

I used a FOR EACH in my original attempt at this, and it seemed to have the problem Pat cites; see the code fragment in my first post.

I suspect that success with the FOR EACH code snippet from that other thread depends on the order of the references and how many of them there are in the references collection. If you have a large number of references and try deleting only a small fraction of the total, and if the one's you're deleting aren't near the end of the collection, Pat's subtle bug isn't going to bite you.

In my case, this table participates in most of the relationships in the db. So the problem rears its head in spades.

 

[raskew]

OK -

I think I retract my previous statement. When I tested it (on Northwind's Orders table, which has 4 relations) the actual deletion was commented-out. The debug.print line correctly returned each of the four relations.

Following your previous post, tried it again, this time actually deleting the relation. Guess what, it only got 3 of the 4. That sure took the wind out of my sails (and shoots the Help File description of the For Each statement).

Thanks for pointing out your problems with the For Each statement. Without that wouldn't have gone back and retested.

Best Wishes - Bob