Executing DTS Package from MS Access2000 code?

[HiBoo]

I am attempting to run a DTS package in SQL7.0 from code in ACCESS2000. Is this possible?

Here is my situation. I create 13 .txt files in Access using a loop structure for over 200 branches. Each loop creates 13 .txt files of branch data. What I want to do is execute a pre-existing DTS package in the loop to import the data into SQL. Automating this process is my goal as this is a data conversion project.

Project Outline:
1) Receive Delimited txt file from source
2) Process source data into proper fields
3) Decrypt source data
4) Import into new SQL tables

What I need to know is...
1) Can this be done in Access using code? If yes,
2) How?
3) Any alternate suggestions would be appreciated.

Thanks for any and all advise!

P.S. - I've also pasted this posting in Tek-Tips Access General Discussions

 

[tlbroadbent]

I provided an answer in the Access forum. See thread181-124711. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[MinAmiChief]

HiBoo,

I write lines to a batch file and then execute the batch file with the VBA fundction that executes NT command's ShellCommand(Command, Visible or not)

1. I function that gets all of the excel spreadsheets file names in a directory
2. For each file name I change it to a standard file name that my DTS package recognises. ie rename Junedata.xls data.xls
3. Execute the DTS package on Data.xls
4. change the filename back again.

All coding is done with VBA and batch files.
Use
"dtsrun" -to execute packages
"osql" - to execute sotred procedures. Have the results out put to a csv file and open it when the procedure has finished.



Be the change that you want to see in the world - Mahatma Gandhi