Access Email with Hyperlink to record
A couple of days ago I posted about ways to get an email hyperlink to activate your access database and navigate to a specific record.
It was a bit cumbersome because it had to use both batch files and vbs files as intermediate steps, and to make matters worse, it required a single batch file per record you wanted to link to. It wasn’t pretty but it was the best I had managed to make in order to actually getting record hyperlinks working.
A fellow MVP, Ben Clothier, was reading my article and trying to think of ways to improve upon it. He came across this article and mailed it to me :
Basically, if you have ever wondered how Windows knows to activate Chrome, Internet Explorer or something else, here is the answer. It is specified in the registry. If we take a look in the registry we see:
Now whenever a http link is clicked, the information here tells windows to fire up “C:\Program Files (x86)\Google\Chrome\Application\chrome.exe” and pass it the %1 parameter (Which would be the web page address) . From there its up to chrome to load the webpage and display it.
Now the great thing, is that according to the link from Ben Clothier, we should be able to do the same thing. It took a bit of playing around to get the syntax right, because an access app isn’t an executable file. Now we could maybe use msAccess.exe and pass it both the path of our application as well as the parameter (The recordID) that we want to look at, but there are a few issues with that as well. First we might not have the full path of the access installation, and also for the /cmd switch on access to work the application has to not be running already. So instead of trying to use the url to activate access directly I opted for using wscript to activate a vbscript file. Its actually nearly the same vbscript file I used for my original post about hyperlinking to records. More about that later.
So I went into my registry and added the following keys to the HKCU (HKEY_CURRENT_USER).
[HKEY_CURRENT_USER\Software\Classes\ComToolLinkHandler] @="URL: ComTool Link Protocol"
@="wscript.exe G:\DATA\\METRO-TS\ComTool\LinkManager\Vbs\LinkManager.vbs "%1""
After a wee bit (well probably an hour) of playing around I had weeded out the issues. A typo I had made in the registry made me go nearly mad before I got it working. That and parts of the URL I used within the email contained special characters (< and >), and those were getting mangled by the process (Being replaced with “%3e”. I have now for simplicity left them out, otherwise it can be dealt with by some search and replace.
Now when an email is sent, the hyperlink looks like this:
<a href:”ComToolLinkHandler:718″>Record 718</a>
which of course when formatted by the email handler just shows as “Record 718” as a hyperlink (Usually blue and underlined).
The vbs file is the same as we used in the first hyperlink example I posted. The vbs file seems to give the most flexibility. Once I realized we could do this application URL registration I also went and tried to register that the link should be handled directly by the access application, through the use of the /cmd switch. This does work, but the issue is that we end up with multiple open access applications, which isn’t really ideal. If the types of link you send you is of the kind where you login, do something and close again, then you could use the direct access approach. But I really favor the vbs solution, and the flexibility it gives to handle whether the database is already open.
I have combined all 3 solutions (URL protocol Direct Access, URL protocol to vbscript file, and batch file creation) into a single zip file. It includes both the database, and the vbs file.
In the zip file look for “Hyperlink Example by TheSmileyCoder.accdb”. In a “real” production scenario, you would probably want to move the vbs file to a network location and update paths accordingly. I’ve done some extra “stuff” to make the hyperlink example independent of what folder you put the demo in. Again in production, you might want to do that differently.
NOTE: You must rename the file AccessHyperlink.RenameToDotVbs to AccessHyperlink.vbs for the demo to work. Chrome kept blocking the download, due to the vbs file in it.