Add buttons to export and load a single selected object

[joyfullservice]

In some workflows, or when dealing with very large and complex databases, it may be helpful to be able to export a single component rather than the entire project. Suggested by @mwolfe02 in #378

[joyfullservice]

Added some new toolbar buttons to export or import a single object, based on what is currently selected in the navigation pane.

Getting the selected item in the navigation pane isn't something directly exposed to VBA, but it wasn't too difficult to achieve using UIAutomation. Thanks to @mwolfe02 and Daniel Pineault for their inspiration on these techniques!

Reading the name of the icon for the selected object seems to be a reasonably effective way to determine the object type. The free Accessibility Insights for Windows utility makes it really easy to inspect the class names and properties of the UI items.

Now I just need to build out the export and import functions for single objects...

[joyfullservice]

Success! A single click to export the selected item. 👌

[mwolfe02]

Nice!

[joyfullservice]

Well, that ended up being a whole lot more complicated that I was anticipating!! A simple thing like DoCmd.DeleteObject is not so simple when you run it from a library database. For example, if you attempt to delete the autoexec macro from the current database using DoCmd.DeleteObject, it actually deletes the macro from the add-in! Apparently if the object exists in the context of the CodeDb, it will delete it from there first. If it doesn't, then it will delete it from the CurrentDb.

After testing the DeleteObject function in various contexts, I found that if I ran it from an external automation client, it would default to the current database first. Armed with this knowledge, I implemented a VBScript based asynchronous worker engine that could run various processes externally, including a worker queue to manage the external processes and callbacks. This worked great, until I hit the challenge of trying to passively wait for the worker to finish. When I used a DoEvents polling loop to wait for the worker to finish, this kept the add-in persistently as the active VBProject, which meant the worker could not set the current VBProject long enough to delete the database object in the correct context.

This morning I was inspired with the idea of perhaps using alternate renaming approaches, since DoCmd.Rename (mostly) suffers the same issue of running in the context of the add-in before operating in the current database. If I could rename the object to a name that doesn't match the add-in object, I can easily delete the object with DoCmd.DeleteObject. I was able to rename modules through VBE, and tables and queries through the DAO objects. Macros (interestingly) worked fine with DoCmd.Rename, which is a blessing since they don't provide an alternate renaming option that I am aware of.

'---------------------------------------------------------------------------------------
' Procedure : DeleteObjectIfExists
' Author    : Adam Waller
' Date      : 3/3/2023
' Purpose   : Deletes the object if it exists. This gets really tricky if the object
'           : exists in both the add-in database and the current database. Thankfully
'           : we have a way to work around this for most objects by renaming to a
'           : unique name that doesn't exist in the add-in, and deleting the renamed
'           : object.
'---------------------------------------------------------------------------------------
'
Public Sub DeleteObjectIfExists(intType As AcObjectType, strName As String)

    Dim blnExistsInAddIn As Boolean
    Dim strTempName As String

    ' If object does not exist in the current database, no need to go further
    If Not ObjectExists(intType, strName) Then Exit Sub

    ' Check to see if the object exists in the add-in database. (See note above)
    Select Case intType
        ' Objec types used in the add-in
        Case acForm, acMacro, acModule, acQuery, acReport, acTable
            blnExistsInAddIn = ObjectExists(intType, strName, True)
    End Select

    ' Attempt to delete the object
    If DebugMode(True) Then On Error Resume Next Else On Error Resume Next

    If Not blnExistsInAddIn Then
        ' Nice! We can use a simple call to delete the object
        DoCmd.DeleteObject intType, strName
    Else
        ' This is where it gets fun... If you attempt to delete an object from the
        ' VBA code in the add-in, it will default to operating on the add-in object
        ' first, before looking in the current database. This can cause corruption
        ' in the add-in, in addition to failing to delete the object from the
        ' current database. To work around this, we will rename the object to something
        ' random first, then delete it based on the randomized (non-matching) name.

        ' Build a random temp name that will not collide with the add-in or any existing
        ' object in the current database. (But leave a meaningful clue in the name, in
        ' case something goes wrong and it ends up staying in the database.)
        strTempName = strName & "_DELETE_" & GetStringHash(Perf.MicroTimer)

        ' We need to avoid using DoCmd.Rename for the same reasons
        Select Case intType
            Case acForm, acReport
                ' Unfortunately we don't have a way to effectively rename this object.
                Log.Error eelError, "Unable to delete " & strName & _
                    " because an object with the same name exists in the add-in database."
                strTempName = vbNullString
            Case acMacro
                ' The rename command seems to work on this object type... (At least
                ' in Access 2010)
                DoCmd.Rename strTempName, acMacro, strName
            Case acModule
                ' Rename the VBE object
                GetVBProjectForCurrentDB.VBComponents(strName).Name = strTempName
            Case acQuery
                ' Rename the Query Definition object
                CurrentDb.QueryDefs(strName).Name = strTempName
            Case acTable
                ' Rename the Table Definition object
                CurrentDb.TableDefs(strName).Name = strTempName
        End Select

        ' Trap any errors involved in renaming the object
        If Not CatchAny(eelError, "Error renaming object: " & strName, ModuleName & ".DeleteObjectIfExists") Then

            ' Rename object using the temp name
            If strTempName <> vbNullString Then DoCmd.DeleteObject intType, strTempName
        End If
    End If

    ' Catch any errors with deleting the object
    CatchAny eelError, "Error deleting object: " & strName, ModuleName & ".DeleteObjectIfExists"

