Append to a TXT File Rather than overwrite

[CharlesWhiteman]

I amusing the following code which takes query data and creates a TXT file. My EDM system poles the directory and when it sees this file it uses the data to suck/index the document and then removes the file. Thats fine as long as the query is not updated but if it is then a document may get missed by the edm system. The solution, which is my question, is how to get it to check to see if linkmanager.txt is there and if it is append to the file or if not then create the file?

This is my current code

DoCmd.TransferText acExportDelim, , "QryLetter", "C:\My
Documents\AccessOffice\AccessOutput\linkmanager.txt", False

Any clues much appriciated

 

[boblarson]

I believe you will have to use either a file scripting object, or an Open strTextPath For Append As #1 method, and cycle through a recordset object populated by the query.

 

[CharlesWhiteman]

Thanks for the reply bob; although I am stuck on it's meaning in practice

 

[boblarson]

You would have to do something like this:

    Dim rst As ADODB.Recordset
    Dim strFilePath As String

    strFilePath = "C:\My Documents\AccessOffice\AccessOutput\linkmanager.txt"

    Set rst = New ADODB.Recordset

    rst.Open "QryLetter", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

    Open strFilePath For Append As #1

    Do Until rst.EOF
        Print #1, rst(0), rst(1), rst(2), rst(3)
        rst.MoveNext
    Loop

    Close #1
    rst.Close
    Set rst = Nothing

The rst parts are the fields within the query. You can either refer to them as rst n being the column in the query (but zero based so the first column is 0, second is 1, etc.) or you can use rst("YourFieldNameHere") to make the code easier to know what it is and also if the columns get moved around in the query it won't affect this code.

Also, this uses ActiveX Data Objects (which I am better in than DAO, but you can use DAO if you are more comfortable with it) so just make sure you have an Microsoft ActiveX Data Objects reference set or a DAO reference set if using DAO.

 

[CharlesWhiteman]

Thanks for setting me on the road. I'll begin the journey!