Passing Variables to Local Application from Salesforce.com

shiftyCreating solutions for a living often puts you in situations where business needs sometimes just don’t align to “how technology normally works”.  One of these situations came up today.

Requirement:  We have information in Salesforce.com that we need to pass to a local (on the Workstation) Access Database and then open it up.

My brain starts screaming “WHAT?!?!  You can’t do that…the browser won’t let you interact with the local file system!”  After some noodling around, my brain reaches WAAAAY back to VB6 days – Microsoft VBScript.  We could use old school ADO and VBScript commands to open up the Access Database, insert records, and then open that same database through a Shell Run command!

Now – only if Salesforce could be manipulated to build the VBScript file and then the user could just execute it!

After a little messing around, here’s the code!  This code generates the VBS, downloads it with a specific name, and the user can just execute it.

**NOTE – It works with one click in IE, but in Chrome and FF you have to execute the file manually from the downloaded location (clicking on the file via the browser throws an error for some reason)

Visualforce Page

<apex:page standardController="Account" extensions="VBScriptGeneratorController" showHeader="false" sidebar="false" contentType="application/vbs#OpenWord.vbs">
<apex:outputText escape="false" value="{!strOutput}" />
</apex:page>

Controller

public VBScriptGeneratorController(ApexPages.StandardController scController) {
 // TODO
 this.soObject = (sObject)scController.getRecord();
Account acct = [SELECT Id, Name FROM Account WHERE Id = :(Id)this.soObject.get('Id')];
strOutput = '';
strOutput+= 'Set conn = CreateObject("ADODB.Connection") \r\n';
 strOutput+= 'conn.Open "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\Users\\Andy\\TestADO.accdb;" \r\n';
 strOutput+= 'strSQL = "INSERT INTO tblData (Var1, Var2) VALUES (\'' + acct.Id + '\',\'' + acct.Name +'\')" \r\n';
 strOutput+= 'Set rs = conn.Execute(StrSQL) \r\n';
 strOutput+= 'conn.Close \r\n';
 strOutput+= 'CreateObject("WScript.Shell").Run "C:\\Users\\Andy\\TestADO.accdb" \r\n';
 }