Combining Records into a Single text file

[dccjr3927]

I am having trouble combining records for export to a text file (Shipping report). There are 3 parts to the desired file: header section, detail section, and trailer section. The sections begin with a single character field (H, D, and T). There is only one header and trailer per file, but there can be multiple Detail records in between them. The text file is a fixed width format. Header has 7 fields, Detail has 14 fields, and Trailer has 7 fields. This file is being imported into an IBM AS400 system, so I am bound by that systems format requirements.



So, I have a single table that contains all of the fields for the sections. I cannot figure out how to query the header, detail(s), and trailer sections (separate queries in order to not get the fields that do not belong in the respective sections) into a single recordset (or combine 3 recordsets with different numbers of fields in each in the proper sequence).

I can do this for one section without much problem, but do not want to have to open the 3 in a text editor and manually copy and past each section into position. I am not sure in I can accomplish this in Access alone. I would not be opposed to exporting to an Excel worksheet then exporting from there. I am just not sure if I can control Excel via Access VBA, or whether I would have to do create some code in Excel as well. My goal is to make this as not technical as possible, as the user(s) do not have much Access knowledge, other than clicking a button on a form and filling in boxes.

Any help would be greatly appreciated.

[orange]

see this thread https://www.accessforums.net/showthread.php?t=43682

Review post # 3 to append the 3 files to a single file.

[dccjr3927]

Thank you. I am going to try this.

[accesstos]

Also, you can use the GetString() method of ADODB.Recordset as seems in the sample bellow:

Function RecordsInText(strSQL As String) As String
    'Need to set a reference to Microsoft ActiveX Data Objects x.x Library.
    Dim rs As ADODB.Recordset
    
    Set rs = New ADODB.Recordset
    rs.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
        If rs.RecordCount > 0 Then
        RecordsInText = rs.GetString(, , vbTab, vbCrLf)
    End If
    
    rs.Close
    Set rs = Nothing
End Function

Sub BuildTextFile()
    Dim strText As String
    Dim f As Long
    
    strText = RecordsInText("SELECT Field1, Field2 FROM tblTest") & String(100, "~") & vbCrLf
    strText = strText & RecordsInText("SELECT Field3, Field4, Field5, Field6 FROM tblTest") & String(100, "~") & vbCrLf
    strText = strText & RecordsInText("SELECT Field7, Field8, Field9, Field10 FROM tblTest")
    
    f = FreeFile
    Open "C:\TestFile.txt" For Output As #f
    Print #f, strText
    Close #f
    
End Sub

Replace the text in red with the actual names of your table and test it.