End Sub

I am happy to report that the strategy of renaming the objects before deleting worked beautifully, and the new Load Selected button now works as intended!

The only exception right now is that you can't reload a form object with the same name as one of the add-in forms. If someone knows a clever way to rename a form object (MSysObjects?) without using DoCmd.Rename, we could add support for that too. 😄

[mwolfe02]

This may be too clever by half, but one option would be to avoid the possibility of naming conflicts by appending a Unicode Private Use Area character to the add-in's form names at design time.

For example:

DoCmd.Rename "Form1" & ChrW(&HE384), acForm, "Form1"

The PUA character shows as a dot in the navigation pane:

Here's proof that the object did get renamed with the PUA character appended and that it kept its Unicode value:

?Hex(AscW(Right(Forms(0).Name, 1)))
E384

[ollypsilon]

What's the issue here? There's only one object in navigation pane and it is selected: Version is 4.0.9.

[joyfullservice]

What's the issue here? There's only one object in navigation pane and it is selected:

Would you be able to do a little testing on this? It could be related to German version of Microsoft Access, or it could be something different in a newer version than what I am working with.

Earlier in this thread I described using the Accessibility Insights for Windows program to inspect the class names of the selected object in the navigation pane. Could you try installing that utility and take a look at the various class names? You can find the VBA code in modUIAutomation. I am assuming that the German version will still use the English names in the class properties, but it would be good to confirm that.

It is a little bit more difficult to step through the code for this because as soon as you hit a breakpoint and activate the IDE, the selected item no longer has the focus. 😄 You might find it easier to add Debug.Print statements in various places to see where the code is failing to identify the selected object.

Let me know what you find out!

[ollypsilon]

Ok, for me this check fails: https://github.com/joyfullservice/msaccess-vcs-integration/blob/0ff31fe6ba208356d774ace113a67f48df4e000e/Version%20Control.accda.src/modules/modUIAutomation.bas#L38

With my version object class is NetUINavPaneItem (UIA_ButtonControlTypeId in your module) and not UIA_PaneControlTypeId

And here all attributes:

I hope I used the utility correct...

[joyfullservice]

On my system when I have an object selected in the navigation pane, the focused element is the navigation pane...

From your screenshot, it appears that elements have the same structure. If you add some debug print statements to the GetSelectedNavPaneObject function, we could clarify exactly what is selected. (The issue could be here, or later on in the code.)

    ' Get currently selected element
    Set oSelected = oClient.GetFocusedElement

    ' Print the selected item to the debug window
    Debug.Print "Selected type: " & oSelected.CurrentControlType
    Debug.Print "Selected class: " & oSelected.CurrentClassName

    ' Drill down to selected item name
    If oSelected.CurrentControlType = UIA_PaneControlTypeId Then

