The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

One Step Beyond — Run Code in Another Access Database


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:
  1. Reference to an Access application
  2. Variable to hold the path of the application
  3. 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.

Tip of the Month: Get More Information about an Error


As you work in Microsoft Access, especially with code, you may get cryptic error messages that are nothing more than a number.

When this happens to you, don’t respond okay to the dialog box, but rather click on debug button, then open the immediate window (control-G), and type “? err.description”

This won’t give you the description every time, but periodically it will fill in details that should have been in the error message but were not.


Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886