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

Posted on 2008-11-12
Last Modified: 2013-11-07
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!
Question by:billkrieger
    LVL 51

    Expert Comment

    by:Mark Wills
    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.

    Author Comment

    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.
    LVL 51

    Accepted Solution

    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''');

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now