Deep Linking or Hyperlinking in Microsoft Access

Email hyperlink/Deep Link to Access record

Literally for ages, I’ve wanted to ability to email a link to users, that would take them to a specific record within my access application. For quite a while I’ve been sending out Monday morning email blasts to my application users, with a list of the tasks they have to do. Some of these tasks are on a webserver and those I can include hyperlinks too. But the items in my own app, I have not been able to link to. It’s been very frustrating that the third party product supports hyperlink but my app just couldn’t do it. You can send a link to an application, but you cannot pass parameters with that link, which means it gets kinda useless.

Well FINALLY I managed to figure out a workaround for this, and that is what I am going to share with you today.

Solution Concept

The solution consists of several small but easy steps. The path of the link goes like so:

Outlook hyperlink -> Batch file -> vbs file -> Access application.

I will share with you each step, but we are going to do it in reverse, and start with the how the code inside the access application works, and then go backwards all the way to the Outlook mail. Bear with me.

Step 1 – Create a function in your application to handle the incoming link.

Whether the application is already loaded or not, we need a function in application that can take parameters and navigate to the record. If your apps look anything like mine you probably already have procedures in place that can take an id, and navigate somewhere. I won’t go into details with how to make those, but presume you already have such a feature. In my example below I use the OpenReview function, which navigates to a document review when passed an ID. It could look like this:

In theory you could call it directly from the link but I prefer to wrap it all in a function I call ExternalLink, since there might be different reactions for when the OpenReview is called internally and when it is called from an external source, such as Outlook.

 

If we take a look at the code, note the first line where I check to ensure that my main form “frm_DocManage” is loaded. This is just an extra way of making sure that the application is fully loaded and running. Hopefully it’s superfluous, but I decided to add it as an extra precaution.

Now the incoming link, pInput in this case, I have formated to look like this example: <Type>Review</Type><ID>7462</ID>

Using an xml like notation allows me to include both the type and the ID in the same string. There are plenty of ways to pass the arguments but I personally like this way, because it allows me to easily add extra arguments later, without having to revisit alot of code. I extract the individual elements with the getxml function, which you might have seen in my treeview tutorials. It’s included below:

Finally I run a Select statement based on the type, to determine what further action to take. If it’s a review, I proceed to open a review with the ID passed.

 

Step 2 How to run the ExternalLink from outside our Access Application

Great, so we now have a function inside our Access application, but we need a way to run it, and that method has to support a parameter. For this purpose we create a master vbscript file, and place it on our network. We could maybe have “just” used a batch file, but the vbscript has some advantages, including the ability to wait while we let our user login to the system (as well as get the active object, if the application is already open)

The vbscript (LinkManager.vbs) is comprised of a few parts. Ill go through each bit by itself, so if you are copy pasting, you will have to combine the bits back together.

Part 1 – Check to make sure an argument was received.

This part is quite simple. We just count that that arguments (parameters if you will) was passed to the file, and exit if that is not the case.

Part 2 – Opening the access application (or re-using the one already open)

First we switch to inline error handling, and then try to use GetObject to get our access application. I might add that I use a homemade installer that always copies the compiled file to the Program Files location. It’s technically an accde file, but when we upgraded from mde to accde we didn’t want to break the links people might have had on their PC. Renaming the file to mde solved that.

Anyway, if the application is not open, then GetObject will open it for you, using the application registered to open mde files. If the application is already open, GetObject will return the active application. Should this for some reason fail, we give a message to the user and gracefully exit. At the end we return to “standard” error handling. Improve if you will 🙂

 

Part 3 Wait for the login to happen

I think all my applications require login in one form or another. If the script file has attached to an already running instance of the app it is not a problem. But if the app is just getting started, then the startup form is the login form. So I’ve instructed the script to wait until the login form is no longer loaded, in intervals of 500 milliseconds

Part 4 Running the custom code

Now comes the fun part, we run the access code we wrote in step 1, and pass on the arguments the vbscript received. If you need to, the Run call can accept multiple parameters. At this point the Access code to navigate to your linked item will run

Part 5 Ensure that user control is granted

We need to turn control over to the user. If the application is started by automation it will close when the script ends, unless we do this. Certain actions (And I don’t recall which at the moment) can also trigger user control to be activated. Regardless, it is simple enough to check and activate

Part 6  Bring the window to the forefront

I initially tried to use code inside Access in the ExternalLink function to bring the access window to the forefront but I couldn’t get it to work when the link was executed from Outlook. Focus always ended up back in Outlook. So I found this workaroud and added it to the vbscript. It activates the window which matches the title string provided. If an application title is set in your app, it will use that, otherwise it will default to Microsoft Access.

Part 7 Cleanup

Finally all that remains is to clean up variables used. It might not strictly be necessary but it’s become a habit of mine.

Step 3 How to pass a parameter to the vbscript

