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?
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")
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.
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.
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??
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.
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.
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.
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.
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.
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.
. 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.
