Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    18

    Unanswered: WinHTTPRequest usage question

    Hi,

    I'm really in uncharted territory so please bear with me. I have an application which compiles approximately 6,000 lines of XML instructions and exports them as an XML file. This much is functional. I have found some code that would instead allow me to upload the XML code to its destination using WinHttpRequest.

    I can make this code work with a short XML file written into a variable within VBA code, but this amounts to about 12 lines. The lines of code I need are put together into table records, in rows, and again number about 6,000. What I would like to do is somehow take the rows of XML instruction from a table and POST my 'payload' XML file using winHttpRequest.

    Maybe its as simple as this: how do I take 6,000 rows of a table and write them into a single variable (or multiple variables) in VBA so that the script I have can deliver it to a secured website. If I could figure out how to do that I think I'd be good to go. Prior to this if I needed to harvest data from a table I simply used dLookup but this is a bit larger (obviously).

    Sorry if this is a novice skill - I'm self taught.

    Thanks in advance!

    Flipper
    "Don't you hate Perry's wife?"

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can open a RecordSet on the table and loop through it, from the first to the last line:
    Code:
    Function LoopThroughATable(ByVal TableName As String)
    
        Dim rst As DAO.Recordset
        
        Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
        With rst
            Do Until .EOF
            
                ' Do something with the current line
                '
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Function
    If you want to store the lines into an array you can use the GetRows method of the RecordSet:
    Code:
    Function TableToArray(ByVal TableName As String)
    
        Dim rst As DAO.Recordset
        Dim strLines As Variant
        Dim lngLineCount As Long
        
        
        Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
        With rst
            .MoveLast
            lngLineCount = .RecordCount
            .MoveFirst
            strLines = .GetRows(lngLineCount - 1)
            
            ' strLines is now an array (0 to lngLineCount-1)containing the lines from the table
            .Close
        End With
        Set rst = Nothing
        
    End Function
    You can also concatenate the lines (there is a limit to the length of a string, though):
    Code:
    Function ConcatenateTableLines(ByVal TableName As String) As String
    
        Dim rst As DAO.Recordset
        Dim strRetVal As String
        
        Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
        With rst
            Do Until .EOF       
                strRetVal = strRetVal & !<Column> ' <column> is the name of the column into the table
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        ConcatenateTableLines = strRetVal
        
    End Function
    Have a nice day!

  3. #3
    Join Date
    Oct 2003
    Posts
    18
    Thanks! This worked but for some reason it takes a really long time to run.

    I'm still scratching my head. I appreciate the info!

    Flipper
    "Don't you hate Perry's wife?"

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    P.S. Looping through a Recordset can be long, specially if there are many rows to process. In a SQL Server databse you could probably use a recursive stored procedure to get the string and that would be faster, but everything would be faster with a SQL Server anyway!
    Have a nice day!

Tags for this Thread

Posting Permissions

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