How To: Switch Access frontend by VBA without leaving Access

Introduction

I had a friend ask me today:

I need your help in the following situation:

I am creating an application with multiple microsoft access files that act as front-end and a sql server database as back-end, I need to change ms access file to another in a single instance, for this I am creating a custom backstage view, but no way to change that file access without having to create a new instance. I know how to create the command in the backstage view, but i do not know which VBA code to use to accomplish my task. to better clarify my question.
Thank you very much in advance.

TLDR: To Loong Didn’t Read

Skip to bottom for solution.

The Story

This time I will try to post my “trip” to solving this particular problem. If you are not interested skip to the bottom for the working code samples.
I know its possible to do this by using Shell to invoke a new Access and then simply close the “old” access. However that is slightly different than what was requested. So I started up access and created 2 test databases, and very original of me I named them Database1 and Database2. (And I usually hazzle people about not naming their fields and controls properly. Shame on me)

Anyway, added a button to a form in Database1 and used the following code:

Upon clicking Database1 would close, but Database2 would not open. Inserting a debug point on the line with OpenCurrentDatabase showed me that the line is never executed. As the database closing is somewhat similar to Quit. This means that the next line is never executed because the application has shut down. I also try to use the OpenCurrentDatabase without closing the active database first which just gives an error.

At this point I realize I need something that runs external to access, but which can be activated from Access. Vbscript is the nearest leap for me, mostly since I already know the syntax as it is similar to VBA. I also see that the path ahead of me is lined up in a few different parts. Executing the vbscript from the active access, closing the db from the script, and opening a new db from the script. The important part at this point is to break up the task into smaller manageable parts, analyze them and then work on them. The analyze part is pretty important. Alot of people want to skip ahead to just work/code on the first part, but before I spend alot of time on part 1, I want to ensure that all parts are atleast feasible. No reason to polish up the first 2 parts if I cannot get part 3 to even run.

With this in mind, the first thing I want to try is to create a vbscript that can be used to automate access. So my first vbscript looked like this:

To make your own vb script you start up notepad and when you save the file save it with the .VBS extension.
The purpose at this point simply to try and see if I can use the vbscript to get a working handle on the access application. Running it directly from the explorer window (doubleclick), it worked just fine, if only 1 access application is open (more on that later).

So the next step is to see if I can close the active access database, without exiting access itself, and then open a new database. So I modified my script to read:

Tested it again, and it worked like I wanted it to. The active database was closed and Database2 was opened. At this point I realize it is likely that this might not work in a pure run-time environment (That is client does not have access installed but is using the run-time library instead), since in that instance it would not make much sense to leave access open without anything to run. Just a word of caution.

So next step is to try and see if I can run the vbscript from within access, or if it will lock up on me, the vbscript trying to close the database that called the script in the first place. Well, going to give it a try.

So I open my Database1, and add a button to my form, and for the click event I write:

This worked fine, and closed the active database and opened Database2, but the FollowHyperlink gave a warning about vbs files being potentially unsafe. Something to worry about later. At least it is working at this point.

Moving on, I want to make the vb script more flexible, so that I can pass parameters to it, more specifically pass it the path of the database I wish to open.
http://stackoverflow.com/questions/2806713/can-i-pass-an-argument-to-a-vbscript-vbs-file-launched-with-cscript

I spent some trying to modify my code, but couldn’t get it to work with FollowHyperlink. So googled “Alternative to FollowHyperlink” and came up with this:

which lead me to this

So to make it work I need to call a windows API from within Access. It is actually not that hard.

First I must declare the API and I usually do this in a single module in my databases to keep all the API in one place. Directly copied from the links above, my stand-alone module now reads:

Note that none of the parameters are optional in the sense we know it from VBA, but if you look up the syntax on ShellExecute you will find that you can actually pass empty strings or 0 in some cases. So they must be provided but may in some cases be left meaningless (empty).

So I now modified my button click event to read:

Of course I also need to modify my vb script to take advantage of the new parameter instead of being hardcoded to always open database2.

It now looks like this, and note that I added a check to see if any parameters were provided:

And while testing this it also turns out I no longer get the security warning from before, which is nice. It makes it appear much more professional in my opinion.
All in all, I am now at the point of having a working solution to my problem. I have found “a” path trough my initial steps. Now that I now it is possible, it is time to start wondering about improving and finetuning the bits.

You may recall that there are potential problems if multiple access are open simultaneously. That is because GetObject will just return the first instance it finds. So we want to make sure we get the right access application before telling it to close the application. I can do this by modifying my GetObject syntax like so:

As you might see or guess from the syntax I am now going through THE db to get to the application, instead of going through the application to get to A db.
This means that my vb script must accept two parameters, and while studying the syntax I see that parameters are simply separated by spaces. Looking at this I realize I also need to take into account that the database path might contain spaces. To pass a doublequote within a string you need to double it up. for example

