36. (Advanced Programming In Access 2013) Importing XML To A Table From A RESTful Web API
HTML-код
- Опубликовано: 30 июл 2024
- Importing XML To A Table From A RESTful Web API video in the "Advanced Programming in Microsoft Access 2013" series hosted by Steve Bishop. In this free advanced video tutorial series Steve will be going over Microsoft SQL Server installation, Database Migration, creating a better User Interface, using external data sources, complex Visual Basic For Applications (VBA) concepts and distributing your application.
Click here for the full playlist of "Advanced Programming in Access 2013":
• 1. (Advanced Programmi...
Click here for the Work Files of this series:
github.com/Xipooo/AdvancedPro...
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!!!
Hi Steve
Excellent Tutorials, Thanks a lot Steve for uploading this videos, it’s really helpful, never found such good videos.
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!
Thanks Steve for that great and helpful video! Very nice
THE BEST TRAINING ON ACCESS I'VE EVER SEEN..... THANKS A MILLION. QUESTION: What system did you use to record your video ? Thanks.
+Alejandro Zapata I eventually ended up using APowersoft free recorder.
Very good Video!!!! Great work talking like a Professor...... Waiting for importing json table..... lesson!!!
+jako26Thank you jako
+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
+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?
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.
+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!!
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!
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
That entirely depends on the API you're talking to, but you should be able to use the same xmlhttp object.
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
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?
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?
You need to drop the original first. Just run the SQL Query "DROP Table "
Thanks, I've done a delete * and then acAppendData.
Thank you steven, but can you tell how post xml data on web api
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
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.
Yes of course and the table name is the name of XML file .
Thank you for your clarification
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
+nonso okafor Did you watch my beginner series? I cover how to do this there.
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
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?
No. I think you're getting confused as to what XML and JSON are doing.
Sorry, that really doesn't help me understand what the issue is.
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
+jai kushwaha Change the "AllowEdits" property of the form to false when it's complete, and true when the data is incomplete or new.
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?
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.
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
You need to add it to your references.
I did, or at least I think I did......
I assume you mean Microsoft XML, V6.0
wrayman63 Yes.
Yep, Tools>References>Available References>Micrsoft XML, v6.0 is checked, Am I missing something?
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.
+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.
ok dont worry thanks to reply