Can I use DTS Execute Process Task to call Access
-
OK, Try Not To Laugh!
My current shop uses a ton of MS Access along with the Windows Task Scheduler for scheduling. I want to replace this architecture with SQL Server and SQLAgent - but I have to find a way to do it "a little at a time" (did I mention they use a ton of MS Access?). So...
Has anyone had any experience calling MS Access from DTS??? I was thinking of using the Execute Process task to call a BAT file. Inside the BAT I would have the MSACCESS command line passing it a /x macro-name. The Acces macro would do whatever RunCommand and then Exit Access.
Any comments and/or suggestions would be welcome.
Mike at the Mill -
You might run into instances of the scheduled job hanging while it's waiting for a user response (who knows what is in the macro that's being run
)It might be better to use a VB App. You have more control over the execution that way. You can execute the MS Access command-line and exit without waiting for the command to finish.
--------------------
Colt 45 - the original point and click interface
-
What do you need to do with the Access apps? Extract data from the tables, run existing action queries, run existing code, or something else. Are the access files mdbs or adps? if they are mdbs you may want to move the data to sql server and keep the front ends as Access forms.
Chapter 6 of Professional SQL Server 2000 DTS by Wrox covers several issues associated with creating DTS packages that work with Access.
I would not recommend that you use macros at all because hey are hard to debug and there is no error trapping, you will be better off calling a Subroutine in the Access apps. You could have a Helper Access app that contains the code you need to open each of the production apps and initiate events.
In summary, there are lots of ways to do this type of work.
-
Thank you both -
To answer your questions: MDBs not ADP. And yeah, I've got the book - but Chapter 6 says "Converting Heterogeneous ..." - I wish I were that lucky!! I'm not converting - at least not yet! Yes - I DO want to move the data to SS. But I'm new in my job - and I'm not sure I can do this (politically). And yes - you're right about macros (the Halt Macro dlg box helps you "Just Say No" to debugging.
Thanks any way, folks.
-
Yes, you can use DTS to call a batch file to invoke an Access application. I am doing this with an Access Data Project file. I personally find it complex to follow the flow of execution as laid out on the macro tab, so, the only macro I call from the batch is a "Run Code" macro, which opens a code module where I write all my logic. The logic opens recordsets which pass parameters that are used to generate ADP reports - we have over 200 reports - according to a scheduler I built. The module then exports the reports, in snapshot format or whatever, to a holding folder, updates certain SQL Server tables with triggering information, and then the ADP quits. Then, later, according to scheduled jobs, the reports are emailed as attachments, using SQL Mail.
This works reliably and quickly.
-
Ok, so using a .bat files to do this isn't the best way for error checking and such, but it you really want to call the access apps the same way you do currently with Windows task scheduler, you can create the scheduled tasks and use DTS and the SQL Agent to call them. If you really want to do it you can call them with the schtasks command. That way you can do whatever processing you need to in the begnning of your DTS job then call the scheduled task, then do more processing in DTS if need be...
-
If you are just concerned with firing off a .BAT file, then you don't even need to use DTS, just the job scheduler.
The only caution I have is be very aware of your permissions, remember that the job scheduler is run using the permissions of the SERVER's SQL Server Agent.
Shalom!,Michael Lee
-
Man, ...
You guys are good!!! Thanks Mike, Michael, and Luke.
Mike at the Mill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply