Server-Based Running?

[Dan]

Hi,

I have a VBA program that generates some excel reports. I do this on my
client PC on the first business day of the month, but I would like to
set this up on our Win 2003 Server so that it can run automatically
based on a monthly schedule. The components are 'ODBC to a UNIX
progress db', 'MS Access MDB tables', and 'VBA to generate the results
data and output to Excel files'. The current client VBA includes an
email routing table and uses EasyMail to email out the reports using our
iMail server. How would I get this to run on Win 2003 server? Looking
for pointers.

Thanks,
Dan

 

[Albert D.Kallal]

You can setup a windows script to launch the ms-access applcation....

dim accessApp

msgbox "Click ok to run batch job",64

set accessApp = createObject("Access.Application")

accessApp.OpenCurrentDataBase("C:\Documents and Settings\Albert\My
Documents\Access\ScriptExample\MultiSelect.mdb")

accessApp.Run "TimeUpDate"

accessApp.Quit

set accessApp = nothing

msgbox "Job complete", 64

Of course, you would remove the msgbox stuff.....

The above simply runs a sub called TimeUpDate in a standard ms-access code
module....

 

[John Nurick]

In addition to Albert's wise advice, it's worth studying these articles:

Using Microsoft Access as an Automation Server
http://support.microsoft.com/?id=210111

Considerations for server-side Automation of Office
http://support.microsoft.com/?id=257757

 

[Dan]

After reading the links below, it looks like I should not try to move my
'Access VBA/Excel Output' app to the server as there are too many issues.

Given this what are my alternatives?

Can Visual Basic be used instead to run on the server with no GUI? Can
VB create Excel format files including formatting?

Thanks,
Dan

 

[Tim Ferguson]

Can Visual Basic be used instead to run on the server with no GUI? Can
VB create Excel format files including formatting?

At first sight, I don't see any reason why you can't accomplish either of
these with Visual Basic or with VBS. You don't need Access, but you will
need Excel present to be automated. You can use VB create a console
application and run it as a Service. With VBS, the simplest way is to call
it from the system scheduler; you may be able to talk your network admin to
let it run on his or server if you spend a _lot_ on a fancy dinner first!

Hope that helps


Tim F

 

[Brendan Reynolds]

The requirement for formatting is the fly in the ointment here, I think.
Other than that, you could use VB and the JET database engine to write data
to Excel files without the need to automate Excel on the server. But for the
formatting, I think you'd need to automate Excel on the server, and
Microsoft's warning against server-side automation of Office apps includes
Excel, not just Access.

If you have access (no pun, etc.) to SQL Server 2000 or 2005, you might want
to take a look at SQL Server Reporting Services. Excel is one of the
built-in rendering formats, and it will handle the scheduling and deployment
for you, too.