If you are a fan of vintage sci-fi, especially those with unexpected twists and out-of-normal bounds, you may have seen a series called One Step Beyond. Similar to The Twilight Zone, it took a single situation and put a twist on it that brought it beyond the normal or expected. This reaching one step beyond is what I will focus on in this Access Wizard.
In a June 2016 Wizard, I showed you how to fire one event on a form from another form event. In the August 2016 Wizard, I took you a step further and showed you how to fire code from one form that exists in another form within the application. This month we are going to go one step beyond. I will show you how to reach beyond your existing application to run code in another Access application.
Where This Might Come in Handy
Every once in a while, you may have a complicated routine that manipulates a data set that you may care about. Or perhaps you may have a very intricate routine that you would like to be able to use without copying and pasting the code into your existing application.
Just as you can reach from one form to another form within an application, Access can also reach from one application to another application to do something.
I recently had a customer that wanted to get to the results of a complicated series of data manipulation steps that included both SQL statements and called functions. This is a perfect case of creating the data in the first application and getting the results into the second application.
The Mechanics
Conceptually, this might sound like a very complicated process and, yes, conceptually it is. However, mechanically it is truly simple.
You need the following:
- Reference to an Access application
- Variable to hold the path of the application
- Name of routine to run an application.
Here is some sample code:
Public Sub RunTableauCalcsFromPUMA()
Dim appAccess As New Access.Application
Dim strTgtDB as string
On Error GoTo PROCERR
strTgtDB = “C:\Data\Utilities.accdb"
appAccess.OpenCurrentDatabase strTgtDB
appAccess.Run "ExportTableauData"
appAccess.Quit
Set appAccess = Nothing
On Error GoTo 0
PROCEXIT:
Exit Sub
PROCERR:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
RunTableauCalcsFromPUMA of Module modTools"
Resume PROCEXIT
Resume
End Sub
The first line
Dim appAccess As New Access.Application
creates a variable called appAccess, which is a Access application. This variable now can do anything any Access application can do, including run code.
The next variable, strTgtDB, gets assigned the fully qualified path to the Access application you want to work with. In this case, it is an application called Utilities.
The next two lines
appAccess.OpenCurrentDatabase strTgtDB
appAccess.Run "ExportTableauData"
opens the target database, and then with the statement
appAccess.Run "ExportTableauData
runs the routine that does the work. In this case, it does a good bit of data manipulation and then exports that data to a known location on a disk or network.
After that is complete, it is housekeeping time: it closes down the application and sets the Access application to nothing.
Gotchas
In a routine such as this, the possibilities for something to go wrong are significant. As a result, I recommend you include error handling, as I did in my sample code.
The other significant possibility for a problem is that the routine you are calling must be visible to the application that you are using. In most instances that will not be a problem; however, you may need to include a Public qualifier before the routine in your target application.
Conclusion
It is surprising that something that seems so complicated can be so simple in its execution. As you are able to more deeply understand what is going on with Microsoft Access, you can continue to reach one step beyond, and one step beyond, and one step beyond. As a result, your ability to grasp something that was out of your reach extends much further than you would expect.
