1

I am looking for a solution to effectively communicate between two running MS Access applications.

The approaches I tried so far is to use a common linked table and to use MSMQ service for communication. Both approaches work, but there is no way to "push" the data or command from one application to another and since MS Access doesn't support multi-threaded execution of VBA code, it is very difficult to implement polling without performance disadvantages.

Same time, VBA does support the addressof operator (from version 2000) that means we can also theoretically implement call-back functions in VBA and MS Access. But I have never seen any example how this can be used for inter-process communication and would appreciate any minimal example how I can send a string from one MS Access application to another without monitoring a shared table all the time.

Alexander Galkin
  • 12,086
  • 12
  • 63
  • 115

2 Answers2

1

You can use GetObject() to return the Access.Application object from another running db. With the application object you have access to just about everything you might need. Here's a contrived example of opening a form (but you can do a myriad of other things with the Application object):

Sub TestInterop()
Const mdbPath As String = "C:\OtherApp.mdb"
Dim OtherApp As Access.Application

    Set OtherApp = GetObject(mdbPath)
    OtherApp.Visible = True
    OtherApp.DoCmd.OpenForm "Accounts"
End Sub

If the program is not already running, the GetObject() call will start the application (you would need to be careful if you have multiple versions of Access installed as it's difficult to know at runtime which version would actually open the .mdb). However, if GetObject() needs to start the app, it will do so with the visibility set to False, so we explicitly set it to True. If the app is already running, setting its Visibility to True will have no effect.

mwolfe02
  • 23,787
  • 9
  • 91
  • 161
0

Consider it a wild idea, but may be put all your tables into sql express and/or sql ce and make look like a frontend to those tables?

b0rg
  • 1,879
  • 12
  • 17
  • This is exactly what we are doing now and what I mean with "shared table". But there is no way to implement a trigger that would fire if some data is inserted into this table -- you have to keep polling it to look for changes and this degrades the performance of MS Acess application dramatically... – Alexander Galkin Feb 24 '12 at 10:00
  • 1
    @Alexander Galkin: degrade performance dramatically ? I am surprised, since I've been using polling (at low intervals, say 1 min) without siignificant impact. The trick here is to leave the connection open. – iDevlop Feb 24 '12 at 10:09
  • @iDevlop What we want is to communicate the context menu click in one MS Access application into another one and 1 min interval is too long. As long as we use 1 sec polling we see performance problems (mouse cursor freezing for a fraction of second). – Alexander Galkin Feb 24 '12 at 10:11
  • 1
    Are the Access instances on the same LAN ? If yes, did you consider DDE ? Searching for "ms access vba dde" will give you a few examples. – iDevlop Feb 24 '12 at 10:14