Check instances of opening an add-in (accde)

[jackyP]

In VBA, is it possible in a procedure of a complement (accde) to check if this complement is already opened by another calling database?

 

[Josef P.]

If you like playing the lottery, you could make an entry in a table in the add-in every time you start the add-in and make sure that the entry is deleted again when you exit. Deleting is the difficult part: how do you make sure that this is stable?

Note: Reading the laccdb from the add-in will not help either, as this will be the same user/PC.

As already written in the other thread, I think this approach is the wrong way to go.

 

[Edgar_]

Websockets exist and they're easy to implement. Other sockets too, there's a user who's always making threads about winsockets, you could contact him, he might have some insights for you.

And I'm pretty sure sockets are not unique for this task. Other measures could be used with enough creativity, maybe making the complement insert a record every certain interval, and you could monitor if it's open if there's recent records inserted.

 

[jackyP]

If you like playing the lottery, you could make an entry in a table in the add-in every time you start the add-in and make sure that the entry is deleted again when you exit. Deleting is the difficult part: how do you make sure that this is stable?

Note: Reading the laccdb from the add-in will not help either, as this will be the same user/PC.

As already written in the other thread, I think this approach is the wrong way to go.

Hi @Josef P.

If I have 2 calls to the add-in by the same user, I will have 2 indications in the laccdb file instead of just one.

The problem is also the case of improper closing or the laccdb file is not deleted.

According to my tests, if the laccdb file is not deleted, the user data is added to the existing ones.

The only difference with the table data is that there will be a time when the laccdb file will be deleted.
Reading the contents of the laccdb file is therefore not entirely efficient.

 

[The_Doc_Man]

According to my tests, if the laccdb file is not deleted, the user data is added to the existing ones.

Well... yes. The granularity of the .LACCDB or .LDB file is that anything in the same dev:\path\file is in the same scope. Therefore, everything gets to share one lock file.

Windows ALSO has a locking scheme external to the Access lock file. If the lock file itself becomes locked (at the Windows level), you block Access from being able to open the target DB in any writeable mode. Not going to swear you couldn't open it READ-ONLY but with it already opened in any other mode, let's say I don't like your odds of success there. By design (one that has persisted for a couple of DECADES), the lock file is a bottleneck for access to the file's contents.

 

[jackyP]

I just used ADO's OpenSchema method to determine the connections to the add-in.
I added this code in a test add-on.
Here is the code:

Private Sub cmdTest_Click()

Const JET_SCHEMA_USERROSTER As String = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"

Dim cnc As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim countUser As Integer

cnc.Provider = "Microsoft.ACE.OLEDB.12.0"
cnc.Open "Data Source=" & CodeProject.FullName

Set rst = cnc.OpenSchema(adSchemaProviderSpecific, , JET_SCHEMA_USERROSTER)

Me.txtTest = rst.Fields(0).Name & " :" & vbCrLf & vbCrLf

Do Until rst.EOF
    Me.txtTest = Me.txtTest & rst.Fields(0) & vbCrLf
    countUser = countUser + 1
    rst.MoveNext
Loop

rst.Close
cnc.Close

Me.txtTest.SetFocus

End Sub

You can check in the illustration the opening of 2 complements at the same time.
The 3rd connection is that of the VBA code.
In the case of inappropriate database closure, there is no problem.