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.
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) Else Err.Raise 666, , "Bad xml passed(" & strxml & ")" End If exitFunction: On Error GoTo 0 Exit Function ErrHandler: 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" wscript.quit 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 Else MsgBox Err.Number & " - " & Err.Description & vbNewLine & _ "Please contact Anders Ebro for assistance", vbCritical, "ComTool - Bad link " & err.number End If Set oApp = Nothing wscript.Quit 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 Loop
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 oapp.usercontrol=true 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 sAppName="" on error resume next 'The apptitle property might not be set, so in case of error just ignore it sAppName=oapp.Currentdb.Properties("appTitle") 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.
'Cleanup 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 Next 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