The concept is that the GetFocusedElement call returns the navigation pane. We then walk through the descendents of this object, looking for a button (class NetUINavPaneItem) where the UIA_HasKeyboardFocusPropertyId = True. Again, this is pretty difficult to step through in a debugger because they keyboard focus changes when you switch to the code window.

Use the following steps to test:

1. Open any database with some objects
2. Click the button on the toolbar to open the add-in, then close the add-in form.
3. Open the VBA editor and add the above code to the module.
4. Select a single object in the navigation pane.
5. Without clicking anywhere else, click the button to export the selected component.
6. Inspect the VBE immediate window, and see what type and class were returned by the selected element.

Let me know what you find out!

[ollypsilon]

Hi! Well, that's what I did, working with debug.prints as suggested in your first reply. Did it again, without AI utility loaded, just clicked the table "Users" in navigation pane. Result is the same: Selected type: 50000 Selected class: NetUINavPaneItem

I have also added another table and a query to have more than one object in pane, result is same. Here's a screenshot with same element selected than you have in your screenshot:

But this is "just" the parent (see HasKeyboardFocus), not the clicked element which is this:

[ollypsilon]

As you "expect" one single object only what not using application object properties?

Yes, it's always object with focus and could be anything, no just an element of navigation pane...

[joyfullservice]

Thanks for the additional updates and screenshots. It seems that the GetFocusedElement might be returning the pane object in my (2010) version of Access, but the actual item in your (newer) version of Access. By switching the search scope to subtree, we can search the current element, and any descendant of this element to find the item with keyboard focus. I think this will work in both versions of Access. I also commented out the if statement that looks for the pane control, since this may only be relevant in my environment. (The main purpose there was to ensure we were looking at items in the navigation pane, not in some other random place in the application, but I think we should be fairly safe without this check since there are several other criteria items that have to be met.)

Could you try pasting this into the add-in and see if it works on your system?

Public Function GetSelectedNavPaneObject() As AccessObject

    Dim oClient As UIAutomationClient.CUIAutomation
    Dim oSelected As UIAutomationClient.IUIAutomationElement
    Dim oElement As UIAutomationClient.IUIAutomationElement
    Dim oCondition As UIAutomationClient.IUIAutomationCondition

    ' Create new automation client
    Set oClient = New UIAutomationClient.CUIAutomation

    ' Get currently selected element
    Set oSelected = oClient.GetFocusedElement

    ' Drill down to selected item name
    'If oSelected.CurrentControlType = UIA_PaneControlTypeId Then

        ' Build condition for navigation pane item with keyboard focus
        Set oCondition = oClient.CreateAndCondition( _
            oClient.CreatePropertyCondition(UIA_HasKeyboardFocusPropertyId, True), _
            oClient.CreatePropertyCondition(UIA_ClassNamePropertyId, "NetUINavPaneItem"))

        ' Attempt to find the selected item (looking for keyboard focus)
        Set oElement = oSelected.FindFirst(TreeScope_Subtree, oCondition)

        ' If an item was found, the continue to drill down to get the name and type
        If Not oElement Is Nothing Then
            Set GetSelectedNavPaneObject = GetUnderlyingDbObjectFromButton(oClient, oElement)
        End If
    'End If

End Function

[joyfullservice]

As you "expect" one single object only why not using application object properties? Yes, it's always object with focus and could be anything, no just an element of navigation pane...

That's a good thought... I have not explored those properties before. As you pointed out, the challenge would be narrowing the scope to just the item selected in the navigation pane. You would not want to inadvertently export the currently open form instead of the selected table. The UIAutomation approach solves this problem by explicitly using the navigation pane.

From a software design standpoint, I like the idea of a consistent behavior, where the item is always selected in the navigation pane for export or load from source.

[mwolfe02]