would actually produce a msgbox with the string “Test” (including both of the doublequotes visible onscreen)

So change my button click event to read:

And test that as well, with a modified vb script.

Once I had that working as well, all that remains is pretty much just prettying up the code, adding comments, error handlers and extras. The final result can be seen a bit down.

Feedback on blog post method:

This time I choose to outline the method and my thinking behind it. I would love to hear some feedback on that. Did you like it? Or was it just too long, and you only wanted the quick-n-dirty solution and did not want to be bored with my lifes tale?

The solution

As explained above the final solution consists of a few parts. The first I present here will be the vbscript.

as well as a standalone module containing the API declare and a few functions:

To use this you must simply call the function with the path of the frontend that you wish to switch to.

Author:

Posted in Misc., Random Tidbits
9 comments on “How To: Switch Access frontend by VBA without leaving Access
  1. Dan Knight says:

    Anders,
    Nice bit of coding; I’ve been working on a similar functionality, so you’ve saved me some time, since I will just have to modify yours rather than reinvent the wheel.

    As to the length of the blog post: I’d say most folks coming by a blog like yours aren’t looking for the casual read, but rather are looking for a solution, AND looking to learn, which means the thought process outline you used helps not only with the solution (the entire code) but most important the “why” of the specific code snippets.

    Nice job and thanks.

    • TheSmileyCoder says:

      Hi Dan
      Thank your for the feedback. Its always nice to know that people are using what I took time to put into words 🙂

  2. Christopher Smith says:

    Hi,
    Also have to comment very nice way of switching, I also hadn’t thought to use Windows Script Host. Actually works much faster than what I would have thought.

    However has anyone come across on some rare instances “Set oApp=GetObject(PathToDb).Application” coming back with null when called from a compiled accdr file?

    Thanks

    • TheSmileyCoder says:

      Hi Christopher
      No, I have not seen that happen. But then again, I have not yet had the need to use Runtime only files.

      Does running the code again yield a different result? I.e. is it a intermittent bug? If it is a persistent bug, I would guess it is because the runtime library is not installed, and thus the registry does not have a application registered for handling accdr files?

      • Christopher Smith says:

        Hi,
        It was intermittent on my machine only, now unable to reproduce (Shrugs Shoulders). The script handles accdr’s without any issue at all (The extension means I don’t have to call msaccess.exe with /runtime).

        I havent changed a thing so I am thinking maybe it because of my continual opening the source accdb editing.

        • cscarlet696 says:

          We came across the issue again, the error relates to being unable to read the file because its being locked by another process. To get around this we put in “WScript.Sleep(200)” before and after “Set oApp = GetObject(wscript.arguments(0)).Application”

          So far no re-occurance of the error and hasn’t slowed it to much and may help if anyone else bumps into the issue (Although it may just re-appear and troll me again).

          • TheSmileyCoder says:

            Its hard to provide much feedback on your approach, without knowing all the details, but I am happy to hear it worked for you, and also happy that you took the time to share the wait solution in case others have the same issue.

            If you want to optimize it, and try to avoid the wait, you could perhaps check the object for nothing, and only wait if nothing was returned in the first call. Regardless, I think a 200ms wait on switching apps will be acceptable in almost all circumstances. I just wanted to present another option, in case the 200ms is suddenly not enough 🙂

          • cscarlet696 says:

            Hmm I hadn’t thought of that, I’ll have a look into seeing if it will work and post code if it does. From what I have seen I believe its our AV product (Unfortunatly I have no control over) as my home machine with a spinning rust disc and another AV product doesnt require the wait.

            Just remembered the only thing accdr has an issue with this is if no code is present on the first form that is loaded, if on load has no vba or macros it closes. The solution is to slap any old code in.

  3. Steve Jones says:

    Thanks for taking time to post the example and I do appreciate reading through your thought process. I inherited code that switches database and I have found it to be VERY particular how and where it is placed. Here are the guts of it:
    Private Sub Form_Load()

    If CurrentProject.FullName = “R:\Network\Path\File.accdb” Then
    ‘ Delete the backup copy if it exists
    If Len(Dir(“R:\Network\Path\File_USER.accdb”)) > 0 Then
    Kill “R:\Network\Path\File_USER.accdb”
    End If

    Application.Quit
    CreateBackup ‘ This is an FSO file copy

    ‘ Code to open the newly created copy
    Shell “C:\Program Files (x86)\Microsoft Office\Office15\MSACCESS.exe “”R:\Network\Path\File_USER.accdb”””, vbMaximizedFocus
    Quit
    Else

    DoCmd.OpenForm “Open_Some_Access_Form”, , , stLinkCriteria

    End If

    End Sub

Ask a question, leave some feedback, post a reply!