Now this is where the method used is a bit of a hack. The underlying issue is that we cannot have a file link in a mail, which also includes a parameter. I don’t know why, whether it’s for security or simply a feature that hasn’t been implemented. Since we can’t pass the parameter we dedicate a folder to contain literally thousands of batch files. So we have a ReviewLink1.bat, a ReviewLink2.bat, and so on. Yea, it’s not pretty but it works. So when we want to send a link to review 681, instead of having a link to a file with a parameter, we have a link to a specific file, where the file contains code to pass to the vbscript. So one file per review.

Lets see how one of those files (ReviewLink681.bat) looks:

So under the networked application folder (Where the backend used to reside before we migrated to SQL server) I placed a folder called LinkManager. I placed the batch files in that folder, and placed the vbscript in a subfolder called Vbs. The subfolder was just to make the vbs file easier to find.

Step 4 How to include the link in your email

I started by making a function that will return the path for a given ReviewID

And in the html body of my mail I include the hyperlink

Step 5 Creation of bat files

Now depending on how you intend to use those hyperlinks into your application, the method of batch file creation might vary. Maybe you want to check/create the batch file just before sending out your email. Personally I send out blasts with lists of review, so there might be 50 links in 1 file. And I don’t want to check 50 batch files each time I create the blast, especially since 40 of the links might be used for the next email as well.

So in my scenario I opted to create 10.000 files in 1 go. That should last me til the end of the this year, then I can append another 10.000 files for next year. It should always be the job of the code inside your application to handle links to items that might have been deleted/removed. We don’t want to leave that to the batch/vbs file anyway. So below is how to create 10.000 files in 1 go. On our server, it took 31 seconds to create all 10.000 files. You might want to start out with maybe 100 or 1000 files for your first test. Your server/connection might not be as fast.

 

Summary

So with the above steps (Hey, no one promised it would be simple) we can now send out emails with deep links into our database application. People can click the link and be brought directly to the right record. Wanna see how it looks? Take a look at the video below

Posted in Misc., Random Tidbits Tagged with: , , , ,
15 comments on “Deep Linking or Hyperlinking in Microsoft Access
  1. Giorgio says:

    Hi Anders, where is wscript defined?

  2. TheSmileyCoder says:

    Hi Giorgio. wscript is the file itself (To the best of my knowledge). I am no expert on vbs file. My understanding is that it is sort of like using Me in a form module.

  3. grovelli says:

    So, with Option Explicit, when you do a Debug, Compile in the VBA window, it doesn’t throw an error even though wscript isn’t defined? Amazing.

    • TheSmileyCoder says:

      No it is a .vbs file. I actually just edit them in notepad++. So there is no “compiling” like we do in the editor.

      • grovelli says:

        I guess I have to try the whole thing out myself to fully understand it.
        “Now the incoming link, pInput in this case, I have formated to look like this example: Review7462”
        Which line of code does this formatting to pInput? The closest thing I’ve been able to find is
        Print #FileNum, “\\Data\ComTool\LinkManager\Vbs\LinkManager.vbs “”Review” & l & “”””

        • TheSmileyCoder says:

          Just for clarification, some of the markup syntax doesn’t seem to post well inside comments, even if placed within code tags. So your comment looks a bit weird 🙂
          Anyway, I didn’t actually show the code generating the email. Its rather extensive code that formats a couple of tables within the mail. Maybe I will get the time to do that later. Thank you for pointing it out.

          • grovelli says:

            So if I want to try out your example, do I need to create frmReview and frm_DocManage? Otherwise can you suggest a way for me to reproduce your accomplishment?

          • grovelli says:

            How do you link your email to a third-party document-control system(you mentioned it in your youtube video)?
            When you click any of the numbers on the left side of your email, it opens up a bat file, what’s the code that achieves that?

          • TheSmileyCoder says:

            I’ve been working on an updated method, along with a sample db to better explain the concept. It should be ready after the weekend.

          • Giorgio says:

            Thanks Anders,
            hopefully there will also be an explanation of how you navigate to that part of the email when you click on any of the links in the Table of Contents; I didn’t understand the bit when you say, “and then in short, each table has an anchor”, did you mean each item in the table of contents has an anchor?

          • TheSmileyCoder says:

            No that part is not in the demo. I’ve tried to keep the demo somewhat simple, despite the complexity of all the different pieces that have to fit together.

  4. grovelli says:

    At 0:30 in the youtube video did you say, “My application (comfortable) sends out emails”?
    When you click on any of the links in the Table of Contents, how do you navigate to that part of the email?

    • TheSmileyCoder says:

      EDIT: I currently can’t seem to post the html without it being formated as HTML, and thus not readable for you…will try something else

      Edit 2: I will email it to you.

  5. Seth Schrock says:

    I had actually done the same thing and had it working, but it wasn’t pretty. I’ll have to compare what I’ve done to your solution and see how you did it.

  6. TheSmileyCoder says:

    Try to take a look at the new post I just made. It features a different technique of registering an application to handle the link. Looks very slick
    http://thesmileycoder.com/access-email-with-hyperlink-to-record/

Leave a Reply

Your email address will not be published. Required fields are marked *

*