Automate importing data into SQL Server using .ADP

[sylmart7]

Hi. I need advice and opinions on the most efficient way to perform
this task.

I'm using an Access .ADP with SQL Server 7.0.

Each month, users need to import data from a new text file into a SQL
Server table, overwriting the previous month's data. The text file is
always named with the month and year, for example, "TextFile0103.txt."


Here's what's currently being done:

1. Create and run a command object in code to DROP the SQL Server
table.

2. Prompt the user for the Month and Year, then build a string for
the text file name (i.e., strFileName = "TextFile" & strResponse &
".txt").

3. Next, import the data:
DoCmd.TransferText acImportDelimited,,"SQLServerTableName",
"FilePath\strFileName"


This works fine, as long as I set the default max records in the .ADP
higher than 10,000 (users may be importing 200,000 - 500,000 records).


Here's what I'd appreciate some help with:

What's a more efficient way to do this, relying on SQL Server rather
than the DoCmd.TransferText action? It has to run from the .ADP
front-end because I need the user input to determine which text file
to import.

Is there some way to:
1. Create a DTS package in SQL Server
2. Pass it the Source name in code so it could be run dynamically for
whatever text file users want to import?
(The Destination is always the same, and I could DELETE all records
from that table before running the DTS package.)


Thanks for any help you can provide!