Background: I am using a MS Access 2000 front end with SQL Server 8.0
back end.
I have the requirement to import all text files (regardless of
filename) from a given folder on the network into a table within SQL
Server. This import needs to run continuously, as more text files
will be saved in the folder by a separate system and they need to be
updated into the SQL Server table. I have a DTS which can import all
text files from the folder into the SQL Server table.
The problem is in triggering the DTS continuously. (The Schedule
Package only runs at a maximum of once a minute, which is not quick
enough.)
Can anyone answer any, or all, of the following:
1. Is there a way of executing the DTS every time a text file appears
in the given folder?
2. Can ANY DTS be run continuously through a loop (either via a
stored procedure or a 2nd DTS) without affecting any other processes
in the SQL Server? I.e., how can you trigger a DTS to run again as
soon as it has completed, regardless of success or failure?
3. Can you reference / execute a DTS within a stored procedure, and
if so, how is this done?
In addition, I need to have a button on the MS Access 2000 GUI that
runs the DTS in case of failure, as a kind of manual update.
4. Is it possible to run a DTS from the Access front end, and, if
yes, how is it done?