Visual Studio instance does not terminate

Add-in Express™ Support Service
That's what is more important than anything else

Visual Studio instance does not terminate
 
Posts 1 - 10 of 14
First | Prev. | 1 2 | Next | Last 
Frederik Handschuh




Posts: 20
 Joined: 2023-08-07
[CODE]Hello everyone, I have a problem with the integration of Access. When I open a database and do something with it in the Open event, this results in the Visual Studio instance continuing to run after Access is closed. What could be the reason for this?

To achieve this behavior, it is sufficient to access the Currentproject

Private Sub AdxAccessAppEvents1_OpenDatabase(sender As Object, e As EventArgs) Handles AdxAccessAppEvents1.OpenDatabase
Debug.Print(AccessApp.CurrentProject.Name)
End Sub
Posted 07 Aug, 2023 19:24:17 Top
Andrei Smolin


Add-in Express team


Posts: 19122
 Joined: 2006-05-11
Hello Frederik,

I assume your Access is running behind the scenes: open the Task Manager window, switch to the Details tab, scan the list of processes looking for MSACCESS.EXE.

I suggest that you get AccessApp.CurrentProject and save it to a variable (e.g. theProject), print theProject.Name and then invoke System.Runtime.InteropServices.Marshal.ReleaseComObject(theProject). I suggest that you check https://www.add-in-express.com/creating-addins-blog/why-doesnt-excel-quit/ where we explain why such issues occur and what is required to solve them.

Regards from Poland (GMT+2),

Andrei Smolin
Add-in Express Team Leader
Posted 08 Aug, 2023 14:31:35 Top
Frederik Handschuh




Posts: 20
 Joined: 2023-08-07
Hello Andrei,

I am trying to release the COM object but I receive the following error message.

"An unhandled exception of type 'System.Runtime.InteropServices.InvalidComObjectException' occurred in Unknown Module. A COM object that has been separated from its underlying RCW cannot be used"

I get this message even if there is no additional code. When I execute the COM object in the Finalize of the AddIn, another instance of Access still remains open in the background.

[CODE]
Private Sub AdxAccessAppEvents1_OpenDatabase(sender As Object, e As EventArgs) Handles AdxAccessAppEvents1.OpenDatabase
Dim accApp As Object
accApp = AccessApp
Debug.Print(accApp.CurrentProject.Name)
System.Runtime.InteropServices.Marshal.ReleaseComObject(accApp)

End Sub
Posted 08 Aug, 2023 16:11:05 Top
Andrei Smolin


Add-in Express team


Posts: 19122
 Joined: 2006-05-11
Hello Frederik,

You should only release COM objects created in your code. Say, AccessApp is created outside of your code and you shouldn't release it. On the other hand, you call access.CurrentProject and this creates a COM object. Check my previous message on what to do to release it and see the code below; also, you can check the blog: it may provide extra details.

Private Sub AdxAccessAppEvents1_OpenDatabase(sender As Object, e As EventArgs) Handles AdxAccessAppEvents1.OpenDatabase
Dim prj As Object = AccessApp.CurrentProject
Debug.Print(prj.Name)
System.Runtime.InteropServices.Marshal.ReleaseComObject(prj)
End Sub

Regards from Poland (GMT+2),

Andrei Smolin
Add-in Express Team Leader
Posted 08 Aug, 2023 18:47:53 Top
Frederik Handschuh




Posts: 20
 Joined: 2023-08-07
Hello Andrei,

This solved my problem. However, now when I try to get the startup properties before releasing the object, it causes Access to not close at all. Or rather, Access closes briefly and immediately reopens

[CODE]
Function GetStartUpProperty(ByRef curproj As Object, ByVal strPropName As String, ByRef foundProp As Boolean) As Long
Dim dbs As Dao.Database, prp As Dao.Property
GetStartUpProperty = -100

' Datenbank holen
dbs = curproj.Application.CurrentDb
On Error GoTo Change_Bye
GetStartUpProperty = dbs.Properties(strPropName).Value
foundProp = True
'System.Runtime.InteropServices.Marshal.ReleaseComObject(dbs)
Exit Function


Change_Bye:
dbs = Nothing
foundProp = False
Exit Function

End Function
Posted 10 Aug, 2023 10:13:49 Top
Andrei Smolin


Add-in Express team


Posts: 19122
 Joined: 2006-05-11
Hello Frederik,

In my previous post I sent you a hyperlink to the blog where we explain how accessing a property of a complex (non-trivial) type creates a COM object that you need to release. In the new version of your code you access the property called Properties; that property returns a COM object of the DAO.Properties type; you leave it unreleased. Then you call the Item property (it is hidden in COM; you can unhide it in the VBA Object Browser; .NET just sees it) on the Properties object. That property accepts a parameter called Index; you pass strPropName to that method. And that method returns an object of the DAO.Property type - it is a COM object that you leave unreleased - corresponding to the parameter specified; you get Value off that object. Do you see? dbs.Properties(strPropName).Value creates two COM object which you don't release.

