If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#12
|
|||
|
|||
How to Pass a Parm from VBS script to Access 2007
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. |
#13
|
|||
|
|||
How to Pass a Parm from VBS script to Access 2007
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. . |
#14
|
|||
|
|||
How to Pass a Parm from VBS script to Access 2007
On Fri, 23 Apr 2010 08:13:01 -0700, Brad
wrote: 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. I'd certainly suggest using a split database (tables in a shared backend, forms, reports, queries and code in a frontend on each user's desk), quite independent of this automation issue. See http://www.granite.ab.ca/access/splitapp.htm or http://allenbrowne.com/ser-01.html for details. However, it's probably not necessary to have the report table in a separate database from the reports themselves. You could either use a separate, dedicated frontend just for the batch job, or even include the table of reports and the code in your general-purpose frontend, and have it activated with a command line switch from the Scheduler, as suggested earlier in this thread. -- John W. Vinson [MVP] |
#15
|
|||
|
|||
How to Pass a Parm from VBS script to Access 2007
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" wrote in message news 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. . |
#16
|
|||
|
|||
How to Pass a Parm from VBS script to Access 2007
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 |
#17
|
|||
|
|||
How to Pass a Parm from VBS script to Access 2007
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" wrote in message news 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. . . |
#18
|
|||
|
|||
How to Pass a Parm from VBS script to Access 2007
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" wrote in message ... 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 |
|
Thread Tools | |
Display Modes | |
|
|