Unanswered: triggering report export to excel from table creation
I am using sql server 2000, access 2000, and excel 2000. I have a view that exports via job (dts package) from MSSQL to ACCESS and overwrites an existing table in that db. I want to know if this action can trigger a report that is compiled from this table, and a few subsequent queries, and export it to a specified folder in an excel spreadsheet. (Without any human interaction ex. onclick, onopen etc.)
Your problem is that JET (the Access db engine) is not like SQL Server. It is file based whereas SQL Server is a service - SQL Server is "aware", JET is a bit dim. As such it is not possible for JET to trigger anything at all, especially not something requiring complex code and probably automation.
I know what you are thinking - "Thank you pootie - that was very informative and you are clearly as knowledgable as you are handsome however that is absolutely stuff all use to me.".
But I haven't finished yet.....
I am assuming that the access application is not open at the time of the export. As such, you will need to open Access, probably shelling DOS via p_cmdshell (if this is allowed - some shops do not allow its use) as it is the Access VBA code that will run all this. You can set code to run when Access opens using either a macro (yuk) or set a form to open via the startup options. Have it check if there is new data is in the table, if there is run the queries, run the export, shut itself down.