Delete records from a delimeted file being used as datasource for a linked server using Jet 4.0 OLE DB in application

I have a .txt file that I am using as a datasource for a linked server. This is a log file that has 3 fields (date, path and message) that I am viewing in a gridview in

Since this log file will grow, I would like to give users the ability to delete records in the file that have a date less then a date they choose in a parameter field.
Here is a sample of my SELECT statement:
Select convert(varchar(10),edate,101) as [Date], ePATH, eMessage from ARLOCKBOX...boaLog#txt
      WHERE convert(varchar(10),edate,101) = '11/10/2008'

I don't think Jet 4.0 supports deleting records through a query.

What is the best way to approach this?

Thanks in advance!
Who is Participating?
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.

Mark WillsTopic AdvisorCommented:
why not import the log file into a table, index according to the most commonly used access paths (but the looks it would include date), and then you can archive, delete, and make use of an indexed query.
billkriegerAuthor Commented:
Thanks for the response. The file will be appended to every night & it will grow very fast. so I was looking for a way to delete old records from the file. So I would not have to go through all the records in my queries. I gave them an option to export the web page results to an Excel file and delete the log file. The application will recreate it.

I did not find any way to be able to delete records in the file? Any suggestions?

Thanks again for taking the time.
Mark WillsTopic AdvisorCommented:
Don't think you will get much joy in deleting from the text file short of reading it in and re-writing it.  There are a couple of DOS type commands to "find" certain values and can be either output, or held back from output - but then you will need to loop around the whole spectrum of dates - not at all a good thing.

What about if...

A stored procedure to import the file - two parameters, one is filename, and the other date. It loads the file into a table, and then deletes anything older than date

Can then export it again if needed.  

If all else fails, could try the following :

DELETE OPENQUERY (ARLOCKBOX, 'SELECT edate FROM boaLog#txt WHERE convert(datetime,edate,101) < ''20060601''');

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
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
.NET Programming

From novice to tech pro — start learning today.