JCBWS JCBWS - 1 year ago 121 Question

ASP.NET VB Web Site that open .accdb database with command line parameters

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 :)

Answer Source

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):

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download