How do I link a text file to Access 2007 ADP?

I am trying to link a text file to my ADP to import results that are emailed to me daily.  I have written the stored procedure to append them into the proper table but cannot get the records from the text file to the temporary table within the SQL Server.  The only workaround that I have come up with is to create a MDB that links the two together and with a query sends the text file to the temp table on the SQL Server, then I have to go into the application and finish the import.

I would like to be able to create the import directly with a single mouse click within the application. Can anyone indicate how to attach the text file to the ADP and possibly delete the file after import?  or am I asking too much?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use TransferText to import a Text file:

Or, you can open an ADO recordset based on the textfile, if that makes more sense:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lcohanDatabase AnalystCommented:
Try this from SQL Query to import a Sample.text file into a temp table having one column and note that the path is relative to the SQL Server and not your client computer. In other words the sample.txt file must be local on the SQL Server:

create table #log (line varchar(4000))
exec('bulk insert #log from "C:\Temp\Sample.text"')

Same thing works with any multi column permanent SQL table
JasBradAuthor Commented:
The solution still requires additional tweaking for the importation due to the fact that there is no header information in the text file.  With header information in the text file to be imported, the solution would work easily.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.