Writing to Tables

I've figured out how to get all of the data I need from Bloomberg to print to the Immediate Window.  However, I don't know how to write it to a table instead.   I  want to have a table with Tickers down the first columm and data fields for those tickers going across to the right like so:

 Ticker     Stock_Price       Shares_Out
AA             12.22               1021.214            

There are about 270 tickers and about 20 fields for each ticker.  The way the data comes from Bloomberg, I get a "security" which is the ticker and then I loop through to get the field names and values.  It would be safest if I could read in the Bloomberg field name and save it under that field name in my Access table (in case some fields are missing) but I don't know how to make the Access field name a variable equal to the Bloomberg field name.

Does anyone know how to do this?

Thanks for any help!

Private session As blpapicomLib.session
Private refdataservice As blpapicomLib.Service
Private req As blpapicomLib.Request
Private cid As blpapicomLib.CorrelationId

Public Sub MakeRequest()

    Set session = New blpapicomLib.session
    session.Start
   
    session.OpenService ("//blp/refdata")
    Set refdataservice = session.GetService("//blp/refdata")
    Set req = refdataservice.CreateRequest("ReferenceDataRequest")
    
' Request Tickers
    
    Dim CurrTicker As String
    Dim rs As Recordset
    
    Set rs = CurrentDb.OpenRecordset("Credit_Universe")
    
    rs.MoveFirst
    
Do While Not rs.EOF
    CurrTicker = rs.fields("[Ticker]")
    req.GetElement("securities").AppendValue CurrTicker & " US Equity"
    rs.MoveNext
Loop
    
' Request Fields
    
    Dim CurrItem As String
    Dim rsItem As Recordset
    
    Set rsItem = CurrentDb.OpenRecordset("Corp_Info_Fields")
    
    rsItem.MoveFirst
    
Do While Not rsItem.EOF
    CurrItem = rsItem.fields("[Data_Item]")
    req.GetElement("fields").AppendValue CurrItem
    rsItem.MoveNext
Loop
        
' Get Response Messages from Bloomberg

Dim cid As blpapicomLib.CorrelationId
Set cid = session.SendRequest(req)

Dim ContinueToLoop As Boolean
ContinueToLoop = True

Dim rsCorpInfo As Recordset
Set rsCorpInfo = CurrentDb.OpenRecordset("Corporate_Info")
rsCorpInfo.AddNew
    
Do While ContinueToLoop
    Dim eventObj As blpapicomLib.Event
    Set eventObj = session.NextEvent
        
    If eventObj.EventType = PARTIAL_RESPONSE Or eventObj.EventType = RESPONSE Then
        Dim it As blpapicomLib.MessageIterator
        Set it = eventObj.CreateMessageIterator()
        
        Do While it.Next()
            Dim msg As Message
            Set msg = it.Message
            
            Dim numSecurities As Integer
            numSecurities = msg.GetElement("securityData").NumValues
            Dim i As Integer
            
            For i = 0 To numSecurities - 1
                Dim security As Element
                Set security = msg.GetElement("securityData").GetValue(i)
                Debug.Print security.GetElement("security").Value
                rsCorpInfo!Ticker = security.GetElement("security").Value
                rsCorpInfo.Update
                Dim fields As Element
                Set fields = security.GetElement("fieldData")
                Dim a As Integer
                Dim numFields As Integer
                numFields = fields.NumElements
                
                For a = 0 To numFields - 1
                    fldplace = a
                    Dim field As Element
                    Set field = fields.GetElement(a)
                    Debug.Print field.Name & " = " & field.Value
                    rsCorpInfo!field.Name = field.Value
                Next
                currentrow = currentrow + 1
            Next
        Loop
        If eventObj.EventType = RESPONSE Then
            ContinueToLoop = False ' Exit after processing the RESPONSE messages
        End If
    End If
Loop

End Sub

Select allOpen in new window