From a software design standpoint, I like the idea of a consistent behavior, where the item is always selected in the navigation pane for export or load from source.

What about removing the buttons from the ribbon and adding commands to the right-click menu of the navigation pane? It's less discoverable, but it removes any ambiguity as to what object will be imported or exported (both for the user and the code).

[joyfullservice]

What about removing the buttons from the ribbon and adding commands to the right-click menu of the navigation pane? It's less discoverable, but it removes any ambiguity as to what object will be imported or exported (both for the user and the code).

That is an interesting idea... How difficult is it to modify the right-click menu for the navigation pane? I am assuming it would be a similar approach to what you describe here. Would you be able to reliably get the context of the item selected?

[mwolfe02]

Kevin Bell uses this feature with his Data Source Manager add-in. I'm sure he'd be willing to assist...even if it's just to say it's more work than it's worth 😂

[ollypsilon]

Well, looks better but doesn't work still. I got this debug.print output: Navigation pane item image name not recognized: Mit Access verknüpfte Tabelle (for Users) It is defined in GetUnderlyingDbObjectFromButton Object name is localized as you can see in AI screenshots from you and me. When I manually override If strImage Like "Table*" Then and set object manually with next statement all is working fine. After changing MS Office language to English image name is returned as Table Linked to Access as expected by your code.

I had the same issue with localized form elements in Excel and switched to index but for me it was possible as we define objects...

[mwolfe02]

I just pinged Kevin with a link to this issue. One of the other things he worked out is making the shortcut items context-sensitive. In his case, the items only appear on the shortcut menu for tables and are hidden for forms and reports. If exporting/importing linked/local tables will not be supported, the menu items could be hidden for those objects.

[joyfullservice]

Well, looks better but doesn't work still. I got this debug.print output: Navigation pane item image name not recognized: Mit Access verknüpfte Tabelle (for Users) It is defined in GetUnderlyingDbObjectFromButton Object name is localized as you can see in AI screenshots from you and me. When I manually override If strImage Like "Table*" Then and set object manually with next statement all is working fine. After changing MS Office language to English image name is returned as Table Linked to Access as expected by your code.

I had the same issue with localized form elements in Excel and switched to index but for me it was possible as we define objects...

Nice!! I was using the image name to figure out what type of object was selected, but maybe there is a better way to do this. 🤔 We could certainly add additional translations to the case statements if that appears to be the easiest way to accomplish this. Would you be able to provide a list of the German names for the images? It is pretty easy to find them using the AI tool.

For example, we might use something like this for tables:

If (strImage Like "Table*") or (strImage Like "*Tabelle") Then

[ollypsilon]

Yes, this could be done but even it's just a few fixed names like table, query, form etc. it's needed for each Office language available. I wonder whether it would make sense to query Access' own object library and search for the corresponding object, in this case there should be a table "Users". Like the set you do but with Not is Nothing condition.

[joyfullservice]

Yes, this could be done but even it's just a few fixed names like table, query, form etc. it's needed for each Office language available. I wonder whether it would make sense to query Access' own object library and search for the corresponding object, in this case there should be a table "Users". Like the set you do but with Not is Nothing condition.

I thought about doing that, but unfortunately there are many databases out there where the same name is used for different object types. You might have a table named Users, but also a form and report that also use the same name. In that case which one would you export?

You are right that the names would have to be spelled out for different language versions. I am fine with doing that at this point, and potentially improving/refactoring that later on. Even if we add five languages the code would still be pretty simple and readable since we are primarily just adding to the case statements. I agree that it's not the most ideal solution, but it definitely seems like the simplest and most reliable at this point. 😄

My plan would be to add languages as requested by end users, and have the interested users provide the language-specific image names using the AI tool.

[KevBell5]

It’s been a while since I look at command bars so it is a bit (ok a lot) fuzzy, but it should be pretty simple to add buttons on the Nav Pan context menus.

I’m adding a new button to the table context menu like this.

