Changing navigation bar group when accessed via vba
I have a VBA code module in access that creates 4 new tables and adds them to the database. I would like to add a part at the end where they are organized in the navbar via custom groups so that they are all organized. Is this possible via vba?
EDIT:
I don't want tables to be in a group of unassigned objects. I want to change the name of this group via VBA.
source to share
EDIT: Added some extra code to add other types of objects to the custom Nav group.
The following code will assign tables to your custom navigation group.
ATTENTION!! There is a "update" issue for the "MSysNavPaneObjectID" table that I am still trying to solve. If you create a new table and then try to add it to your group - sometimes it fires on the first try, sometimes it fails but will work after a delay (sometimes up to five or ten minutes!)
At this point, I got around the problem (when it failed) by reading information from the "MSysObjects" table and then adding a new entry to "MSysNavPaneObjectID".
The code below just creates five small tables and adds to the Nav Group 'Clients' tabs
Modify the code to use the group / table names.
Option Compare Database
Option Explicit
Sub Test_My_Code()
Dim dbs As DAO.Database
Dim strResult As String
Dim i As Integer
Dim strSQL As String
Dim strTableName As String
Set dbs = CurrentDb
For i = 1 To 5
strTableName = "Query" & i
'>>> CHANGE FOLLOWING LINE TO YOUR CUSTOM NAME
' Pass the Nav Group, Object Name, Object Type
strResult = SetNavGroup("Clients", strTableName, "Query")
Debug.Print strResult
Next i
For i = 1 To 5
strTableName = "0000" & i
strSQL = "CREATE TABLE " & strTableName & " (PayEmpID INT, PayDate Date);"
dbs.Execute strSQL
'>>> CHANGE FOLLOWING LINE TO YOUR CUSTOM NAME
' Pass the Nav Group, Object Name, Object Type
strResult = SetNavGroup("Clients", strTableName, "Table")
Debug.Print strResult
Next i
dbs.Close
Set dbs = Nothing
End Sub
Function SetNavGroup(strGroup As String, strTable As String, strType As String) As String
Dim strSQL As String
Dim dbs As DAO.Database
Dim rs As DAO.recordSet
Dim lCatID As Long
Dim lGrpID As Long
Dim lObjID As Long
Dim lType As Long
SetNavGroup = "Failed"
Set dbs = CurrentDb
' Ignore the following code unless you want to manage 'Categories'
' Table MSysNavPaneGroupCategories has fields: Filter, Flags, Id (AutoNumber), Name, Position, SelectedObjectID, Type
' strSQL = "SELECT Id, Name, Position, Type " & _
' "FROM MSysNavPaneGroupCategories " & _
' "WHERE (((MSysNavPaneGroupCategories.Name)='" & strGroup & "'));"
' Set rs = dbs.OpenRecordset(strSQL)
' If rs.EOF Then
' MsgBox "No group named '" & strGroup & "' found. Will quit now.", vbOKOnly, "No Group Found"
' rs.Close
' Set rs = Nothing
' dbs.Close
' Set dbs = Nothing
' Exit Function
' End If
' lCatID = rs!ID
' rs.Close
' When you create a new table, it name is added to table 'MSysNavPaneObjectIDs'
' Types
' Type TypeDesc
'-32768 Form
'-32766 Macro
'-32764 Reports
'-32761 Module
'-32758 Users
'-32757 Database Document
'-32756 Data Access Pages
'1 Table - Local Access Tables
'2 Access object - Database
'3 Access object - Containers
'4 Table - Linked ODBC Tables
'5 Queries
'6 Table - Linked Access Tables
'8 SubDataSheets
If LCase(strType) = "table" Then
lType = 1
ElseIf LCase(strType) = "query" Then
lType = 5
ElseIf LCase(strType) = "form" Then
lType = -32768
ElseIf LCase(strType) = "report" Then
lType = -32764
ElseIf LCase(strType) = "module" Then
lType = -32761
ElseIf LCase(strType) = "macro" Then
lType = -32766
Else
MsgBox "Add your own code to handle the object type of '" & strType & "'", vbOKOnly, "Add Code"
dbs.Close
Set dbs = Nothing
Exit Function
End If
' Table MSysNavPaneGroups has fields: Flags, GroupCategoryID, Id, Name, Object, Type, Group, ObjectID, Position
Debug.Print "---------------------------------------"
Debug.Print "Add '" & strType & "' " & strTable & "' to Group '" & strGroup & "'"
strSQL = "SELECT GroupCategoryID, Id, Name " & _
"FROM MSysNavPaneGroups " & _
"WHERE (((MSysNavPaneGroups.Name)='" & strGroup & "') AND ((MSysNavPaneGroups.Name) Not Like 'Unassigned*'));"
Set rs = dbs.OpenRecordset(strSQL)
If rs.EOF Then
MsgBox "No group named '" & strGroup & "' found. Will quit now.", vbOKOnly, "No Group Found"
rs.Close
Set rs = Nothing
dbs.Close
Set dbs = Nothing
Exit Function
End If
Debug.Print rs!GroupCategoryID & vbTab & rs!ID & vbTab & rs!Name
lGrpID = rs!ID
rs.Close
Try_Again:
' Filter By Type
strSQL = "SELECT Id, Name, Type " & _
"FROM MSysNavPaneObjectIDs " & _
"WHERE (((MSysNavPaneObjectIDs.Name)='" & strTable & "') AND ((MSysNavPaneObjectIDs.Type)=" & lType & "));"
Set rs = dbs.OpenRecordset(strSQL)
If rs.EOF Then
' Seems to be a refresh issue / delay! I have found no way to force a refresh.
' This table gets rebuilt at the whim of Access, so let try a different approach....
' Lets add the record vis code.
Debug.Print "Table not found in MSysNavPaneObjectIDs, try MSysObjects."
strSQL = "SELECT * " & _
"FROM MSysObjects " & _
"WHERE (((MSysObjects.Name)='" & strTable & "') AND ((MSysObjects.Type)=" & lType & "));"
Set rs = dbs.OpenRecordset(strSQL)
If rs.EOF Then
MsgBox "This is crazy! Table '" & strTable & "' not found in MSysObjects.", vbOKOnly, "No Table Found"
rs.Close
Set rs = Nothing
dbs.Close
Set dbs = Nothing
Exit Function
Else
Debug.Print "Table not found in MSysNavPaneObjectIDs, but was found in MSysObjects. Lets try to add via code."
strSQL = "INSERT INTO MSysNavPaneObjectIDs ( ID, Name, Type ) VALUES ( " & rs!ID & ", '" & strTable & "', " & lType & ")"
dbs.Execute strSQL
GoTo Try_Again
End If
End If
Debug.Print rs!ID & vbTab & rs!Name & vbTab & rs!type
lObjID = rs!ID
rs.Close
' Add the table to the Custom group
strSQL = "INSERT INTO MSysNavPaneGroupToObjects ( GroupID, ObjectID, Name ) VALUES ( " & lGrpID & ", " & lObjID & ", '" & strTable & "' )"
dbs.Execute strSQL
dbs.Close
Set dbs = Nothing
SetNavGroup = "Passed"
End Function
source to share
Thanks a lot for your code, I had to change it a bit in my particular case due to a problem with updating the table. I am actually re-creating the table (by deleting the old one earlier). Since the MSysNavPaneObjectID is not updated, the old ID is stored internally.
eg. let's use the tmpFoo table which I want to put in the TEMP group.
tmpFoo is already in the TEMP group. TEMP has id 1 and tmpFoo has id 1000 Then I delete tmpFoo and immediately recreate tmpFoo. tmpFoo is now in the Unassigned Objects section.
In MSysObjects, tmpFoo is now 1100, but in MSysNavPaneObjectID, the table is not updated and tmpFoo is 1000 more here.
In this case, a link is created in the table MSysNavPaneGroupToObjects between TEMP (1) and tmpFoo (1000) => Nothing happens because the identifier 1000 no longer exists in MSysObjects.
So the modified code below gets in all cases the ID from MSysObjects and then checks if the ID exists in MSysNavPaneObjectID.
If not, add a line, then use the same ID to add it to MSysNavPaneGroupToObjects.
So it seems that I have no problem with updating (adding Application.RefreshDatabaseWindow in the top function). Thanks again Wayne,
Function SetNavGroup(strGroup As String, strTable As String, strType As String) As String
Dim strSQL As String
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim lCatID As Long
Dim lGrpID As Long
Dim lObjID As Long
Dim lType As Long
SetNavGroup = "Failed"
Set dbs = CurrentDb
' When you create a new table, it name is added to table 'MSysNavPaneObjectIDs'
' Types
' Type TypeDesc
'-32768 Form
'-32766 Macro
'-32764 Reports
'-32761 Module
'-32758 Users
'-32757 Database Document
'-32756 Data Access Pages
'1 Table - Local Access Tables
'2 Access object - Database
'3 Access object - Containers
'4 Table - Linked ODBC Tables
'5 Queries
'6 Table - Linked Access Tables
'8 SubDataSheets
If LCase(strType) = "table" Then
lType = 1
ElseIf LCase(strType) = "query" Then
lType = 5
ElseIf LCase(strType) = "form" Then
lType = -32768
ElseIf LCase(strType) = "report" Then
lType = -32764
ElseIf LCase(strType) = "module" Then
lType = -32761
ElseIf LCase(strType) = "macro" Then
lType = -32766
Else
MsgBox "Add your own code to handle the object type of '" & strType & "'", vbOKOnly, "Add Code"
dbs.Close
Set dbs = Nothing
Exit Function
End If
' Table MSysNavPaneGroups has fields: Flags, GroupCategoryID, Id, Name, Object, Type, Group, ObjectID, Position
Debug.Print "---------------------------------------"
Debug.Print "Add '" & strType & "' '" & strTable & "' to Group '" & strGroup & "'"
strSQL = "SELECT GroupCategoryID, Id, Name " & _
"FROM MSysNavPaneGroups " & _
"WHERE (((MSysNavPaneGroups.Name)='" & strGroup & "') AND ((MSysNavPaneGroups.Name) Not Like 'Unassigned*'));"
Set rs = dbs.OpenRecordset(strSQL)
If rs.EOF Then
MsgBox "No group named '" & strGroup & "' found. Will quit now.", vbOKOnly, "No Group Found"
rs.Close
Set rs = Nothing
dbs.Close
Set dbs = Nothing
Exit Function
End If
Debug.Print rs!GroupCategoryID & vbTab & rs!ID & vbTab & rs!Name
lGrpID = rs!ID
rs.Close
' Get Table ID From MSysObjects
strSQL = "SELECT * " & _
"FROM MSysObjects " & _
"WHERE (((MSysObjects.Name)='" & strTable & "') AND ((MSysObjects.Type)=" & lType & "));"
Set rs = dbs.OpenRecordset(strSQL)
If rs.EOF Then
MsgBox "This is crazy! Table '" & strTable & "' not found in MSysObjects.", vbOKOnly, "No Table Found"
rs.Close
Set rs = Nothing
dbs.Close
Set dbs = Nothing
Exit Function
End If
lObjID = rs!ID
Debug.Print "Table found in MSysObjects " & lObjID & " . Lets compare to MSysNavPaneObjectIDs."
' Filter By Type
strSQL = "SELECT Id, Name, Type " & _
"FROM MSysNavPaneObjectIDs " & _
"WHERE (((MSysNavPaneObjectIDs.ID)=" & lObjID & ") AND ((MSysNavPaneObjectIDs.Type)=" & lType & "));"
Set rs = dbs.OpenRecordset(strSQL)
If rs.EOF Then
' Seems to be a refresh issue / delay! I have found no way to force a refresh.
' This table gets rebuilt at the whim of Access, so let try a different approach....
' Lets add the record via this code.
Debug.Print "Table not found in MSysNavPaneObjectIDs, add it from MSysObjects."
strSQL = "INSERT INTO MSysNavPaneObjectIDs ( ID, Name, Type ) VALUES ( " & lObjID & ", '" & strTable & "', " & lType & ")"
dbs.Execute strSQL
End If
Debug.Print lObjID & vbTab & strTable & vbTab & lType
rs.Close
' Add the table to the Custom group
strSQL = "INSERT INTO MSysNavPaneGroupToObjects ( GroupID, ObjectID, Name ) VALUES ( " & lGrpID & ", " & lObjID & ", '" & strTable & "' )"
dbs.Execute strSQL
dbs.Close
Set dbs = Nothing
SetNavGroup = "Passed"
End Function
source to share
Here my code is not as user-error friendly as the main code, but it should be a little faster to make a massive move.
Public Sub Test_My_Code()
Dim i As Long, db As Database, qd As QueryDef
Set db = CurrentDb
For i = 1 To 10
DoCmd.RunSQL "CREATE TABLE [~~Table:" & Format(i, "00000") & "](PayEmpID INT, PayDate Date)"
Set qd = db.CreateQueryDef("~~Query:" & Format(i, "00000"), "SELECT * FROM [~~Table:" & Format(i, "00000") & "];")
Next i
MsgBox IIf(SetNavGroup(CategorySelection:="Like '*'", GroupSelection:="='TestGroup'", ObjectSelection:="Like '~~Table:#####'"), "New Tables Moved", "Table Move Failed")
MsgBox IIf(SetNavGroup(CategorySelection:="Like '*'", GroupSelection:="='TestGroup'", ObjectSelection:="Like '~~Query:#####'"), "New Queries Moved", "Query Move Failed")
End Sub
Private Sub SetNavGroup_tst(): MsgBox IIf(SetNavGroup(GroupSelection:="='Verified Formularies'", ObjectSelection:="Like '*Verified*'"), "Tables Moved OK", "Failed"): End Sub
'Parameters:
' CategorySelection -- used to filter which custom(type=4) categories to modify
' ex select the 'Custom' Navigation Category (default): "='Custom'"
' GroupSelection -- used to filter which custom(type=-1) groups to add the objects to
' ex select a specific group: "='Verified Formularies'"
' ex select set of specific groups: "In ('Group Name1','Group Name2')"
' ObjectSelection -- used to filter which database objects to move under the groups
' ex select a range of tables: "Like '*Verified*'"
' UnassignedOnly -- used to only look at objects from the Unassigned group
' True - set only unassigned objects
' False - add objects even if they're already in a group
Public Function SetNavGroup(GroupSelection As String, ObjectSelection As String, Optional CategorySelection As String = "='Custom'", Optional UnassignedOnly As Boolean = True) As Boolean
SetNavGroup = False
If Trim(GroupSelection) = "" Then Exit Function
If Trim(ObjectSelection) = "" Then Exit Function
DoCmd.SetWarnings False
On Error GoTo SilentlyContinue
'TempTable Name
Dim ToMove As String
Randomize: ToMove = "~~ToMove_TMP" & (Fix(100000 * Rnd) Mod 100)
'Build temporary table of what to move
Dim SQL As String: SQL = _
"SELECT [Ghost:ToMove].* INTO [" & ToMove & "] " & _
"FROM ( " & _
"SELECT MSysNavPaneGroups.GroupCategoryID, MSysNavPaneGroupCategories.Name AS CategoryName, MSysNavPaneGroups.Id AS GroupID, MSysNavPaneGroups.Name AS GroupName, MSysObjects.Id AS ObjectID, MSysObjects.Name AS ObjectName, MSysObjects.Type AS ObjectType, '' AS ObjectAlias " & _
"FROM MSysObjects, MSysNavPaneGroupCategories INNER JOIN MSysNavPaneGroups ON MSysNavPaneGroupCategories.Id = MSysNavPaneGroups.GroupCategoryID " & _
"WHERE (((MSysNavPaneGroupCategories.Name) " & CategorySelection & ") AND ((MSysNavPaneGroups.Name) " & GroupSelection & ") AND MSysObjects.Name " & ObjectSelection & " AND ((MSysNavPaneGroupCategories.Type)=4) AND ((MSysNavPaneGroups.[Object Type Group])=-1)) " & _
"GROUP BY MSysNavPaneGroups.GroupCategoryID, MSysNavPaneGroupCategories.Name, MSysNavPaneGroups.Id, MSysNavPaneGroups.Name, MSysObjects.Id, MSysObjects.Name, MSysObjects.Type " & _
"ORDER BY Min(MSysNavPaneGroupCategories.Position), Min(MSysNavPaneGroups.Position)" & _
") AS [Ghost:ToMove] LEFT JOIN ( " & _
"SELECT MSysNavPaneGroups.GroupCategoryID, MSysNavPaneGroupToObjects.GroupID, MSysNavPaneGroupToObjects.ObjectID " & _
"FROM MSysNavPaneGroups INNER JOIN MSysNavPaneGroupToObjects ON MSysNavPaneGroups.Id = MSysNavPaneGroupToObjects.GroupID " & _
") AS [Ghost:AssignedObjects] ON ([Ghost:ToMove].ObjectID = [Ghost:AssignedObjects].ObjectID) AND ([Ghost:ToMove].GroupID = [Ghost:AssignedObjects].GroupID) AND ([Ghost:ToMove].GroupCategoryID = [Ghost:AssignedObjects].GroupCategoryID) " & _
"WHERE [Ghost:AssignedObjects].GroupCategoryID Is Null;"
If Not UnassignedOnly Then SQL = _
"SELECT MSysNavPaneGroups.GroupCategoryID, MSysNavPaneGroupCategories.Name AS CategoryName, MSysNavPaneGroups.Id AS GroupID, MSysNavPaneGroups.Name AS GroupName, MSysObjects.Id AS ObjectID, MSysObjects.Name AS ObjectName, MSysObjects.Type AS ObjectType, '' AS ObjectAlias " & _
"INTO [" & ToMove & "] " & _
"FROM MSysObjects, MSysNavPaneGroupCategories INNER JOIN MSysNavPaneGroups ON MSysNavPaneGroupCategories.Id = MSysNavPaneGroups.GroupCategoryID " & _
"WHERE (((MSysNavPaneGroupCategories.Name) " & CategorySelection & ") AND ((MSysNavPaneGroups.Name) " & GroupSelection & ") AND MSysObjects.Name " & ObjectSelection & " AND ((MSysNavPaneGroupCategories.Type)=4) AND ((MSysNavPaneGroups.[Object Type Group])=-1)) " & _
"GROUP BY MSysNavPaneGroups.GroupCategoryID, MSysNavPaneGroupCategories.Name, MSysNavPaneGroups.Id, MSysNavPaneGroups.Name, MSysObjects.Id, MSysObjects.Name, MSysObjects.Type " & _
"ORDER BY Min(MSysNavPaneGroupCategories.Position), Min(MSysNavPaneGroups.Position);"
DoCmd.RunSQL SQL
If DCount("*", "[" & ToMove & "]") = 0 Then Err.Raise 63 'Nothing to move
'Add the objects to their groups
DoCmd.RunSQL _
"INSERT INTO MSysNavPaneGroupToObjects ( GroupID, Name, ObjectID ) " & _
"SELECT TM.GroupID, TM.ObjectAlias, TM.ObjectID " & _
"FROM [" & ToMove & "] AS TM LEFT JOIN MSysNavPaneGroupToObjects ON (TM.ObjectID = MSysNavPaneGroupToObjects.ObjectID) AND (TM.GroupID = MSysNavPaneGroupToObjects.GroupID) " & _
"WHERE MSysNavPaneGroupToObjects.GroupID Is Null;"
'Add any missing NavPaneObjectIDs
DoCmd.RunSQL _
"INSERT INTO MSysNavPaneObjectIDs ( Id, Name, Type ) " & _
"SELECT DISTINCT TM.ObjectID, TM.ObjectName, TM.ObjectType " & _
"FROM [" & ToMove & "] AS TM LEFT JOIN MSysNavPaneObjectIDs ON TM.ObjectID = MSysNavPaneObjectIDs.Id " & _
"WHERE (((MSysNavPaneObjectIDs.Id) Is Null));"
SetNavGroup = True
EOFn:
On Error Resume Next
DoCmd.DeleteObject acTable, ToMove
On Error GoTo 0
DoCmd.SetWarnings True
Exit Function
SilentlyContinue: Resume EOFn
End Function
source to share