How to Pass a Parm from VBS script to Access 2007
in [General]
|
From: Hans Up on 23 Apr 2010 14:26 Brad wrote: > Steve and Hans, > > Thanks for the great ideas. I really appreciate your help. > > I like the idea of the Windows Scheduler initiating an Access DB that > contains a table of report names. The info in this table would then control > which reports are run. We are planning to automatically e-mail some reports > and/or store some reports in folders in PDF format. You can use an autoexec macro, as suggested elsewhere, to kick off your operation. Just remember if anyone opens the database without holding down the shift key, the reports will start spitting out. That is the reason I suggested using a separate macro and including its name after the /x switch. Sounds like your version of a "WhichReports" table might benefit from an additional field for output destination: email; PDF; paper. Or maybe put the output destinations in a related table if one report may ever have more than one destination. I was thinking about something simpler .... just a field for report name, and a Yes/No field to indicate whether that report should be included in the next scheduled print cycle. > The one piece that I still don't quite understand is how to use the Access > DB that has the "report" table to link to a second database that has the > table and reports. I understand how a person can link to tables, but I don't > understanding linking to reports that are in a second Database. I must be > missing something. The way I imagined it is the db started by Windows Scheduler contains both the WhichReports table and the actual report objects. If you absolutely need them in separate databases, consider linking WhichReports into the database which contains the reports and have the scheduler start the database which contains the reports. Regards, Hans
From: Brad on 23 Apr 2010 14:59 Steve, Thanks! Brad "Steve" wrote: > Hi Brad, > > You don't need a table of report names! Just put a copy of the reports you > want to run off hours in your off hours database. Put any queries you use > for record sources for the reports in the off hours database. Link to all > tables you use in the queries and/or in the reports. Then all you need is > code to run all the reports in the off hours database. > > Steve > > > "Brad" <Brad(a)discussions.microsoft.com> wrote in message > news:D39FEA85-AB37-4F88-AC98-5031B922217B(a)microsoft.com... > > Steve and Hans, > > > > Thanks for the great ideas. I really appreciate your help. > > > > I like the idea of the Windows Scheduler initiating an Access DB that > > contains a table of report names. The info in this table would then > > control > > which reports are run. We are planning to automatically e-mail some > > reports > > and/or store some reports in folders in PDF format. > > > > The one piece that I still don't quite understand is how to use the Access > > DB that has the "report" table to link to a second database that has the > > table and reports. I understand how a person can link to tables, but I > > don't > > understanding linking to reports that are in a second Database. I must be > > missing something. > > > > Thanks, > > Brad > > > > > > "Hans Up" wrote: > > > >> Brad wrote: > >> > We are in the process of automating the running of several reports > >> > during > >> > "off hours". > >> > >> Hey! That's what I was thinking of trying. > >> > >> My plan is to store the names of the reports in rows in a job queue > >> table. The sub fired by the database macro would fetch those names in a > >> recordset, then loop through the recordset to print out each report. > >> That approach will avoid the need to feed a parameter to my sub from a > >> VBS script. > >> . > >> > > > . >
From: david on 24 Apr 2010 06:43
Rem Create a app object: set app=createobject("access.application") app.opencurrentdatabase(strdb) Rem Run function with parameters: app.run(myPublicFunction,1,"a") (david) "Brad" <Brad(a)discussions.microsoft.com> wrote in message news:973EAFBF-39BC-406F-A0D2-A2EDEE5D8E0A(a)microsoft.com... > We have a VBS Script that initiates a Sub in an Access 2007 DB. > > This works nicely. > > We now would like to pass a parm from our VBS script to this Sub. It is > our > understanding that the "command" field in our VBA code will contain the > passed data. > > What we can't figure out is how to code the VBS script to pass the parm to > Access. > > An example would be most appreciated. > > Thanks, > > Brad |