access-vba Create Text Delimited File in Access Vba Example:

Example

Private Sub this()
    Dim rs As DAO.Recordset
    Dim q%: Dim z%
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Invoice;")

    With rs
        rs.MoveLast
        q = rs.RecordCount
        rs.MoveFirst
        z = rs.Fields.Count
    End With
    
    Dim aRR As Variant
    
    aRR = rs.GetRows(q)
    
    Dim i%: Dim j%: Dim counter#: Dim Sep$: Dim Wholeline$: Dim NewTextFile$: Dim path$: Dim fileNameV$
    
    Sep = "|"
    path = Environ("USERPROFILE") & "\Desktop" & "\"
    fileNameV = "Text007.txt"
    NewTextFile = path & fileNameV
    Open NewTextFile For Output As #2
    For j = LBound(aRR, 2) To UBound(aRR, 2)
        For i = LBound(aRR, 1) To UBound(aRR, 1)
            Wholeline = Wholeline & aRR(i, j) & Sep
        Next i
        Print #2, Wholeline
        Wholeline = vbNullString
    Next j
    Close #2

    rs.Close
    Set rs = Nothing
    Erase aRR
    
End Sub