Set newButton = CommandBars(*Navigation Pane List Pop-up").Controls.Add(Type:= msoControlButton, Temporary:=true)
With newButton
    .faceID = {image number}
    .caption={my caption}
    .OnAction={public function to call}
End With

In the OnAction function I use Application.CurrentObjectName to get the object name and look at the tabledef connect property to see if it is a linked table or not.

If you want to add context menus for the other objects, I think you use “Navigation Pane object Pop-up” and query the system table to get the object type.

Let me know if this helps.

[ollypsilon]

My plan would be to add languages as requested by end users, and have the interested users provide the language-specific image names using the AI tool.

I've created one object of each class, here are the German names:

• Navigation pane item image name not recognized: Tabelle (for Local table)
• Navigation pane item image name not recognized: Mit Access verknüpfte Tabelle (for Linked table)
• Navigation pane item image name not recognized: Abfrage (for Query)
• Navigation pane item image name not recognized: Formular (for Form)
• Navigation pane item image name not recognized: Bericht (for Report)
• Navigation pane item image name not recognized: Makro (for Macro)
• Navigation pane item image name not recognized: Modul (for Module)
• Navigation pane item image name not recognized: Klassenmodul (for Class module)

I wonder why either non-localized names or localized name objects are not available in VBA, would make multi-language developments easier I'd say.

[joyfullservice]

Thanks, @ollypsilon! I will get these names integrated into the function. Would you be able to test a few different types of queries to determine the best test case for those? (In English, they all end with "Query", so I used "Query".) Tables all started with "Table", so I used "Table". It appears that in German, the other types of tables end with "Tabelle". (I assume this would be the case for all the various types of linked tables like ODBC, linked Excel, linked text, etc...)

    ' There are multiple icons for some objects
    If strImage Like "Table*" Then
        Set objItem = CurrentData.AllTables(strName)
    ElseIf strImage Like "*Query" Then
        Set objItem = CurrentData.AllQueries(strName)

Would you be able to confirm the German icon names with at least a couple different types of tables and queries for me? Thanks!

[ollypsilon]

Would you be able to confirm the German icon names with at least a couple different types of tables and queries for me? Thanks!

I can confirm for all kind of linked tables it is ending with Tabelle. Unfortunately for some reason Access is giving an error on creating any kind of ODBC table (Excel, Access, file), so can't confirm it for ODBC tables. For all kind of queries beside union and passthrough it is ending either Abfrage oder abfrage.

I wonder if enumerating the object's parent group would help in terms of easier to map. They are localized tough but the group might be easier to select the application objects like AllTables, AllQueries...

Localized elements create trouble on AI tool itself: https://github.com/microsoft/accessibility-insights-windows/issues/1572 😉

[joyfullservice]

I can confirm for all kind of linked tables it is ending with Tabelle. Unfortunately for some reason Access is giving an error on creating any kind of ODBC table (Excel, Access, file), so can't confirm it for ODBC tables. For all kind of queries beside union and passthrough it is ending either Abfrage oder abfrage.

Perfect! Thank you for verifying this on the German version. It sounds like the existing code should work then, but let me know if you encounter any issues.

I wonder if enumerating the object's parent group would help in terms of easier to map. They are localized tough but the group might be easier to select the application objects like AllTables, AllQueries...

The challenge there is that you can create custom groups that contain a mixture of different objects. The nice thing about the image name is that the user can't change those, so it should give us a consistent way to identify the object type. (Other than the localization factor.)

[joyfullservice]

It’s been a while since I look at command bars so it is a bit (ok a lot) fuzzy, but it should be pretty simple to add buttons on the Nav Pane context menus.

@KevBell5 - Thanks for the tips on adding custom options to the right-click menu! I may explore that in the future if I run into a situation with a strong use case for this. I probably won't take the time on this particular feature being that it is already a bit of an edge case already. (The tool is already optimized for speed to where you can often export changes on the entire database in the amount of time it takes you to locate and select the desired object.)

[joyfullservice]

This has been implemented on the dev branch, and seems to be working well from my occasional use. I am closing this out as completed. 👍