asked on

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?

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lcohan

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

jason bradway

ASKER

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.