Prev: Access Not Responding
Next: Microsoft Office Spreadsheet 10.0
From: Steve on 22 Apr 2010 14:29
Brad,

One way you can do this is to use Windows Scheduler to open your database
file at a preset time. Then in your database have an autoexec macro that
runs your reports. You could even have a separate database with linked
tables and just your reports. The purpose of this database would be to just
run your off hours reports.

Steve
santus(a)penn.com


"Brad" <Brad(a)discussions.microsoft.com> wrote in message
news:B71577E1-07AD-4556-AC1C-25B2C250DB74(a)microsoft.com...
> Hans,
>
> Thanks for the help and the great examples.
>
> We haven't figured everything out yet, but I believe that we are one step
> closer.
>
> We are in the process of automating the running of several reports during
> "off hours".
>
> Your assistance will help us put the foundation pieces in place.
>
> Thanks again,
>
> Brad
>
>
> "Hans Up" wrote:
>
>> Brad wrote:
>> > Hans,
>> >
>> > Same Sub each time, but the Parameter that we would like to pass from
>> > the
>> > Windows Script to this Sub in Access may have a different value each
>> > time.
>>
>> OK, the same Sub each time would make it simpler. But while I was
>> waiting I tried an approach inspired by Tom's suggestion to offer more
>> flexibility.
>>
>> You need something which fires when the database starts to accept the
>> parameter you're feeding with the /cmd switch. In my first try, I used
>> my form's open event because I already had that set up and I have little
>> experience with macros.
>>
>> You could use an autoexec macro, but I chose to create a macro I called
>> "mcrStartController" and trigger it with the /x command line switch.
>>
>> The macro consists of a single RunCode line, and the Function Name box
>> contains Controller()
>>
>> Essentially all the Controller function does is break the strings out
>> from the Command() function and feed parameters to the appropriate sub:
>>
>> Public Function Controller()
>> Dim varArguments As Variant
>> Dim i As Integer
>> Dim strMsg As String
>> varArguments = Split(Command())
>>
>> Select Case varArguments(0)
>> Case "YourSub"
>> YourSub varArguments(1)
>> Case "DoubleIt"
>> DoubleIt varArguments(1)
>> Case Else
>> 'log this if nobody will be around for the MsgBox
>> strMsg = "'" & varArguments(0) & "' not usable"
>> MsgBox strMsg
>> End Select
>> End Function
>>
>> And here are two subs which can be called from Controller:
>>
>> Public Sub YourSub(ByVal pstrVbsParam)
>> Dim strMsg As String
>> strMsg = "Hello " & pstrVbsParam
>> MsgBox strMsg
>> End Sub
>>
>> Public Sub DoubleIt(ByVal pstrNumber As Double)
>> MsgBox pstrNumber & " * 2 = " & CStr(Val(pstrNumber) * 2)
>> End Sub
>>
>> And this is the VBS which starts everything:
>>
>> Dim objShell
>> Dim strExe
>> Dim strDb
>> Dim strParam
>> Dim strMacro
>>
>> Set objShell = WScript.CreateObject("WScript.Shell")
>> strExe = "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"
>> strDb = "C:\Access\wip\version_control\vc.mdb"
>> strMacro = "mcrStartController"
>>
>> 'strParam = "YourSub World"
>> strParam = "DoubleIt 5.2"
>> 'strParam = "Deliberate failure here"
>>
>> ObjShell.exec(strExe & " " & strDb & " /x " & strMacro & _
>> " /cmd " & strParam)
>> Set ObjShell = Nothing
>>
>> It seems kind of fiddly, but it works. I couldn't see a simpler route
>> to get there. In your case, since you're dealing with only one sub, you
>> can simplify this thing.
>>
>> I'm curious how you will feed it different parameters each time. Will
>> you modify the VBS script to change parameters values, or have you
>> worked out a slick alternative?
>> .
>>


From: Hans Up on 22 Apr 2010 16:19
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: Brad on 23 Apr 2010 11:13
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: John W. Vinson on 23 Apr 2010 11:39
On Fri, 23 Apr 2010 08:13:01 -0700, Brad <Brad(a)discussions.microsoft.com>
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]
From: Steve on 23 Apr 2010 14:15
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.
>> .
>>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Access Not Responding
Next: Microsoft Office Spreadsheet 10.0