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:

Public Sub OpenReview(lReviewID as long)
   DoCmd.OpenForm "frmReview"
   Forms!frmReview.Recordset.FindFirst "ReviewID=" & lReviewID 
End Sub

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.


Public Function ExternalLink(pInput As String)
   'Check to see that the main form is open (otherwise app might not be fully loaded, and we don't want to execute a link in that case
   If Not CurrentProject.AllForms("frm_DocManage").IsLoaded Then
      MsgBox "External link executed before app was fully open. Please contact AEC"
      Exit Function
   End If
   Dim sType As String
   Dim sID As String
   sType = getXML(pInput, "Type")
   sID = getXML(pInput, "ID")
   Select Case sType
      Case "Review"
         OpenReview CLng(sID)

      Case "Comment"
         OpenComment clng(sID)
      Case Else
         MsgBox "Option to open a " & sType & " is not yet implemented. Please contact AEC"
   End Select
End Function

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:

Public Function getXML(strxml As String, strElement As String) As String
On Error GoTo ErrHandler
    If InStr(1, strxml, "<" & strElement & ">", vbTextCompare) > 0 And InStr(1, strxml, "</" & strElement & ">", vbTextCompare) > 0 Then
        'Found it
        Dim intLeft As Integer
        Dim intRight As Integer
        intLeft = InStr(1, strxml, "<" & strElement & ">", vbTextCompare) + Len(strElement) + 2
        intRight = InStr(1, strxml, "</" & strElement & ">", vbTextCompare)
        getXML = Mid(strxml, intLeft, intRight - intLeft)
        Err.Raise 666, , "Bad xml passed(" & strxml & ")"
    End If
   On Error GoTo 0
   Exit Function
    Msgbox err.number & " - " & err.description
    Resume exitFunction
End Function

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.

'Check that a argument was passed
if wscript.arguments.count=0 then
	msgbox "Bad link - No Arguments - Error 40"
end if

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)

'Get or open a application
  On Error Resume Next
  Set oApp = GetObject("C:\Program Files\ComTool\ComTool.mde").Application
  If Err.Number <> 0 Then 
     If Err.Number = 432 Then 
        MsgBox "The ComTool application could not be found." & vbNewLine & _
               "Perhaps it has been installed in a non-default location." & vbNewLine & _
               "Please contact Anders Ebro for assistance", vbCritical, "ComTool - Bad link " & err.number
        MsgBox Err.Number & " - " & Err.Description & vbNewLine & _
               "Please contact Anders Ebro for assistance", vbCritical, "ComTool - Bad link " & err.number
     End If
     Set oApp = Nothing
  End If

On Error GoTo 0

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

'Check if login form is open
  Do While oApp.CurrentProject.AllForms("frm_Login").IsLoaded
    wscript.sleep 500

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

'Call function to handle the link, passing in the argument.
  oApp.Run "ExternalLink", WScript.Arguments(0)

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

'Ensure UserControl is activated, and bring app to foreground
	if not oApp.usercontrol then 
	end if

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.

	set WshShell = WScript.CreateObject("WScript.Shell")
	Dim sAppName
	on error resume next
	'The apptitle property might not be set, so in case of error just ignore it
	on error goto 0
	if sAppName="" then
		'Couldn't find an app title, so use "Microsoft Access"
		sAppName="Microsoft Access"
	end if
	WshShell.appactivate sAppName

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.

  Set oApp = Nothing
  set wshshell=nothing

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:

@Echo Off
\\Data\ComTool\LinkManager\Vbs\LinkManager.vbs "<Type>Review</Type><ID>681</ID>"

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

Public Function ExternalReviewLinkPath(lReviewID As Long) As String
   ExternalReviewLinkPath = "\\DATA\ComTool\LinkManager\ReviewLink" & Format(lReviewID, "00000") & ".bat"
End Function

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

"<a href=" & ExternalReviewLinkPath(RS!ReviewID) & ">Review" & RS!ReviewID & "</a>"

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.


Public Sub CreateLinkFiles()
   Dim sFileName As String
   Dim FileNum As Long
   Dim l As Long
   For l = 1000 To 10000
      sFileName = "\\Data\ComTool\LinkManager\LinkReview" & Format(l, "00000") & ".bat"
      FileNum = FreeFile
      Open sFileName For Output As #FileNum
      Print #FileNum, "@Echo Off"
      Print #FileNum, "\\Data\ComTool\LinkManager\Vbs\LinkManager.vbs ""<Type>Review</Type><ID>" & l & "</ID>"""
      Close #FileNum
End Sub


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

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

Leave a Reply to TheSmileyCoder Cancel reply

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


This site uses Akismet to reduce spam. Learn how your comment data is processed.