Weather API 2
By Richard Rost
2 years ago
Using Weather API in MS Access for Current Data Part 2
In this Microsoft Access tutorial, you will learn how to pull current weather data using a web-based weather API. We will cover methods for API calls, setting up a default zip code, creating the appropriate modules, and parsing the returned weather data to display key information like temperature and location in your Access database. This is part 2.
Members
There is no extended cut, but here is the database download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, weather API tutorial, Microsoft Access, web-based API, current weather data, Access VBA, parse weather data, API key management, weather data API, OpenWeatherMap integration, zip code weather retrieval, automate data pulling, VBA programming, Access API calls, weather data extraction, real-time weather Access, JSON response parsing Access
Subscribe to Weather API 2
Get notifications when this page is updated
Intro In this video, we continue our Microsoft Access weather API project by building on the basics from part one. I'll show you how to update the form to use a zip code for API requests, set up default values, modify field controls, and organize the layout. We will update the VBA code to securely use an API key, retrieve weather data, and extract specific values like temperature, feels like, and location from the API response. You'll also learn how to handle string parsing in VBA and display the weather results directly in your form. This is part 2.Transcript This is part two of my weather API series where we're getting the weather from the internet with an API, and it's pretty cool stuff. If you haven't watched part one yet, go watch that so you know what we're talking about, and then come on back.
Alright, so yesterday we learned all about the API call we have to do, the URL that we have to use, this guy, right, to get what we want. There are other forms of this too. I put some other ones online for you. Here are two other versions. You can either say Q equals and then your location like that, or instead of Q you can say zip and use a zip code. I don't know what they have available for other countries, but here in the US you can use a zip code. You can probably do this in the UK and Canada too. You'll have to look if you're somewhere else, sorry.
And then you can also use latitude and longitude, which was pretty funny because the first time I did this, I just put in 81.9 for my longitude, and I forgot the negative. So it had me somewhere way out on the other side of the world. But these are the three different methods you can use. And there are tons of different options too. You can change the units from Imperial, which we talked about last time. There are different language settings. So read their website for a lot more information. I'm just going into the basics here.
But I'm gonna stick with the zip code version, I think, in this one because that's just the easiest; punch in a zip code. Okay. We're gonna do some modifications on this guy. So let's design this. The first thing is let's change this today's date to current, oh, I'm sorry, zip code. We're gonna put the zip code in first. So this guy, we no longer need a control source. Leave that unbound. Let's change the name of this to just zip. It's no longer a short date. And I am going to put a default value in there of 33909. That's my zip code in Cape Coral, Florida.
Now for this class, we don't need these buttons. You guys can go away. Let's change this. While I'm at it, real quick, why not? Weather API, whoops, weather API. And I'm gonna slide you over here. I'm gonna put this button down below it. All right, get current weather. And we're gonna status the response that comes back from the API. So we'll keep this little status box here. And if you don't know what the status box is, I use this in a lot of my videos. I got a whole separate video on the status box. Here it is, I'll put a link down below. It's basically just a message box alternative. Instead of message boxing stuff and popping something up, you can just put it in this little gray box.
All right, now get current weather. We are going to go to build event, see what we got in here. All right, so this guy basically calls a function called getUTCTime and then just displays the status there. So we're going to change this getUTCTime. And I believe that is in this getTime module. So if we open that up, here is this stuff. All right, let's change some stuff around here. So we're going to say instead of getUTCTime, we're going to change this to getWeather as a string. We're going to return a string back, all the information that comes back to us. We need the HTTP object, the response text, the URL. We don't need this, this lns. Okay?
Now, my URL is where you're going to put your URL that we've been building. I'm going to copy it from where I have it, my clipboard. Where are you? Where's the clipboard? Oh, wrong one. There you are. I found you. There you are. Okay, let's copy this. Copy, move you out of the way, and paste right here URL. Boom, there it is. It's pretty long. Let's split it up. I'm going to split it up right here after that question mark. And we'll put it on the next line like that. Now the zip code, we're going to specify on the form. Now this is a module, so we have to use the full name of that form, okay, which is what we're going to close off our quotes and forms main menu F zip is the name of the field. I just smacked that right inside the string there, okay?
The next thing we need is the API key. Now I'm going to put the API key in a separate module because if I put it up here, I'm going to accidentally flash it at least three times during the video, and I don't want you getting my API key. So I'm going to come over here and I'm going to create a new module. And I'm going to say global constant API key equals, and I'm going to put my API key right there. And then I can use this constant called API key, and I don't have to worry about you seeing this. In fact, I'm going to save this as Rick's API key. And you can come in here if you want to put your API key in here or just move this into there. Whatever you want to do. I don't care. Alright. But I'm gonna paste my API key in there and then you're not going to see this anymore. Okay. So the API key is in there. Now I can take this and just delete that and put the API key out here. See? And it just goes out and gets that constant. Nice and easy.
Okay, so we got there, we got this is the same, this is the same, this is the same, this is the same, this is the same, and right down here we get s equals response text. All I'm gonna do is I'm gonna get rid of all of this stuff here. That's where we do the calculations for the date and stuff. And I'm just gonna say get weather equals response text. So this guy is literally just going to go out to this address, get the data back, and return it as part of this function call. Pretty easy.
All right, let's go back to the main menu. All right, main menu, right-click, build event, here's the button for that. Now it's no longer getUTCTime and it's no longer a date. This is going to be s as a string and s is going to be get weather. And what are we going to do with it? Well, we're just going to status it. Status S. All right. Save it. Give me a debug compile, make sure everything is good, everything compiles. Okay, great. And let's come over here, close it, open it. That moves. I got code in here that moves. This is one of my older ones. I used to have in this Rixmod keep out, I used to have code in here that would move the startup window. I'm just going to exit sub this. You can keep this if you want to. This is so that when I log on, it would put the window in the middle of my screen for recording purposes, but I don't use it anymore.
Okay, let's try that again. Get weather and boom, there it is. It went out and grabbed this stuff from the web, from the website we specified, openweathermap.org. All right. Now, while we're building this, I'm going to take all of this code that's in here. It's on a shift F2. We're going to... Oh, that's really big. Let's move this like this. I want to copy all of this and drop this on my clipboard as well because there's a lot of data in here. We need to go through and parse it.
So just copy that to your clipboard here, come back to my handy-dandy clipboard. We're gonna put this on the bottom. I use a clipboard constantly when I'm programming, constantly, just to put stuff back and forth. All right, so save this. Now, what we're gonna do next is parse the bits of data out of here that we need. For example, the temperature is right here. Let me just squeeze this a little bit so you can see it better, there we go, okay. Temperature value equals right there. You can probably even grab it from here. Okay. That's what you want. That's the start of the temperature value, and it's going to end with the next quote. So we're going to use my find between function to pull that out of this string.
Once again, as a reminder, here is the find between video where I explain how this code was built. If you are a gold member, you can come to the find between page, there's a link down below and you can go right to the code vault and grab the find between code. If you're a silver member or you're not a member at all, here it is, get typing. I'm just going to hit copy and now it's on my clipboard. See, benefits of being a gold member or platinum if you want.
All right, back over here, let's go to the global module and I'm just going to paste that in the bottom here under my sleep function. Boom, there it is. So now I've got findBetween. And again, if you want to learn how this thing was built, go watch the findBetween video. Okay? Okay.
Now, back to here. Let's make another variable real quick, t as a string. And we're going to say t equals findBetween. Where's the string? Well, that's in the status box. That's the box on the main menu that's got the data in it, right, status box. All right, what's the start of the string? Now, put a set of quotes like that to start with, trust me. Okay, now go back to your clipboard. That's the text you're looking for for the beginning of it. Notice it's got a double quote inside there. Now what you have to do in Access, this goes back to beginner or early expert level stuff. For every one of these inside of a string, you have to put a double double quote. All right? So I'm going to take this, copy it, come back over here inside those quotes, and paste that, but I got to make one more quote because this becomes a double double quote inside the quotes. That says there's a literal quote there. This is a topic that's confusing for a lot of people including advanced developers. I personally sometimes have to stop and think a couple of times about how many quotes do I need. So go watch this video if you need more help. I talk a lot more about the double double quotes problem. Okay, go watch this.
All right, so back to this guy. All right, copy. Now we need, what are you looking for for the end of the string? Well, that's just the closing quotes. So it's going to be quote, quote, quote, quote. That's a literal set of quotes inside of a string. Now we're just going to message box T, and we'll see what that value is. Save it. Do a debug compile. Everything checks out. Let's come back over. You get out of the way. Come back over here, hit the button, bink, and there's my temperature, 79.05. And that's about right, it's about 79 degrees outside, not too bad tonight. If you look in here you should be able to see that, 79, there it is, 79.05. I don't think their feels right is right because it's a little more humid, so this might be something with their data. I've been working with this for the past couple of hours and that hasn't changed. The current temperature is always the same as the feels right. What's the humidity? 92, yeah, it's up there. It feels like it's about to rain, folks.
All right, now what you can do is, you can take this, instead of putting it in T and message boxing it, why not throw it in some more fields in here. Design view. Copy this guy, copy, paste. I'm going to put that over here. We're going to do current temp. We're going to do, let's change this here to current temp. Oh, come on. Current temp. Can you guys read this text okay? Is this big enough for you to see? I know YouTube sometimes compresses the video and makes it a little harder to see this. I know my website has clearer video than YouTube does. So, let me know. Post in the comments down below. If you have problems reading this text, let me know. We'll do current temp, we'll do feels like, even though I don't think it's right. Feels like, I know I zoom in on text if it's something that's really important to see. Why is my box so big? There it is, feels like. Hit okay. That's interesting to know by the way. If you zoom in like this and then let's say you change the dimensions of this box. If you hit OK, it saves the dimensions. If you hit Cancel, it doesn't. If you open it up again, it's back to its old dimensions. I discovered that a little while ago. Feels like, I got the location. And then we'll do the current date and time. Date and time.
It's funny because date is a reserved word in Access. Time is a reserved word in Access, but date time is not. So you can use date time safely. But don't always use like date or just time. Always have it like, you know, order date, appointment date, that kind of stuff. Never use just date. Just like, you don't want to use name. Those are all reserved words. All right. Date time, location, feels like, and current temp. Come over here and we'll do the same thing. Now, we can get rid of the T. We don't need T anymore. I just put that in there so we can message box. All right. We're going to say, right here, it's Current temp equals that. I'm just going to copy and paste this. Copy, paste, paste. We only need three of them because date time is just a date time equals now, like that. All right, we have feels like and we got location.
All right, now let's take a look at our text. Feels like is, where are you? That's up here. Let me see. Yeah, I like to squish this so you can see it all together. All right, there it is. Feels like value equals right there. All right, that's what we need. Basically, it's just replacing temperature with feels like in the other one. All right, just like that. Location, what does location look like? Location is here. Okay, so we need... Now, I've noticed, I did some playing with this before, and I've noticed that city ID is always zero unless you specify a city ID. You can give a city ID. If you guys find out that this is different, let me know. And also, when you do this, no matter how you get it, whether it's the zip code or not, it'll give you the longitude and latitude. That's pretty cool. Okay?
So, for this one, we're looking for city ID equals zero and the name equals. And notice all the quotes in there, so we got to fix all of those. All right, so for location, it's going to be that, but then we got to go quote, quote, quote like that. And I think that's all we have to do. Yeah. Okay. All right. Save it. Debug compile. Click the button. Close it. Close it. Open it. And oh, wait a minute, I got my default value and all that. Well, let's go to that. Select all these guys, data. Default now. You forget to take that out of there. All right. Now save it. Close it. Open it. And click the button. And there we go. Obviously you gotta make this bigger. And yeah, see I don't think that I don't think there feels like feels right is working. Oh well, we can take that with them. There we go, beautiful.
That's it, you can pull out whatever other information you want out of here. There's more stuff, there's wind speed and pressure and all that stuff. And they've got different other APIs. You can get forecasts, you can get little pictures, look at the little sunshine. They've got free and paid accounts, so go check them out. It's a good service and I endorse them fully and it works so give them your patronage if you can but I think that about covers it. If you like this stuff, if you like learning with me, and you want to learn more about VBA, come to my website. I got lots and lots of lessons. If you like my quirky teaching style and you enjoy my fantastic sense of humor, then come and learn some more from me.I got lots of lessons here. Also, consider signing up and joining if you want to become a member. Obviously, as a Silver member, you get all of my extended cut videos. You get a free beginner class every month, which you may or may not need.
Oops, someone just beamed in. Gold members can get an expert-level class every month, and Platinum members can get a free developer-level class every month after you've finished all the beginner and expert ones. There are all kinds of cool stuff available on my website. Come and check it out.
But that is gonna be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
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 them out at accessexperts.com.
TOPICS:
Weather API call methods
Using query string parameters in API
Using zip codes for API requests
Using latitude and longitude for API requests
Handling different units and language settings
Modifying an existing VBA form
Setting default values for form fields
Removing unnecessary form controls
Renaming form controls
Building an API URL dynamically
Creating a separate module for API key
Retrieving data from an API
Parsing response text from an API
Using the clipboard in VBA
Extracting temperature from API response
Using the findBetween function
Understanding double double quotes in VBA strings
Displaying extracted data in form fields
Handling API response values (current temperature, feels like, location, date and time)
Ensuring values are displayed correctly in form fields
Saving and debugging VBA code
Using reserved words in Access safely
Extracting additional weather data (wind speed, pressure, etc.)
COMMERCIAL:
In today's video, we're diving into part two of my weather API series. If you haven't caught part one, go watch that first. We'll start by refining our API call using zip codes and ensuring the control is set up correctly. I'll guide you through setting default values, renaming fields, and organizing the interface. Next, we'll modify the code to fetch weather data using a well-structured API key integration. You'll see how to handle responses and parse out specific information like temperature. Finally, we'll place this data into designated fields for a clean display. Full video available on my YouTube channel and website. Live long and prosper, my friends.Quiz Q1. What are the three methods mentioned to retrieve weather data using the API?
A. By city name, street address, and altitude
B. By postal code, city name, and continent
C. By city name, postal code, and latitude/longitude
D. By country name, region, and temperature index
Q2. In the tutorial, what initial default value is set for the zip code in the example?
A. 12345
B. 90210
C. 33909
D. 33101
Q3. What does the tutorial suggest to use in order to keep the API key secure and not display it during the tutorial?
A. Store it in a separate module and use a constant
B. Hard-code it directly in the main function
C. Encode it in Base64
D. Use a local environment variable
Q4. Why does the video use a status box instead of a message box?
A. Because message boxes can't display status text
B. To avoid popping up a message box every time, making it easier to display information
C. Because the status box is required by the API
D. Because the message box is outdated technology
Q5. What function is used to extract specific pieces of data like temperature from the API response text?
A. findSubstring
B. extractData
C. findBetween
D. getData
Q6. Why does the tutorial recommend using double double quotes within strings in VBA?
A. To denote a single quote character
B. To denote a double quote character inside a string
C. To signify the end of a string
D. To escape special characters like backslashes
Q7. What is the variable 't' used for in the script?
A. To store the formatted date and time
B. To store the API key securely
C. To store the extracted temperature value
D. To hold the HTTP response status code
Q8. What additional weather information does the tutorial suggest could be parsed from the API response besides current temperature?
A. Only the city name
B. Precipitation levels and cloud cover types
C. Wind speed and pressure
D. Soil moisture content and UV index
Q9. According to the tutorial, which of the following is essential to check to avoid using reserved words in Access VBA?
A. Using underscores in variable names
B. Adding 'tbl' prefix to all fields
C. Ensuring fields like 'date' or 'time' are well-formed
D. Creating dedicated tables for reserved words
Q10. What are the benefits mentioned about being a Gold or Platinum member on the tutorial creator's website?
A. Free access to all online coding tools
B. Access to more detailed tutorials and code vaults
C. Discounts on API subscriptions
D. Early access to all videos
Answers: 1-C; 2-C; 3-A; 4-B; 5-C; 6-B; 7-C; 8-C; 9-C; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone continues our series on working with a weather API to gather real-time weather data from the internet using Microsoft Access and VBA. If you have not yet watched part one of this series, I recommend checking that out first so you will understand the foundational concepts before moving forward with this lesson.
In our previous session, we covered the basic API call and the URL format used to retrieve weather information. There are multiple ways to construct these requests. For instance, you can use a query string parameter such as Q equals followed by your desired location, or you can use a zip code, which is especially convenient in the US. Zip codes are also often available for use in other countries such as the UK or Canada, but it is best to check the documentation relevant to your area if you are outside the US. Alternatively, latitude and longitude can be used to specify the location, but remember that missing a negative sign, as I did initially, can put you on the other side of the globe! These are the three primary ways to define the location in your API request, and the service offers additional options as well. You can set the units for temperature (for example, switching from Imperial to Metric) and specify language settings. For advanced features, I suggest reading the full API documentation on their website.
For this lesson, I will focus on using the zip code method because it is straightforward. We will make some changes to the Access form to facilitate this approach. First, we will set up a field to accept a zip code, leaving it unbound by removing its control source, and I will assign a default value for testing purposes, using 33909, which is the code for Cape Coral, Florida.
Next, since the other form buttons are not needed for this class, we can remove them to clean up the interface. I will relabel and reposition the main interface elements for clarity. The primary button will be labeled "Get Current Weather," and we will use a status box to display output. If you are unfamiliar with the status box concept, I have a detailed video on it. It serves as an alternative to standard message boxes, letting you display results in a gray box on your form instead of interrupting workflow with popup dialogs.
Moving on to the code, we need to adjust the event procedure for the Get Current Weather button. Previously, this code called a function to retrieve the time from the internet, but we will now modify it to obtain weather data instead. The replacement function, which I will call getWeather, is responsible for constructing the proper URL, sending out the HTTP request, and returning the resulting data as a string.
Because the URL depends on input (the zip code), the function will dynamically build the address using the value from our form. Since this code resides in a separate module, it is important to reference the full form field name to insert the zip code value properly.
Security is also a concern when working with API keys. To avoid inadvertently displaying my key, I will define it as a global constant in a separate module. By referencing this constant in the code, the API key is protected from accidental exposure in your application interface. This technique is not only more secure but makes the code easier to manage and update in the future.
The rest of the function remains much the same as before, except instead of working to calculate and display a date, we will simply return the entire response text from the web request. Once this is set up, we make the call from our main menu form and display the returned string in the status box.
As always, be sure to save your work and use Debug Compile from the VBA editor to ensure there are no syntax errors before running the new functionality. If you encounter any leftover code from previous projects, such as routines to move the application window, you can comment those out or remove them as needed for cleaner operation.
Upon triggering the process, the application fetches the weather data as a JSON string from the OpenWeatherMap API and displays it in the status box on the form. The next step is parsing this response to extract individual values such as temperature, "feels like" temperature, location, and so forth.
I prefer copying large chunks of response data to the clipboard while parsing, which helps when testing or reviewing multiple fields. To pull out specific data points like temperature, you can use a custom "findBetween" function that isolates text between two target strings. If you want more details about how this helper function works, I have an entire video tutorial explaining how to build and use it, and gold members can access the code from my code vault. For others, the code is displayed in that video for manual entry.
Once findBetween is available, the next challenge is handling strings and quotation marks within VBA. When writing code that searches for text in the JSON (for example, extracting the temperature value), you need to account for double quotes in your search targets. In Access VBA, this means using double double quotes, which can be a common source of confusion for even experienced programmers. I recommend watching my dedicated video on handling nested quotation marks in VBA for a more thorough explanation.
After properly setting up the criteria and extracting the required value using findBetween, you can message box the result or, better yet, display it directly in new unbound fields on your form. I suggest adding fields for current temperature, "feels like" temperature, location, and date-time, avoiding reserved words in Access by using labels like "dateTime" instead of just "date" or "time."
From there, it is a matter of writing a few lines of code to assign the extracted values to their corresponding form fields. For values like date and time, you can use VBA's Now function to provide the current timestamp. The other values are isolated from the API response as described.
It is also worth noting that the JSON includes other data such as city ID, longitude and latitude, humidity, wind speed, and more. You can expand your code to display any of these as needed. The API service offers even more, including forecasts and weather icons, for those who want to explore further.
If you are enjoying this series and want to expand your knowledge of Access VBA or just like my approach, you will find a large selection of lessons available on my website. Membership comes with extra benefits, including access to my extended cut videos and free classes every month at the Silver, Gold, and Platinum member levels, depending on your needs and experience.
That concludes today's Access TechHelp tutorial. You can find the complete video tutorial that walks through all of these steps in detail on my website at the link below. Live long and prosper, my friends.Topic List Weather API call methods
Using query string parameters in API
Using zip codes for API requests
Using latitude and longitude in API requests
Modifying a VBA form for weather data input
Setting default values in form fields
Removing and renaming form controls
Dynamically building an API URL in VBA
Storing API key in a separate module
Retrieving API response using HTTP object
Parsing API response text
Extracting temperature from API response
Extracting "feels like" value from API response
Extracting location from API response
Extracting current date and time for display
Using the findBetween function in VBA
Handling double double quotes in VBA strings
Displaying parsed data in Access form fields
Ensuring proper display of form values
Handling reserved words in Access field names |
