Creating a text file with Tab-Separated fields

[jonrmoore]

I have a customer database and a table called accountInfo which contains addressing fields. I need to create an ascii text file that has 3 fields on each line. Those are email_address, first_name, and last_name. In order for this file to work with an email program, I must have the fields separated by tabs, not commas. Can anyone show me how to do this? Thanks.

[brucevde]

Populate an ADO recordset and dump it to the file using the GetString method.

Private Sub Command1_Click()
    Dim db As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim lngFile As Long
    
    Set db = New ADODB.Connection
    db.Open "provider=microsoft.jet.oledb.4.0;data source=m:\testing\nwind2.mdb"
    
    Set rs = db.Execute("Select CustomerId, CompanyName, Country From Customers")
    
    lngFile = FreeFile
    
    Open "C:\Customers.dat" For Binary As #lngFile
    Put #lngFile, , rs.GetString(adClipString, , vbTab, vbNewLine)
    Close #lngFile
    
    rs.Close
    db.Close
End Sub

[jonrmoore]

Thanks so much. This program uses DAO instead of ADO. What would change?

[brucevde]

You need to iterate through the entire recordset.

Private Sub Command2_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim lngFile As Long
    Dim aData As Variant
    
    Set db = DAO.DBEngine.OpenDatabase("m:\testing\nwind2.mdb")
        
    Set rs = db.OpenRecordset("Select CustomerId, CompanyName, Country From Customers", dbOpenForwardOnly, dbReadOnly)
    
    lngFile = FreeFile
    
    Open "C:\Customers.dat" For Output As #lngFile
    
    Do Until rs.EOF
        Print #lngFile, rs.Fields(0); vbTab; rs.Fields(1).Value; vbTab; rs.Fields(2).Value
        rs.MoveNext
    Loop

    Close #lngFile
    
    rs.Close
    db.Close
End Sub

[jonrmoore]

This is perfect, thanks.