Using a web API to load data

[Anakardian]

I have been dabbling in excel to get data from an online database however I would like it to come straight into access instead of coming through excel.

In excel it is quite easy using a web query however I have not been able to find anything that can do the same in access.
Is it even possible or will I need to use excel as the stepping stone?

The data comes from this place: http://eve-marketdata.com/developers/item_orders2.php

 

[spikepl]

Follow the same recipe as given in #14 in http://www.access-programmers.co.uk/forums/showthread.php?t=205219

The returned text you'll have to parse yourself.
http://www.access-programmers.co.uk/forums/showthread.php?t=205219

 

[Anakardian]

thanks for the reference spikepl

I think I can see how it was done.
I will need to try it out tonight and see how it works.

 

[Anakardian]

This is nice.

After activating the reference for active x I just needed to change two lines to make it work:
Dim XMLHttpRequest As XMLHTTP
Set XMLHttpRequest = New MSXML2.XMLHTTP

was replaced by

Dim XMLHttpRequest As Object
Set XMLHttpRequest = CreateObject("MSXML2.XMLHTTP")

 

[Anakardian]

So I have been playing around with this challenge for a while now.

There appears to be two important things in play.

In one area you need to look at childnodes to get your data read correctly.
In the other area you need to look at the attribute of the childnodes. This is the method I ended up using as it is dictated by the structure of the xml.

Taking the return from this link http://api.eve-marketdata.com/api/item_orders2.xml?char_name=demo&buysell=b&type_ids=34,36 you will get a string.
If you save it the result will look like this in notepad: (just a few lines)

<?xml version="1.0" encoding="utf-8"?><emd version="2"><currentTime>2014-10-19 14:18:55</currentTime><result><rowset name="orders" key="orderID" columns="buysell,typeID,orderID,stationID,solarsystemID,regionID,price,volEntered,volRemaining,minVolume,issued,expires,created,range"><row buysell="b" typeID="34" orderID="3761749149" stationID="60003760" solarsystemID="30000142" regionID="10000002" price="5.04" volEntered="500000000" volRemaining="500000000" minVolume="1" range="-1" issued="2014-09-21 13:10:17" expires="2014-12-20 13:10:17" created="2014-10-19 09:36:57"/><row buysell="b" typeID="34" orderID="3703951703" stationID="60003760" solarsystemID="30000142" regionID="10000002" price="5.31" volEntered="200000000" volRemaining="200000000" minVolume="1" range="-1" issued="2014-08-10 21:16:59" expires="2014-11-08 21:16:59" created="2014-10-19 09:36:57"/><row buysell="b" typeID="34" orderID="3797716860" stationID="60003043" solarsystemID="30000163" regionID="10000002" price="5.65" volEntered="30000000" volRemaining="30000000" minVolume="1" range="-1" issued="2014-10-19 08:27:46" expires="2014-10-22 08:27:46" created="2014-10-19 09:36:57"/><row buysell="b" typeID="34" orderID="3776738425" stationID="60004369" solarsystemID="30000136" regionID="10000002" price="4.51" volEntered="10000000" volRemaining="9170344" minVolume="1" range="0" issued="2014-10-03 10:20:56" expires="2015-01-01 10:20:56" created="2014-10-19 09:36:57"/><row buysell="b" typeID="34" orderID="3793466019" stationID="60003760" solarsystemID="30000142" regionID="10000002" price="2.59" volEntered="1000000" volRemaining="1000000" minVolume="1" range="40" issued="2014-10-16 02:43:06" expires="2015-01-14 02:43:06" created="2014-10-19 09:36:57"/><row buysell="b" typeID="34" orderID="3769770757" stationID="60003760" solarsystemID="30000142" regionID="10000002" price="5.35" volEntered="200000000" volRemaining="200000000" minVolume="1" range="-1" issued="2014-09-27 14:57:29" expires="2014-12-26 14:57:29" created="2014-10-19 09:36:57"/><row buysell="b" typeID="34" orderID="3789380759" stationID="60003760" solarsystemID="30000142" regionID="10000002" price="1.65" volEntered="370000" volRemaining="370000" minVolume="1" range="40" issued="2014-10-12 22:20:28" expires="2015-01-10 22:20:28" created="2014-10-19 09:36:57"/><row buysell="b" typeID="34" orderID="3797611259" stationID="60004444" solarsystemID="30000181" regionID="10000002" price="5.52" volEntered="1500000" volRemaining="1500000" minVolume="1" range="-1" issued="2014-10-19 06:32:39" expires="2014-10-26 06:32:39" created="2014-10-19 09:36:57"/><row buysell="b" typeID="34" orderID="3795145537" stationID="60004237" solarsystemID="30000129" regionID="10000002" price="5.07" volEntered="220000000" volRemaining="218154321" minVolume="1" range="4" issued="2014-10-17 15:47:42" expires="2014-11-16 15:47:42" created="2014-10-19 09:36:57"/><row buysell="b" typeID="34" orderID="3777250836" stationID="60004222" solarsystemID="30000153" regionID="10000002" price="4.9" volEntered="20000000" volRemaining="15186994" minVolume="1" range="5" issued="2014-10-03 19:44:34" expires="2015-01-01 19:44:34" created="2014-10-19 09:36:57"/><row buysell="b" typeID="34" orderID="3764102507" stationID="60003760" solarsystemID="30000142" regionID="10000002" price="5.3" volEntered="500000000" volRemaining="500000000" minVolume="1" range="-1" issued="2014-09-23 05:23:54" expires="2014-12-22 05:23:54" created="2014-10-19 09:36:57"/><row buysell="b" typeID="34" orderID="3738286428" stationID="60003760" solarsystemID="30000142" regionID="10000002" price="5.18" volEntered="10000000" volRemaining="10000000" minVolume="1" range="-1" issued="2014-09-03 19:50:38" expires="2014-12-02 19:50:38" created="2014-10-19 09:36:57"/><row buysell="b" typeID="34" orderID="3774339844" stationID="60003760" solarsystemID="30000142" regionID="10000002" price="0.47" volEntered="10000" volRemaining="10000" minVolume="1" range="-1" issued="2014-10-01 07:02:31" expires="2014-10-31 07:02:31" created="2014-10-19 09:36:57"/><row buysell="b" typeID="34" orderID="3795731435" stationID="600

