Insert blank records in a table or export #2

[AlanM]

John471 recently gave me a nice piece of code to insert blank records in an export file. (refer my previous thread)

Unfortunately this uses the WRITE # function, which I believe will only write a CSV file.

It's unfortunate because the application that will use the exported file will only accept tab delimited and without field delimiters (TSV) format

I know the Access TransferText function will export in TSV, but then I lose the function of inserting a blank record into the export file when the customer changes, which is the other requirment of the recipient application.

Anyone have other suggestions?

Many thanks

Alan M

 

[john471]

Use Microsoft Scripting Runtime DLL

Alan,

Ensure your module references the Microsoft Scripting Runtime DLL, then adapt the following code... (you will likely notice the similararities to my previous answer ).

Sub WriteOutPutFileTSV()
    Const cszOutPutFilePathAndName As String = "C:\MyTSVOutPut.tsv"
    Dim szSQL As String
    Dim rs As Recordset
    Dim szLastCustomerID As String  ' Yes, string, but that is how it is in Northwind
    
    'Ensure MicrosoftScripting Runtime is referenced
    Dim fso As New FileSystemObject
    Dim TSOut As TextStream
    
    
    szSQL = "SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.RequiredDate "
    szSQL = szSQL & "FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID "
    szSQL = szSQL & "ORDER BY Customers.CustomerID;"
    
    Set rs = CurrentDb.OpenRecordset(szSQL)
    If Not (rs.EOF And rs.BOF) Then
        'Open cszOutPutFilePathAndName For Output As #1    ' Open file for output.
        Set TSOut = fso.CreateTextFile(cszOutPutFilePathAndName, True, False)
        With rs
            .MoveFirst
            szLastCustomerID = .Fields("CustomerID").Value
            Do While Not .EOF
                If Not szLastCustomerID = .Fields("CustomerID").Value Then
                    'Write #1,   ' Write blank line.
                    TSOut.WriteBlankLines 1
                End If
                'Write #1, .Fields("CustomerID").Value, .Fields("CompanyName").Value, .Fields("OrderID").Value, Format(.Fields("RequiredDate").Value, "DD-MMM-YYYY")  ' Write comma-delimited data.
                TSOut.WriteLine .Fields("CustomerID").Value & vbTab & .Fields("CompanyName").Value & vbTab & .Fields("OrderID").Value & vbTab & Format(.Fields("RequiredDate").Value, "DD-MMM-YYYY")   ' Write Tab-delimited data.
                szLastCustomerID = .Fields("CustomerID").Value
                .MoveNext
            Loop 'while not .eof
        End With 'rs
        'Close #1
        TSOut.Close
    End If
    rs.Close
    Set rs = Nothing
    
End Sub

HTH

John.

 

[AlanM]

Hi John

Many thanks. This has solved my problem.

Cheers
Alan M