Export Access Tables to "|" delimited text files

[Stella]

Help!!! Am extremely new to VBA. Want to set up code behind a button that will prompt the user for a table name(source of data) and file name(end result of export) and then export the data into a pipe delimited text file.

Appreciate any help!!!

[CaptainD]

This code works in VB, you should be able to adapt it to Access VBA
Notes: Exportpath is a variable that holds the drive, folder and file name.
(C:\ExportFolder\ExportFile.txt)
GetADORSP is a funtion that passes back the recordset, you just need to great a recordset how ever your program is doing it.

Private Sub cmdExport_Click()
On Error GoTo ErrHandler
Dim rs As ADODB.Recordset
Dim strSql As String
Const SW_SHOWNORMAL = 1
 
'Clear the text file
Open ExportPath For Output As #1
Print #1, ""
Close #1
If lstPersonnel.SelCount = 0 Then 'Get all records
    iRow = 1
    For i = 0 To lstPersonnel.ListCount - 1
 
        strSql = "EXEC spCFD_CJAC_Export @dStartDate = '" & dtpStartDate.Value & _
        "', @dEndDate = '" & dtpEndDate.Value & "', @iCityIDNum = " _
        & lstPersonnel.ItemData(i)
        'MsgBox strSql
        Set rs = GetADORSP(strSql)
    Open ExportPath For Append As #1
    Do While Not rs.eof
        Print #1, rs.GetString(, , "|", vbCrLf, "");
 
    Loop
    Close #1
    Set rs = Nothing
    Next i
Else 'Get only the selected personnel data
 
        iRow = 1
        For i = 0 To lstPersonnel.ListCount - 1
 
            If lstPersonnel.Selected(i) = True Then
 
                strSql = "EXEC spCFD_CJAC_Export @dStartDate = '" & dtpStartDate.Value & _
                "', @dEndDate = '" & dtpEndDate.Value & "', @iCityIDNum = " _
                & lstPersonnel.ItemData(i)
                Set rs = GetADORSP(strSql)
                Open ExportPath For Append As #1
 
                Do While Not rs.eof
                    Print #1, rs.GetString(, , "|", vbCrLf, "");
 
                Loop
                    Close #1
            End If
                Set rs = Nothing
 
        Next i
 
 
End If
Set rs = Nothing
'Check to see if they want to review the text file before sending it out.
If MsgBox("Would you like to review the data file to be sent?", vbYesNo, "Attention") = vbYes Then
    Call ShellExecute(Me.hWnd, "open", ExportPath, "", 0, SW_SHOWNORMAL)
End If
Exit Sub
ErrHandler:
    MsgBox "Error exporting data. Error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
Set rs = Nothing
Close #1
End Sub
 

If you need more help, let the group know.