Deleting Relationships in newer Access Versions

[Dudley]

Old code, new problem?

The following code worked just fine - built using Access 2002 under XP. Now with Vista (I don't know if this might be the problem) and newer Windows OSs, the code fails at the db.Relations.Delete db.Relations(iRel).Name line with a Runtime 3033 error that says I don't have permission to use the MSysNavPaneGroupToObjects object. Can anyone point me in the right direction to correct this? We're now running the app under 2010 and sometimes 2007. Thanks in advance!!!

    Dim db As Database
    Dim iRel As Integer
    Dim tbl As TableDef
    Dim Response As Variant
   

                    Set db = CurrentDb()
                
                    DoCmd.SetWarnings False
'                    For iRel = db.Relations.Count - 1 To 0 Step -1
'                        db.Relations.Delete db.Relations(iRel).Name
'                    Next iRel
                    For iRel = db.Relations.Count - 1 To 0 Step -1
                        If Not db.Relations(iRel).Name = "MSysAccessStorageSTGREL" Then
                            db.Relations.Delete db.Relations(iRel).Name
                        End If
                    Next iRel

 

[spikepl]

So you are trying to wipe out any and all relations, but Access gets upset when you also start messing with its innards.

Then don't mess with its innards.

 

[Dudley]

This is part of a routine I made to reset the database. First I delete all the relationships, then I replace the tables and then restore the relations. Most of all I'd like to understand what has changed - this used to work just fine.

BTW, I have administrator privileges, and I tried opening Access using Run as Administrator and it makes no difference.

Thanks!

 

[Dudley]

Ok, I found a workaround. I learned that starting in Access 2007 there are three hidden relationships that manage the objects pane (tables, queries, forms, etc.). The names of all begin with "MSys". So I excluded them from my routine and now everything works fine - I'm only deleting relationships between my own tables. Here's the new code:

If Not Left(db.Relations(iRel.Name, 4) = "MSys" Then
db.Relations.Delete db.Relations(iRel).Name
End If

Thanks for your help!