0

Is it possible to open an .mdb/.accdb file (either client-side or server-side) that includes command-line parameters?

What I'm eventually trying to accomplish is embedding a link (with parameters) within an Outlook email (being generated in another Access db via button's OnClick VBA) and when the recipient clicks the link, it automatically opens the .mdb/.accdb file where they can print off the current version of a report(s) based of these parameters.

First attempt

Originally, I was doing this with a batch file located on one of our servers (embedding a link to this batch file in the generated email):

@echo off
xcopy "\\server\share\thisdb.accdb" "C:\local\*.*" /Y
start msaccess.exe "C:\local\thisdb.accdb" /cmd "param1;param2"

...However, this obviously leads to UAC and overall email security concerns.

Second attempt

So, after discussing this with colleges, they suggested constructing a website. Not a problem! Got an ASP.NET VB website up and running in the same day, plus figured out how to pass and grab query strings from link to site.

The problem begins when it comes to attempting to open Access on the client and/or server...

I've tried...

  • Shell
  • System.Diagnostics.Process (with/without System.Diagnostics.ProcessStartInfo)
  • etc... etc... (I don't recall all the methods I've tried from online sources)

Why I'm doing this

For quite some time now, I've simply done the following in VBA:

  1. Auto-generate the email
  2. Exported (and attached) the multiple reports as PDF files
  3. Displayed the email

Over time, this takes a lot of space on our servers and leads to end users looking at out-dated information. My end goal is to create a solution that achieves the following:

  1. Smaller email sizes (benefiting our email server greatly)
  2. The ability for end users to click an embedded link in an email (without any security issues) and open the current version of the report(s)
  3. Do this all in the same mouse clicks (give or take a mouse click) as opening an attached PDF in an Outlook email

Any help/suggestions is greatly appreciated :)

JCBWS
  • 48
  • 1
  • 2
  • 9
  • Note that you won't be able to launch msaccess.exe with parameters from a hyperlink: http://stackoverflow.com/questions/9994595/html-hyperlink-to-call-exe-with-parameters – Andre Nov 07 '16 at 13:53

2 Answers2

1

You shouldn't copy the application (frontend) database. Keep it local and copy the backend database only, the tables of which are linked to the frontend.

For a guide to host a frontend locally:

Deploy and update a Microsoft Access application in a Citrix environment

When located, you can call it with the /x switch from the command-line to run an internal macro:

Command-line switches for Access

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Even with [this](https://www.experts-exchange.com/articles/23959/Deploy-and-update-a-Microsoft-Access-application-in-a-Citrix-environment.html), is there any way to open a locally hosted front-end from a locally-hosted web site URL? – JCBWS Nov 07 '16 at 15:29
  • To my knowledge, browsers don't have this capability. You can download a file (which could be a shortcut), even an executable, but it will be marked at coming from an unsecure zone (which is true), so the user will have accept to run it via the usual UAC dialogue. – Gustav Nov 07 '16 at 15:42
  • Is there anything you would recommend as an alternative that doesn't flag as unsecure (by default) when linked in an Outlook email message? Or would we just have to go back to attaching PDF files. – JCBWS Nov 07 '16 at 17:40
  • I would store the PDFs in _OneDrive_, then post an e-mail with links to these. An update would only require an update of the remote PDFs and a new e-mail notifying this. – Gustav Nov 07 '16 at 18:30
  • I +1 your answer since it helped inspire the "For those that still want to do it this way.." section of my answer – JCBWS Nov 15 '16 at 13:24
0

For those that still want to do it this way..

For those that still want to do it this way, I was able to do the following:

  • Create SQL Stored Procedure (with locked-down admin privileges to xp_cmdshell) that deletes, recreates and runs a scheduled task via cmd and parameters (Links here and here)

    select @cmdstring = 'SchTasks /Delete /TN ""tt_' + @param1 + 'SCExport' + @param2 + 'PDF"" /F' exec master.dbo.xp_cmdshell @cmdstring, NO_OUTPUT

    select @cmdstring = 'SchTasks /Create /S ' + @@SERVERNAME + ' /RU useru /RP userp /XML "' + @xmlfilename + '" /TN "tt_' + @param1 + 'SCExport' + @param 2 + 'PDF"' exec master.dbo.xp_cmdshell @cmdstring, NO_OUTPUT

    select @cmdstring = 'SchTasks /Run /TN "tt_' + @param1 + 'SCExport' + @param2 + 'PDF"' exec master.dbo.xp_cmdshell @cmdstring, NO_OUTPUT

    • The scheduled task is generated via a generated XML file (used function to create the file from XML string here) so I can pass parameters properly through it to the batch file I'm using (Creating and exporting a dummy task will give you the XML string you need to accomplish this)

      + ' <Exec>' + ' <Command>"C:\pathtobat\batfile.bat"</Command>' + ' <Arguments>' + @param1 + ' ' + @param2 + '</Arguments>' + ' </Exec>'

    • The batch file opens and carries the parameters through to the command-line of my Access front-end database

      start "" "C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE" "C:\pathtoAccessdb\Accessdb.accdb" /cmd "%1;%2"

What I`ve decided to do...

Since our company has the full version of SQL Server 2008 R2 Standard (with SSRS properly set up), I've decided to simply reconstruct the three reports in SSRS and put a link in VBA-generated email to the SSRS-hosted report (including parameters in the URL):

http://thisserver/ReportServer/Pages/ReportViewer.aspx?%2fMyReportApp2%2fMyReport&rs:Command=Render&Param1=Value
Community
  • 1
  • 1
JCBWS
  • 48
  • 1
  • 2
  • 9