Sometimes it’s necessary to share variables between multiple instances of an application. For example, you might want to share a database connection. Here’s how: Create an ActiveX EXE with an exposed MultiUse class named Class1. An ActiveX DLL won’t work because it’s running in-process within each client. Include a module in the project and declare the required variable you want to share in this module as a public variable.
The code in an ActiveX DLL looks like this:
'**** Module code *****Global gCon As DatabaseGlobal glngNrOfObjects As Long'**** Code in Class ****Public Property Set DBConn(Con As Database) Set gCon = ConEnd PropertyPublic Property Get DBConn() As Database Set DBConn = gConEnd PropertyPrivate Sub Class_Initialize() ' this variable keeps count of objects created glngNrOfObjects = glngNrOfObjects + 1End SubPrivate Sub Class_Terminate() ' decrease object count glngNrOfObjects = glngNrOfObjects - 1 ' if no objects exist, close the connection If glngNrOfObjects = 0 Then If Not gCon Is Nothing Then gCon.Close Set gCon = Nothing End If End IfEnd Sub
Now reference this ActiveX EXE in your project and use it. Here’s how I did it:
'** Code in application using the ActiveX EXE **Dim clsDBConn As Class1Private Sub Form_Load() Set clsDBConn = New Class1 ' set the connection, when first object is ' created If clsDBConn.DBConn Is Nothing Then Set clsDBConn.DBConn = _ Workspaces(0).OpenDatabase( _ " empdb1.mdb") End IfEnd SubPrivate Sub Form_Unload(Cancel As Integer) Set clsDBConn = NothingEnd Sub
You don’t need to include the code to count the object instances and close the database if your variable is a simple one-that is, if it’s not an object reference. Also, in that case, change the Property Set to Property Let.
Both the projects have a reference to DAO because they’re using the database object.