Opening it with a reader able to digest xml, it will look like this: (just a few lines)

<?xml version="1.0" encoding="UTF-8"?>

-<emd version="2">

<currentTime>2014-10-19 14:18:55</currentTime>


-<result>


-<rowset columns="buysell,typeID,orderID,stationID,solarsystemID,regionID,price,volEntered,volRemaining,minVolume,issued,expires,created,range" key="orderID" name="orders">

<row created="2014-10-19 09:36:57" expires="2014-12-20 13:10:17" issued="2014-09-21 13:10:17" range="-1" minVolume="1" volRemaining="500000000" volEntered="500000000" price="5.04" regionID="10000002" solarsystemID="30000142" stationID="60003760" orderID="3761749149" typeID="34" buysell="b"/>

<row created="2014-10-19 09:36:57" expires="2014-11-08 21:16:59" issued="2014-08-10 21:16:59" range="-1" minVolume="1" volRemaining="200000000" volEntered="200000000" price="5.31" regionID="10000002" solarsystemID="30000142" stationID="60003760" orderID="3703951703" typeID="34" buysell="b"/>

<row created="2014-10-19 09:36:57" expires="2014-10-22 08:27:46" issued="2014-10-19 08:27:46" range="-1" minVolume="1" volRemaining="30000000" volEntered="30000000" price="5.65" regionID="10000002" solarsystemID="30000163" stationID="60003043" orderID="3797716860" typeID="34" buysell="b"/>

<row created="2014-10-19 09:37:37" expires="2014-10-29 17:48:18" issued="2014-09-29 17:48:18" range="3" minVolume="1" volRemaining="985059" volEntered="1000000" price="58.6" regionID="10000002" solarsystemID="30000150" stationID="60004051" orderID="3772691577" typeID="36" buysell="b"/>

</rowset>

</result>

</emd>

To read it I used the following code:

Dim docXMLDOM As DOMDocument
    Dim i As Integer
    Dim n As IXMLDOMNode
    
    Set docXMLDOM = New DOMDocument
    
    docXMLDOM.loadXML (XMLHttpRequest.responseText)

   Dim dbOrders As DAO.Database
   Dim rstOrders As DAO.Recordset

   Set dbOrders = CurrentDb
   Set rstOrders = dbOrders.OpenRecordset("DSAOrder")


i = 0

For Each n In docXMLDOM.selectNodes("//rowset/row")
    'addnew record to recordset
   rstOrders.AddNew
   rstOrders("Buysell").Value = n.Attributes.getNamedItem("buysell").Text
   rstOrders("TypeID").Value = n.Attributes.getNamedItem("typeID").Text
   rstOrders("OrderID").Value = n.Attributes.getNamedItem("orderID").Text
   rstOrders("StationID").Value = n.Attributes.getNamedItem("stationID").Text
   rstOrders("SolarSystemID").Value = n.Attributes.getNamedItem("solarsystemID").Text
   rstOrders("RegionID").Value = n.Attributes.getNamedItem("regionID").Text
   rstOrders("Price").Value = n.Attributes.getNamedItem("price").Text
   rstOrders("VolEntered").Value = n.Attributes.getNamedItem("volEntered").Text
   rstOrders("VolRemaining").Value = n.Attributes.getNamedItem("volRemaining").Text
   rstOrders("MinVolume").Value = n.Attributes.getNamedItem("minVolume").Text
   rstOrders("Range").Value = n.Attributes.getNamedItem("range").Text
   rstOrders("Issued").Value = n.Attributes.getNamedItem("issued").Text
   rstOrders("Expires").Value = n.Attributes.getNamedItem("expires").Text
   rstOrders("Created").Value = n.Attributes.getNamedItem("created").Text
   rstOrders.Update
