Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167

    Unanswered: Changing recordset to delimited string expression

    I'm trying to convert a query's results into a tab-delimited string expression in VBA. I used OpenRecordset to open the query but I have not seen anything on converting a Recordset to a tab-delimited string variable. The reason I need this is an API requires a tables I upload to be included in the body of my XML post as a tab-delimited string. I had a similar issue with this API a while back but in that case I had to import an XML response text as a table.

    Code:
    Public Function Test()
        
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("CWS_01Fulfillment_Found", dbOpenDynaset)
    rs.Close
    
    End Function
    I really appreciate the help I've been getting on this project so far. Sorry for the constant questions, I'm learning but not as fast as I'd like. Thanks again, Joshua

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    See Allen Browne's website for how to loop through a DAO recordset.
    Me.Geek = True

  3. #3
    Join Date
    May 2009
    Posts
    258
    The best option would be to use ADO and the GetString method, similar to the following:
    Code:
    Dim cnn As ADODB.Connection, rst as ADODB.Recordset
    Dim strTable as String
    Set cnn = CurrentProject.Connection
    
    rst.Open "CWS_01Fulfillment_Found", cnn
    If Not rst.EOF Then strTable = rst.GetString(, , ",", ",")
    Regards,

    Ax

  4. #4
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Thanks, that worked, just had to add headers and change a couple minor things. Thanks again, Joshua

  5. #5
    Join Date
    May 2009
    Posts
    258
    Glad to hear it, thanks for letting us know!

  6. #6
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Just in case anyone encounters this in the future, here's how I ended up turning a table/query into a tab delimited text string. Thanks for the help guys I was lost on this one!

    Table/Query has 8 columns, had to add the headers manually with tabs in between and carriage return at end.

    Code:
    Public Function Test()
    
    'THIS TURNS A TABLE OR QUERY INTO A TAB DELIMITED TEXT STRING
    
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strTable As String
    Set cnn = CurrentProject.Connection
    Dim strSource As String
    strSource = "SELECT * FROM CWS_01Fulfillment_Found;"
    rst.Open strSource, cnn, adOpenDynamic
    
    If Not rst.EOF Then strTable = rst.GetString()
    strTableWHed = "order-id" & Chr(9) & "order-item-id" & _
        Chr(9) & "quantity" & Chr(9) & "ship-date" & _
        Chr(9) & "carrier-code" & Chr(9) & "carrier-name" & _
        Chr(9) & "tracking-number" & Chr(9) & "ship-method" & _
        Chr(13) & strTable
    
    MsgBox strTableWHed 
    
    rst.Close
    
    End Function
    Last edited by scrtchmstj; 08-20-09 at 10:24.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •