36. (Advanced Programming In Access 2013) Importing XML To A Table From A RESTful Web API

[@ricardofilipegomes4396]

this means a lot to me a started learning how to program with you ^^ and to love programming!! i will not take a programming degree because i wanna learn more stuff! thanks man! best programming videos ever!!!

[@swapnilwankhede3440]

Hi Steve Excellent Tutorials, Thanks a lot Steve for uploading this videos, it’s really helpful, never found such good videos.

[@not2busy]

Thank You! I finally found what I've been looking for in this video. I managed to get my data straight away. Thanks very much!

[@ismgermany3088]

Thanks Steve for that great and helpful video! Very nice

[@jako265]

Very good Video!!!! Great work talking like a Professor...... Waiting for importing json table..... lesson!!!

[@ProgrammingMadeEZ]

+jako26Thank you jako

[@jako265]

+Programming I have an isue when importing the xml. An object name where i get the xml has a name with .(dot) for example and acces does not allow the import... is there a way to bypass that? Thank you

[@RobertHaber137]

+Programming when my XML file imports, it only brings in the very last record. Example: table should be column A,B,C and records1-20. The only thing it imports is A20. Thoughts?

[@ProgrammingMadeEZ]

Honestly I wouldn't be able to solve the problem without both the database and the data source. Without more detail there's no way for me to know why.

[@RobertHaber137]

+Programming Data source is here: www.zipcodeapi.com/API#radius code from vid, Dim reader As New XMLHTTP60 Dim doc As DOMDocument60 reader.Open "GET", "www.zipcodeapi.com/rest/6oC6Y2CcAlfMeWRx0vbhWa8mT0N0d5ekuRqtU3GuoVhCR8aRDl9AwhXqiLqvRFQl/radius.xml/89148/500/mile", False reader.setRequestHeader "Accept", "application/xml" reader.send Do Until reader.ReadyState = 4 DoEvents Loop If reader.status = 200 Then Set doc = reader.responseXML doc.Save "C:\Users obert.haber\Desktop\ZipCode.xml" Application.ImportXML "C:\Users obert.haber\Desktop\ZipCode.xml", acAppendData Else MsgBox "Unable to import data" End If The append table is Zip_Codes. Columns are zip_code,distance,city,state I have nothing else in the database, as I'm testing your solution. Thanks!!

[@alejandrozapata2665]

THE BEST TRAINING ON ACCESS I'VE EVER SEEN..... THANKS A MILLION. QUESTION: What system did you use to record your video ? Thanks.

[@ProgrammingMadeEZ]

+Alejandro Zapata I eventually ended up using APowersoft free recorder.

[@WebTeam-wl9oo]

Thanks Steve great video. I've followed your video and I'm making a request to Google Books using an ISBN. I'm getting a runtime error 31593 as it appears the XML file returned from Google is empty. I'm using the "reader.setRequestHeader "Accept", "application/xml"" line and saving the file but I get the error on the import. When I open the XML file it is empty. Are you aware if Google supports XML responses? I've also tried using the JSON VBA code but get an error 13 (type mismatch) as there is a numeric value returned in the JSON file that isn't wrapped in quotes.

[@jamilshehadeh]

Thank you steven, but can you tell how post xml data on web api

[@redhahamad6125]

Thanks for your video. Actually I am interested in such things that I am trying to send data from NS Access form to data entry form in AS400. Any idea how can I achieve this? Thanks in advance!

[@scottcarman62]

Steven, as always you do great work. So I am just starting to get into bringing data from our server via XML. I followed the code you wrote in this video letter for letter. When I get to the part "doc.save "pathName here" I am getting an run time error '-2147024891 (80070005) saying that "Access is Denied. Any ideas on how to solve this?

[@rohitharidas91]

Hi Steve, long time viewer here :) , I tried doing this using the Steam API. The XML file gets downloaded perfectly, but during import, I get the error "DTD is prohibited". It works perfectly with other APIs but not the steam API. After a bit of searching, I found that the reason was caused by the second line in the XML . Removing this manually solves the issue. But how fix do this through access automatically?

[@mohammedfarhat1444]

Thank you very much for this wonderful series. I have question , in the import statement there is no tabel name destination How can i specify a tabel name in that statement Application.Importxml "c: orthwind\contact.xml", acStructureAndData

[@ProgrammingMadeEZ]

You can't via the command "Importxml". But once the data is imported into it's own table, you can insert it into the table you want with an insert query.

[@mohammedfarhat1444]

Yes of course and the table name is the name of XML file . Thank you for your clarification

[@JuanMiguelAltube]

Hey man, nice video. it helped me get closer to what I'm trying to achieve. NOw how about writing data back to the API?. I mean, update/insert the records in access and impact them in the web

[@ProgrammingMadeEZ]

That entirely depends on the API you're talking to, but you should be able to use the same xmlhttp object.

[@JuanMiguelAltube]

Thanks! one more pls.. say I manage to write the code to impact changes on the API. Will I still be able to use the GUI (The grid). So if I add a new row, access triggers the piece of code that impacts on the API

[@nonsookafor722]

hello.... thanks for all the videos.... I need your help... what is the vba method to insert image or ole objects on a form, by clicking open and selecting pictures

[@ProgrammingMadeEZ]

+nonso okafor Did you watch my beginner series? I cover how to do this there.

[@nonsookafor722]

yes I did watch all your series, i did not see the video on that...I want to create a form with vba code where you click on a button and get to search for the images to upload and also to put multiple images for more than one record

[@pjamiesonnz]

Thank you so much for this Steve this has been very useful. One issue I'm having is that every time the code runs, it adds a new table (table1, table2 etc) rather than replacing the existing table which is what I'm desperately needing. Can you or anyone else please tell me what I'm doing wrong?

[@ProgrammingMadeEZ]

You need to drop the original first. Just run the SQL Query "DROP Table "

[@pjamiesonnz]

Thanks, I've done a delete * and then acAppendData.

[@pjamiesonnz]

My biggest issue now is that I have 1 call (the most important), but it returns in two tables that have no common record to join on. Do you think a direct JSON call instead of XML would fix this. Or should I be doing some find and replacing on the xml file before importing?

[@ProgrammingMadeEZ]

No. I think you're getting confused as to what XML and JSON are doing.

[@pjamiesonnz]

Sorry, that really doesn't help me understand what the issue is.

[@jaikushwaha12]

hello my friend i made a form in ms access and now i want to creat a submit button on it. if someone will click that button after it no one can change the fields of the form except admin. i also want to make a edit button on the same form that if admin will click it can make changes in the form after submission but the edit button cant be seen by others who are working on the same form except admin. help me

[@ProgrammingMadeEZ]

+jai kushwaha Change the "AllowEdits" property of the form to false when it's complete, and true when the data is incomplete or new.

[@wrayman63]

Using Access 2016, this Database (36) has a compile error: "Compile Error, Can't find project or Library on this line Dim reader As New XMLHTTP60

[@ProgrammingMadeEZ]

You need to add it to your references.

[@wrayman63]

I did, or at least I think I did......

[@wrayman63]

I assume you mean Microsoft XML, V6.0

[@ProgrammingMadeEZ]

wrayman63 Yes.

[@wrayman63]

Yep, Tools>References>Available References>Micrsoft XML, v6.0 is checked, Am I missing something?

[@jaikushwaha12]

thank you dear friend one more thing i want to ask how to add more references in ms? I am facing continue a problem with an event i make in ms access form. if you will provide me your email address i can send you the screenshot of the problem which is like Something wrong with Active X control.

[@ProgrammingMadeEZ]

+jai kushwaha I'm afraid I cannot assist people with their specific databases. When I first started this series it was my intent to do so, but I realized that contractually I cannot do work that would be in competition with the company I work for. Unless you are willing to pay the company for my time I cannot help you on your specific issue.

[@jaikushwaha12]

ok dont worry thanks to reply