• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

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?
1 Solution
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:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now