Next


i = i + 1
'Loop
    
    
    Set dbOrders = Nothing
    Set rstOrders = Nothing
    Set docXMLDOM = Nothing

What the code does is relatively simple.
The return xml string from the website is loaded into docXMLDOM.
As I want to put the return data into a table, I open the table and its content as a recordset.
To get each line of data loaded, I use the addnew and update commands.
Looping through the return string I can load all the data into a table and then continue in access.

 

[peskywinnets]

I'm trying to emulate the metjod (& code) above for processing XML, but I get a compile error with the arrowed bit...



It's probably obvious, but to my untrained eye...it ain't.

Top tips warmly received.

 

[CJ_London]

responding to a 16 month old thread is not the best way to get a solution - start a new thread.

but to answer

It's probably obvious, but to my untrained eye...it ain't.

Does your untrained eye read and understand the error message because that gives you a massive clue as to what is wrong

 

[peskywinnets]

Yes the thread is 16 months old, but by resurrecting it, at least you can see the context of what I'm trying to do (or are duplicate threads preferred?)

The error message says a lot, but I guess I'm not understanding the code (or that it presumably worked Anakardian)....my understanding was that the code takes XML sourced from the web & creates/populates a new table with it.

That was my (limited) understanding.

So the problematic line, the code is trying to open a database called dbOrders? What is the "DSAOrders" aspect here??

 

[CJ_London]

  trying to open a database called dbOrders?

where to you see the word 'database' in the error message?

and what do you think the code does?

and once you've answered that, you should be able to answer

What is the "DSAOrders" aspect here??

 

[peskywinnets]

Googling OpenRecordset() doesn't help me establish what the "DSAOrder" bit means/refers to?

Perhaps I've misunderstood what the code does...I had that the code was self sufficient (creating a new database from scratch & populating it with XML), but perhaps instead the code needs the pre existence of other tables to work?

FWIW, this is the final part of a self learning journey where I've changed some Excel VBA to work in Access, towards creating a signed URL & pulling in XML data from the net. But this latter part (creating a table & populating it with XML data) is totally new to me.

 

[Grumm]

You could make a new thread with just a link to this one and explain your problem.
Your problem has nothing in common with the original problem.

But in any case, you are correct about opening a database. (The currentDB in this case)
Now the trick you need is this :
When you don't know what a certain function does, you can right click on "OpenRecordset", go to definition and then click on the yellow "?" you see

This will give you more information than you ever need to solve the problem.

 

[peskywinnets]

You could make a new thread with just a link to this one and explain your problem.
Your problem has nothing in common with the original problem.

Hmm, the OP was wanting to read XML into a database. I am wanting to achieve the same so found this thread, I've followed his code (& example URL) but come up against a problem relating to it. Ok, that may be because I'm new to creating populating databases, but could it be that others following in my wake, will have the same problem? Context is important.

I realise you're trying to help (& thank you for taking the time to add your input), but I'm still none the wiser (right clicking on openrecordset didn't bring up any yellow exclamation mark!)

I really don't want to sound rude, but is it normal on a forum for someone to ask for information, but then have to cryptically solve clues?

When I see someone struggling on a forum, I normally chime in with the information he/she seeks....rather than dangle teasers.

Does your untrained eye read and understand the error message because that gives you a massive clue as to what is wrong

There's a fine line between being helpful & being patronising.

 

[Grumm]

Indeed I didn't provided a link : Here it is https://msdn.microsoft.com/en-us/library/office/ff820966.aspx

You need to click on definition first btw.
So basically the code is not working because it tries to open the table DSAOrders.
(And from what I understand is that you just use the code not the OP's database structure.) You will have to adapt the code to match your tables and fields.

I guess the reason why most people give clues is that we quickly see that you just copy past the code and then say it doesn't work without understanding what or why it doesn't work. (Or google the error you have)
I just try to post ways for you to get the correct answer so that you can use that knowledge for other futur problems.

 

[peskywinnets]

So basically the code is not working because it tries to open the table DSAOrders.

Thank you.

 

[CJ_London]

I had that the code was self sufficient (creating a new database from scratch & populating it with XML), but perhaps instead the code needs the pre existence of other tables to work?

you got there. Apologies for providing clues, but as Grumm says, you clearly do(did) not understand what the code is actually doing and asking a question where the error message is explicit and answers that question. I was just trying to get you to think about what the system is telling you.

 

[Anakardian]

Hehe, I had quite forgotten about this one.

The code I used was meant to fire some data off to a website and then parse the xml reply into a staging table.

The recordset is there because i looped through a number of datapoints for which a reply would be given.
This was provided by a query called DSAOrder.
There is nothing secret about code or indeed the website providing the data so spend the time to have a look at what is being provided and then look at the code again.
Comparing teh two will give a lot of understanding about how to work with that (in Access) strange data structure.

Once all the replies were in I did some further chewing on the data and so on.