Error 3828 when creating a CSV from a query (1 Viewer)

[theDBguy]

Could you help me with where I put that and when I call it?

You would put the function in a standard module and call it like how @Gasman showed in post #31.

Set rs = fDAOGenericRst(SourceName, dbOpenSnapshot)

 

[arnelgp]

this will handle your Parameter, change the code to this one:

'=============================================
' Export Table or Query to CSV using raw file I/O
'=============================================

Public Sub ExportToCSVRaw(ByVal SourceName As String, Optional ByVal OutputPath As String = "")
' SourceName:       the name of table/query to export
' OutputPath:       the path + filename to export to
    Dim rs As DAO.Recordset
    Dim f As Integer
    Dim i As Integer
    Dim lineOut As String
    
    Set rs = RecordsetOfQuery(SourceName)
    
    If rs.EOF Then
        MsgBox "No records found in " & SourceName, vbInformation
        rs.Close
        Exit Sub
    End If
    
    ' if no OutputPath is passed, we save it
    ' to MyDocument with same name as the SourceName (.csv)
    If Len(OutputPath) = 0 Then
        OutputPath = Environ$("Userprofile") & "\Documents\" & SourceName & ".csv"
    End If
    
    ' Open file for writing
    f = FreeFile
    Open OutputPath For Output As #f
    
    '--- Write header line ---
    For i = 0 To rs.Fields.Count - 1
        lineOut = lineOut & QuoteCSV(rs.Fields(i).Name)
        If i < rs.Fields.Count - 1 Then lineOut = lineOut & ","
    Next i
    Print #f, lineOut
    
    '--- Write data lines ---
    Do While Not rs.EOF
        lineOut = ""
        For i = 0 To rs.Fields.Count - 1
            lineOut = lineOut & QuoteCSV(Nz(rs.Fields(i).Value, ""))
            If i < rs.Fields.Count - 1 Then lineOut = lineOut & ","
        Next i
        Print #f, lineOut
        rs.MoveNext
    Loop
    
    '--- Clean up ---
    Close #f
    rs.Close
    Set rs = Nothing
    
    MsgBox "Export complete: " & OutputPath, vbInformation
End Sub

'=============================================
' Helper: Quote and escape text for CSV
'=============================================
Private Function QuoteCSV(ByVal txt As String) As String
    Dim s As String
    s = CStr(txt)
    s = Replace(s, """", """""")  ' Escape quotes
    QuoteCSV = """" & s & """"
End Function


Function RecordsetOfQuery(ByVal queryName As String) As DAO.Recordset
    Dim i As Integer
    With CurrentDb.QueryDefs(queryName)
        For i = 0 To .Parameters.Count - 1
            .Parameters(i) = Eval(.Parameters(i).Name)
        Next
        Set RecordsetOfQuery = .OpenRecordset
        .Close
    End With
End Function

 

[andymartin3186]

I didn't get much further with this, but had a light bulb moment late last night.
I was running the code on a job by job level, to generate a single line csv to import to xero, meaning if I had 5 jobs to invoice I would have to go into each job and run the function.
I knew it worked when not given the jobID criteria, so instead I now have a button on my main menu that runs the function, and have modified the query to only show jobs which are status "for invoicing", meaning the csv now contains all jobs to invoice, and I can then dump them straight into xero. I'm now going to create an update query so that when the above function runs, it then runs an update query to change the status to invoiced after creating the csv.
Many thanks for your help, whilst I didn't get it to work, this other approach works better