How to Use a Web-Based Weather API To Pull Current Weather Data into Microsoft Access
In today's video, we're going to talk about how to use a web-based weather API to pull current weather data into your Microsoft Access database. This is going to be both an expert and a developer video. I'm going to show you a developer method using an API call that is a lot more powerful, and you can get data results like this very easily.
What is an API? It's an application programming interface. That's simply a fancy way of saying you can ask somebody else to do something for you. Like a WinAPI, you can ask Windows to do something or give you some information. A web-based API is a web server that you can say, hey, give me some information, like what's the current temperature. So that's really all an API does, and there's tons of them, there's millions of them.
All right, today's question comes from Jeffrey in Linwood, Washington, one of my Silver members and an Access Expert student. Jeffrey says, I'm taking your Access Expert 24 class and in there you give a link to an RSS weather feed from Weather Underground. That feed no longer exists. Are there any other sites that you can recommend that still work?
Yes, Jeffrey, unfortunately, that class is about seven years old, and as always when dealing with websites, they come and go, they change things. So unfortunately, Weather Underground's API doesn't work anymore, but a quick little Google search shows AccuWeather has an API, and you just send it your zip code and it will tell you the weather conditions. You can do pretty much exactly what I show in that video.
Let me walk you through it real quick. Take any database, this is my TechHelp free template, you can grab a copy of this off my website if you want to. Copy that URL that I gave you to your clipboard. This guy. Yeah, it's a PowerPoint slide. Let me copy that. Copy, control C. Now in Access, we're going to go to External Data, New Data, From File, and then pick XML file. I know it's a URL, it's not a file, but just pick XML file. Now you're going to paste that URL in here. Change your zip code obviously to whatever you want. If you're out of the US, I'm not exactly sure what you do, there's instructions on their website. Hit OK. Now it's going to go out, it's going to find this table with some other stuff in it. You want to make sure you import the structure and the data. Hit OK. All right. Save the import steps if you want. I've got whole separate videos on saving import steps and doing imports in general.
All right. Now you're going to get some tables imported over here. There's channel, there's image, what you're looking for is item right here. And there's your data. All right. There are some links in here to give you direct links to that stuff. There's a description right here. All right so you can pull this stuff out of this table just like you would any normal data. So if you want to learn more about this I cover it in a lot more detail in my Access Expert level 24 class. It's all the same from this point it's just the URL is different from the one that I use in this class. I also cover other things like setting up an SQL server database online. There's all kinds of cool stuff covered in this. I'll put a link to it down below.
Now this is the lesson for those of you who aren't programmers and don't want to learn VBA, but there's a more powerful method if you use a little bit of VBA. Now if you've never done any VBA programming before and you want to learn, this video will get you started. It's about 20 minutes long. It teaches you everything you need to know. After that, go watch my Web API video. This teaches you how the code works that can have Access, go out to the internet, connect to an API server, and bring back some information.
In this particular video, I show you how to get the current date and time from an internet time server. And it'll just bring back the current date and time, and that will prevent your users from, like, changing the system clock on their computer to act like they logged in to work an hour earlier. Okay? Okay. So go watch this. And also go watch my Find Between video. I build something called the Find Between function. Basically, when these internet servers give you data, they're going to give you a whole bunch of stuff. All kinds of stuff formatted really weird, and we can use my Find Between function to very easily pull out a bit of information. Like right here, we can very easily pull out the timestamp or the date using the Find Between. So go watch these. These are all free videos. They're on my website. They're on my YouTube channel. Go watch those and then come on back.
Okay, now on my website I am going to use the database that we built in the Web API video because it's got all the code that we need to connect to the web API service. We just have to change the URL. Instead of getting the current date and time, we're going to grab the weather from the Open Weather Map website. So, I'm going to come down here on the member section. I happen to be a gold member on my own website, so I can download this. All right, so here's that database. It's pretty simple. You hit Get Current Time and it gets the current time. It goes out to the web server. Now this one is pretty straightforward. Here's a get time mod that we built in the other video. OK, get UTC time. And it just literally goes to this web page and pulls in the information. Here's all the code that you need. OK, now the weather API from OpenWeatherMap is a little more involved, and you have to get something called an API key. Basically, you have to register. It's free, don't panic. But you have to sign up with your name and email address to request information. And they do that to prevent abuse. I think they give you like a thousand free calls a day, which is more than generous. There are, what, 1440 minutes in a day? So you could make a call every two minutes and be fine. Let's go sign up for that first. Let me show you how to do that. Their website is at openweathermap.org. Here's their signup page and I'll put links to all this stuff down below. Put in a username, email, password, repeat the password, all this stuff. You got to be 16 years or older, agree to their terms, hit the little thing here and then go create account. Once you verify your email address and all that good stuff you're going to log in, sign in successfully, then come over here and go to My API Keys.
When you create an account, you'll be given a default API key. You can also create your own. I created one called Weather. You just type in a name over here like I typed in Weather, hit Create or Generate, and it will give you a new key. It's not a super long code here. And yes, I blacked mine out. You cannot have mine. Make your own. They're free. Once you get an API key, copy it to your clipboard. We'll use it in just a few minutes. Now I will say that it took about 10 to 15 minutes between the time that I signed up and got my API key until it worked on their server. So it's not immediate, but it doesn't take long. So just wait a few minutes and it'll start working. All right. So paste your API key into Notepad and here's an example of the URL that you have to use to get data. I'm going to paste that in there as well.
It looks like this. I'm going to make it a little bit bigger. Here's what the URL looks like that you have to go to. And you can just put this in your browser. I'll put a copy of this down below so you can just copy and paste it. Here's the URL right out to about there. The question mark says here's where the parameters start. So I've got Q equals Cape Coral, Florida, USA, that's your location. It's pretty good about a wide variety of location formats. Then I specified at units equals imperial because the default for temperature is Kelvin. How very science-y of them. You can also switch it to Celsius. I'm in America, so we're still using the Fahrenheit, so that's imperial. And then app ID equals, this is where your application, your API key goes you put that right there don't use mine. Okay now you could take this whole thing and copy this and paste it right into your web browser and you'll get something that looks like this. Now this is a JSON response. JSON is a particular data format that's increasingly becoming the standard on the web. JSON can be a little difficult to parse information out of. I personally prefer XML if it's available, and with this particular API it is available. So I'm going to come back in here, and I'm going to add at mode equals XML, if you want. That's up to you. So again, now I'm going to copy this whole thing, drop it in my web browser. And now you get this guy. This is an XML formatted version of the same stuff.
This is just easier to pull data out of because now if you look at this you can see here's the temperature value equals so really what we're looking for is this. I want to find everything between everything between temperature value equals quote and then the next quote and we can use my find between function to very easily do this. We're going to get this information into Access and then just use find between we can also pull out the feels like values right there the humidity the pressure the wind speed all this stuff right what's the location name just to make sure you got the right spot right all right so now we know where to get the data from we've got our account set up we got our API key we can get the data in the web browser.
In tomorrow's video now, we're going to put it all together and use the API code that we built in the web API video to get that information, put it inside of our database, parse all the information out, and your end result will look something like this. We'll do that in tomorrow's class. So tune in tomorrow. Same bad time, same bad channel. You know the drill. If you remember, you can watch it right now because I'm gonna keep recording right now. But that's going to be your TechHelp video for today. I hope you learned something. Live long and prosper my friends. I'll see you tomorrow for part 2.
A special thank you and shout out to our diamond sponsor Juan Soto with Access Experts Software Solutions. They're manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check him out at accessexperts.com.
For a complete video tutorial on this topic, please visit https://599cd.com/WeatherAPI
No comments:
Post a Comment