I suggest that you stop using default methods that COM programming easily provides. When dealing with COM in .NET, such methods hide important details.

I was a VB6/VBA developer years ago. And I do know that VBA gives you a feeling: you write fast. After switching from VBA to VB.NET, I've switched to C# really soon. I bit later, I understood that "writing code fast" doesn't mean "getting results fast". So, I strongly suggest that you switch your project to Option Strict On (the default is Off). This allows you to write your code in the style that C# developers use. This style looks too wordy but it doesn't let you be mistaken by all assumptions; VBA (and VB.NET with Option Strict Off) with such assumptions is a sure way to get issues on a later stage where the time spent would cost significantly higher that the time spent on typing these extra symbols and words.

On a test project I've created to verify your code I set Option Strict On and found two issues. The first one is minor : dbs.Properties.Item(strPropName).Value returns Object and you do not cast it to Long when assigning it to GetStartUpProperty. I usually suggest that you perform the cast explicitly.

The second issue. Microsoft.Office.Interop.Access.Application.CurrentDb returns Microsoft.Office.Interop.Access.Dao.Database, not DAO.Database. I don't know which version of DAO is implemented by Access.Dao but obviously some difference with your DAO version is possible. So, I suggest that you switch from DAO.* to Access.Dao.*. To type less, you can achieve this by adding Imports DAO = Microsoft.Office.Interop.Access.Dao to the Imports section and removing DAO from references.

And finally, I would rewrite dbs.Properties(strPropName).Value in this way: 


Dim props As DAO.Properties = dbs.Properties
Dim prop As DAO.Property = props.Item(strPropName)
If (prop IsNot Nothing) Then
        foundProp = True
        GetStartUpProperty = CLng(prop.Value)
        Marshal.ReleaseComObject(prop)
Else
        foundProp = False
End If
Marshal.ReleaseComObject(props)


To be absolutely sure that this code works, I would also check the incoming properties and add some code to handle dbs.Properties returning Nothing. The last point comes from experience: I've never seen such things in Access but there were lots of similar issues in other Office applications.

HTH

Regards from Poland (GMT+2),

Andrei Smolin
Add-in Express Team Leader
Posted 10 Aug, 2023 12:24:30 Top
Frederik Handschuh




Posts: 20
 Joined: 2023-08-07
First of all, thank you very much for the excellent support. I have another question on a different topic: Is it possible to intercept the 'Save As' action from the backstage? Especially the selected filename and path?
Posted 24 Aug, 2023 16:18:28 Top
Andrei Smolin


Add-in Express team


Posts: 19122
 Joined: 2006-05-11
Hello Frederik,

What do you need to achieve? You can find out where the user saved the document by checking {Workbook, Document etc. }.FullName after the user closes the BackstageView; I'd check if ADXBackstageView.OnHide can be used in this scenario.

Regards from Poland (GMT+2),

Andrei Smolin
Add-in Express Team Leader
Posted 25 Aug, 2023 09:45:02 Top
Frederik Handschuh




Posts: 20
 Joined: 2023-08-07
Good evening, I need your help again. The moment I access the container, I encounter the issue again where Access doesn't close properly. Regardless of whether I release the object or not. 


 Function GetDocProp(ByVal wbInExcelCurrentProjectInAccess As Object, ByVal txt As String) As String
        Try

                Dim app As Access.Application = Nothing
                Dim dbs As Database = Nothing
                Dim container As Container = Nothing
                Dim doc As Document = Nothing
                Dim prop As Dao.Property = Nothing

                Try
                    app = wbInExcelCurrentProjectInAccess.Application
                    dbs = app.CurrentDb
                    container = dbs.Containers("Databases")
                    'doc = container.Documents("SummaryInfo")

                    'If txt = "Creation date" Then
                    '    prop = doc.Properties("DateCreated")
                    'Else
                    '    prop = doc.Properties(txt)
                    'End If

                    GetDocProp = prop.Value
                Finally
                    If prop IsNot Nothing Then Marshal.ReleaseComObject(prop)
                    If doc IsNot Nothing Then Marshal.ReleaseComObject(doc)
                    If container IsNot Nothing Then Marshal.ReleaseComObject(container)
                    If dbs IsNot Nothing Then Marshal.ReleaseComObject(dbs)
                    If app IsNot Nothing Then Marshal.ReleaseComObject(app)

    
                End Try


                Exit Function
       

        Catch ex As Exception  ' Change_Bye:
            Debug.Print(ex.Message)
        End Try



    End Function
Posted 25 Sep, 2023 18:29:31 Top
Andrei Smolin


Add-in Express team


Posts: 19122
 Joined: 2006-05-11
Hello Frederik,

dbs.Containers creates a COM object that you need to release.

Regards from Poland (GMT+2),

Andrei Smolin
Add-in Express Team Leader
Posted 26 Sep, 2023 09:42:25 Top

Posts 1 - 10 of 14
First | Prev. | 1 2 | Next | Last