access custom group

Question

I have my Access 2007 database configured to use "Custom" groups in the navigation pane. I've grouped all my tables in a way that makes sense. However, whenever I update a link table, it loses its grouping.

I have not been able to find a way to avoid this. Since it seems to be unavoidable, I'd like to simply have a macro that adds the table back to the right group programatically. I have not found any examples on how to do this. Any suggestions?

score 6 · Answer 1 · answered Apr 17 '18 at 18:03

Old thread, I know, but I just had to deal with this problem, and thought I'd share my solution: a function that will move an object to a group.

Public Function SetNavPaneGroup(strObjName, strGroupName)
    Dim strSql, idObj, idGrp, db
    Set db = CurrentDb
    idObj = DLookup("Id", "MSysNavPaneObjectIDs", "Name='" & strObjName & "'")
    idGrp = DLookup("Id", "MSysNavPaneGroups", "Name='" & strGroupName & "'")

    If DCount("*", "MSysNavPaneGroupToObjects", "GroupID = " & idGrp & " AND ObjectID = " & idObj) > 0 Then
        strSql = "UPDATE MSysNavPaneGroupToObjects SET GroupID = " & idGrp & ", Name='" & strObjName & "' WHERE ObjectID = " & idObj
        db.Execute strSql, dbFailOnError
    Else
        strSql = "INSERT INTO MSysNavPaneGroupToObjects ( GroupID, ObjectID, Name ) " & vbCrLf & _
        "VALUES (" & idGrp & "," & idObj & ",'" & strObjName & "');"
        db.Execute strSql, dbFailOnError
    End If
    RefreshDatabaseWindow
    Set db = Nothing
End Function

Enjoy.

You update `MSysNavPaneGroupToObjects` rows where ObjectID=idObj if there's a row with (GroupID=idGrp and ObjectID=idObj). But I already have two rows with ObjectID=idObj in this table: one with GroupId=2 (??) and another one with GroupID=54 (my custom group). I think this code will update both rows with the same GroupID. — Winand, Jul 30 '19 at 07:57

score 2 · Answer 2 · edited Jun 25 '23 at 20:33

You should be able to do this by editing the system table MSysNavPaneGroupToObjects. You'll need your table's ID from MSysNavPaneObjectIDs and the group ID from MSysNavPaneGroups (right click the navigation pane to access "Navigation Options" and check "Show System Objects). Then you can use a query like the following to change the table's group.

UPDATE MSysNavPaneGroupToObjects SET GroupID = 9 WHERE ObjectID = 268

Because you are working with system files, backup your .accdb file before starting.

score 2 · Answer 3 · answered Jun 27 '13 at 11:21

Similar problem and although mjoshawa's suggestion didn't quite work for me, I got it working by inserting a new row into the MsSystemNavPaneGroupToObjects table.

INSERT INTO MSysNavPaneGroupToObjects ( GroupID, ObjectID, Name )
VALUES ( 55, 418, 'TableName' )

This worked as part of a UI macro right after the tables were created.

score 2 · Answer 4 · answered Jul 30 '19 at 11:58

put_into_group(obj_name, group_name) moves an object to a specified group by appending/updating record in MSysNavPaneGroupToObjects.

Function DLookupArr(Expr, Domain, Criteria)
' https://stackoverflow.com/questions/5864160
    Dim arr
    Set rs = CurrentDb.OpenRecordset("SELECT " & Expr & " FROM " & Domain & " WHERE " & Criteria)
    If rs.EOF Then
        DLookupArr = Array()
        Exit Function
    End If
    rs.MoveFirst
    Do While Not rs.EOF
        If IsEmpty(arr) Then ReDim arr(0) Else ReDim Preserve arr(UBound(arr) + 1)
        arr(UBound(arr)) = rs(Expr)
        rs.MoveNext
    Loop
    DLookupArr = arr
End Function

Function in_arr(v, arr) As Boolean
    For Each i In arr
        If i = v Then
            in_arr = True
            Exit Function
        End If
    Next i
End Function

Function get_object_group_id(obj_id) As Long
    obj_group_ids = DLookupArr("GroupID", "MSysNavPaneGroupToObjects", "ObjectID=" & obj_id) ' All object's groups
    groups_ids = DLookupArr("Id", "MSysNavPaneGroups", "GroupCategoryID=3") ' All groups IDs
    For Each i In obj_group_ids
        If in_arr(i, groups_ids) Then 'if it's a user-defined group then move from it to `group_name`
            get_object_group_id = i
            Exit For
        End If
    Next i
End Function

Function put_into_group(obj_name, group_name) As Boolean
    obj_id = DLookup("Id", "MSysObjects", "Name='" & obj_name & "'") ' Object-to-move ID
    If IsNull(obj_id) Then Exit Function
    obj_group_id = get_object_group_id(obj_id)
    group_id = DLookup("Id", "MSysNavPaneGroups", "Name='" & group_name & "' And GroupCategoryID=3") ' Target group ID
    If IsNull(group_id) Then Exit Function
    If obj_group_id = group_id Then ' already in group `group_name`
        put_into_group = True
        Exit Function
    End If
    If obj_group_id <> 0 Then ' if it's a user-defined group then move from it to `group_name`
        sql_s = "UPDATE MSysNavPaneGroupToObjects SET GroupID = " & group_id & _
                " WHERE ObjectID=" & obj_id & " And GroupID=" & obj_group_id
    Else ' if object was not in any user-defined groups then add new entry
        sql_s = "INSERT INTO MSysNavPaneGroupToObjects (GroupID, ObjectID, Name) " & _
                "VALUES (" & group_id & "," & obj_id & ",'" & obj_name & "')"
    End If
    CurrentDb.Execute sql_s, dbFailOnError
    RefreshDatabaseWindow
    put_into_group = True
End Function

access custom group

4 Answers4