DAO Recordset Export File

[Littlemalky]

So I found this code to use to export my query as a text file to a file location. It's awesome and efficient and fast, but it's structured as an "Append". I'm not familiar enough with this kind of code to know how to convert it over to just replace the file each time. I want to overwrite it with new contents, not append.

Does anyone know how to modify this?

Private Sub btnEXPORT_TEXT_FILE_Click()
    Dim rs As DAO.Recordset, strRec As String, j As Integer
    Set rs = CurrentDb.OpenRecordset("UNION_ADDITIONS_ACCRUALS_LOAD_FILES")
    Open "[URL="file://\\cobalt\public\SCC\CAPEX\Test\UNION_ADDITIONS_ACCRUALS_LOAD_FILES.txt"]...\Test\UNION_ADDITIONS_ACCRUALS_LOAD_FILES.txt[/URL]" For Append As #1
    Do Until rs.EOF
        For j = 1 To rs.Fields.Count - 1
            If strRec = "" Then
            strRec = rs(j) & vbTab
            Else
            strRec = strRec & rs(j) & vbTab
            End If
        Next
        Print #1, strRec
        rs.MoveNext
        strRec = ""
Loop
Close #1
    MsgBox "Export Complete!"
End Sub

 

[Norie]

Try changing Append to Output.

By the way, I think there might be a way/ways to do this without looping.

 

[Littlemalky]

Is there a way to specify the exact fields I want? That would be ideal.

 

[Norie]

For that you would either need to alter the existing query, use a SELECT statement to only return the required fields from the existing query or use code to only extract the fields you want.

 

[Littlemalky]

So this is working great now, except for that fact that it's cutting off my first field. I'm only getting 3 out of 4 fields. Any advise?

 

[Norie]

Can you post your current code?

 

[Littlemalky]

Yes, sorry.

<code>

Private Sub btnEXPORT_TEXT_FILE_Click()
    Dim rs As DAO.Recordset, strRec As String, j As Integer
    Set rs = CurrentDb.OpenRecordset("UNION_ADDITIONS_ACCRUALS_LOAD_FILES")
    Open "[URL="file://\\cobalt\public\SCC\CAPEX\Test\UNION_ADDITIONS_ACCRUALS_LOAD_FILES.txt"]...\Test\UNION_ADDITIONS_ACCRUALS_LOAD_FILES.txt[/URL]" For Output As #1
    Do Until rs.EOF
        For j = 1 To rs.Fields.Count - 1
            If strRec = "" Then
            strRec = rs(j) & vbTab
            Else
            strRec = strRec & rs(j) & vbTab
            End If
        Next
        Print #1, strRec
        rs.MoveNext
        strRec = ""
Loop
Close #1
    MsgBox "Export Complete!"
End Sub

<code></code>
</code>