Run Data Access Project Macro from DTS Package

[CanNeverThinkOfaName]

Is it possible to run a macro in a data access project from a DTS package

My problem is...

the DTS package rebuilds the data access project daily but when this completes I need to send reports out

As you can't link to reports in the DTS I have to manually open up the data access project and run a macro that will email out these reports

I have to use reports and not just attach tables/queries in the DTS in an Email task as the reports in the data access project let you format them and lay them out in a user friendly manner

So is it possible

Could you use ActiveX Script maybe?
I've never done that before though...

Thanks
Maria

 

[gradley]

When you say "data access project", do you mean a Visual Basic program using data access? If it is an executable program, you can use the "Execute Process Task" to call the program within DTS.

If the reports can be saved to a disk file (report.rpt for example), you can also email them using the xp_sendmail stored procedure.

 

[CanNeverThinkOfaName]

No its an .adp
as in an access front end with SQL Server back end(tables etc)

 

[gradley]

Is there any way to run this from the command line? I know Access allows macros to run at startup

 

[CanNeverThinkOfaName]

Well I have all the rebuilding of tables for the back end in a DTS and then when the rebuild is finished I have to manually open the front end and email all the reports

I was hoping to find a way to add a step to the end of the DTS package to send out the emails with the reports attached without me having to do it manually..

 

[gradley]

I'm not an expert on Access by any means, but I've heard you can convert Access applications into executables and also that you can create start-up macros (to run the reports) that will run when the applilcation is executed.

If you can change the "manually opening the front end" (via an Access startup macro), you can then use the "Execute Process Task" in DTS that I mentioned earlier.

 

[CanNeverThinkOfaName]

I'll give it a go and see how it works out

thanks for the help!