Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: automatica export data from a query into a text file

    I have a MS Access database. With that I have a query that joins two tables. What I want is to export the latest dataset in that query into a text file every minute automatically. Next export should not include the dataset from last export. Please help, how could do it?

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    First you will need to setup the query so that it only pulls new info, That is something that you will have to decide how to do (your data) have a field that says "exported" and turn it to true after you export that data.

    Second I would recommend writng code in VB that will open the text file you wish to append data to, in append mode, and write each line to of data in your query to your text file. I know it is pretty easy in VB 6.0 but have never had to try it with VBA, I would imagine it should work there are well

    Code to open text file:
    Open PathAndFileName For Append As #1

    Code to write to text file:
    Print #1, DataToOutput

    Close test file when done:
    Close #1

    Good Luck

    S-

  3. #3
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    You're prob gonna need a reference to Microsoft Scripting Runtime.

    Here's what I use to write to a text file, you will need to alter it. My use is single log messages:

    Function WriteTextFile(strMsg As String)
    Dim fs As New FileSystemObject, a As TextStream
    Dim strPath As String
    strPath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")) & "Results.txt"
    Set fs = CreateObject("Scripting.FileSystemObject")
    If Not fs.FileExists(strPath) Then
    fs.CreateTextFile strPath, True
    End If
    Set a = fs.OpenTextFile(strPath, ForAppending, True)
    a.WriteLine (strMsg)
    a.Close
    MsgBox "The results file has been written in " & strPath
    End Function
    All code ADO/ADOX unless otherwise specified.
    Mike.

  4. #4
    Join Date
    Feb 2004
    Posts
    2
    Thank you for your replies.

    My query is like the one below:

    SELECT sp_time, sp_channel, so_artist, so_name
    FROM Song INNER JOIN SongPlayed ON Song.so_key=SongPlayed.sp_so_key
    ORDER BY so_artist, sp_channel;

    How do I schedule access to do it once every minute?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •