|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Map Location By Richard Rost 
6 years ago
Map any Location in Google Maps from your Microsoft Access Database. In this video, I will show you how to store any specific location in your Access database, and then with one click open Google Maps in your Web Browser to that location using Application.FollowHyperlink. Cecil from Tennessee (a Platinum Member) asks, "I’m looking to build a database for my local cemeteries. They don’t have any maps or any way to tell you where someone is buried. I have looked online for software or something that I could customize to fit what I’m doing. It does not exist. What can I do?" MembersI'll show you how to take ANY address field(s) in your database and open Google Maps to that address directly. 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! Links
New
IntroIn this video, I will show you how to map any location using Google Maps and store that information in your Microsoft Access database, making it easy to save and view exact spots with just one click. We will use the example of mapping cemetery plots, but you can use this method for any location. I will demonstrate how to copy the Google Maps link, store it in a long text field in Access, and set up a simple button to open the map directly in your web browser.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I am going to show you how to map any location using Google Maps and store that location in your Access database. Then, with one click, you can have Google Maps pop up to show where that location is stored.Cecil, a platinum member, writes: I am looking to build a database for my local cemeteries. They do not have any maps or any way to tell you where someone is buried. I have looked online for software or something that I could customize to fit what I am doing. It does not exist. What can I do? Well, Cecil, a lot of the time a cemetery might give you a plot number or some kind of street location inside your cemetery, but it would be nice if you could simply save a pin to exactly where someone's grave is located. I actually prefer using Google Maps to do this. All you have to do is find the exact location on the map that you want and then store a pin in your database for where Google Maps says that pin is. For example, here I am in Fort Myers. Let's find a spot in Fort Myers. Let's zoom into this Fort Myers Country Club here. Let's say I want to drop a pin exactly on the sand trap here. If you want an even better image, just click on the satellite box down there. That will show you the actual satellite image. As you click, you will see that a little pin pops up. Let me zoom in some more. Let's say I want to drop a pin right on this bunker right here, so click. Now notice this little window popped up right down here. That is the exact latitude and longitude of that pin. If you click on that, your address bar updates with "Maps place" and then the exact latitude and longitude. It also stores some other information like the zoom level. You can collapse that. Notice how, as I zoom in, it changes the width of my zoom level. You can get it exactly the way you want it right there on that pin. This is what you want to store in your Access database. You could store the latitude and longitude separately, but just save the whole thing - save the whole link. Google has not updated this in a very, very long time, so I do not see them changing it anytime soon. I am going to take that and drop this on my Notepad. There is Notepad. I am going to paste that in there. Let me turn word wrap on so you can see the whole thing. That is what it looks like. That is the exact location of that pin. You can get in pretty close detail-wise, so if you are doing individual plots inside of a cemetery, this is the best way to do it. You have the latitude and longitude right down to the foot. How do we store this information in our Access database? It is simple. This could be longer than 255 characters, so I am going to put it in a standard long text field. Here is my standard database template that I use for class. Put that right there. Now I am going to go in my customer table and I am going to add right here a location. I do not think that is a reserved word, so we are going to make that long text. That means it can be pretty much as long as you want it to. It may be over 255 characters, so I am not going to worry about storing it in a short text field. Let's close that up and add that as a field on my customer form. Right-click, design. I am just going to grab my notes field here, copy and paste it, and slide it down here. We do not have to make it super big in the form. Right there. I am going to change this to say location. Change the control source here to location. Copy that and paste it. So I made a box here where I can put that location. Let's go back to my Notepad and I am going to copy this whole thing. Make sure you do not save that with line breaks in it. Make sure it is one continuous link. In fact, do not use Notepad; I just used Notepad because I was teaching you how to save it. Copy it right from the Google Maps address bar and paste it right in that box there. You do not want any line breaks in there. Now, how do I open this? You can open this with one click of a button. Yes, Access has a hyperlink data type. I do not like using it. You can actually make fields called hyperlinks, but they do not work right. I have never liked them. I just use plain text and use this method to jump and open this hyperlink up. It is real simple. It is one button with one line of code. Design view. I am going to basically steal one of these other buttons here, just copy and paste it. Just click on it, copy and paste. Open location map, like this. Put it over here, wherever you want to. Right-click, build event. Here is my code builder. One line of code. It is real simple. Application.FollowHyperlink. There it is. Space and then the address: Location. That is it. There are some other options in here. Subaddress means a tag inside that page. You can choose whether you want a new window or other options. These options are all fine. This is going to open up this hyperlink in your default web browser, which for me is Google Chrome. I just use Microsoft's Edge for teaching classes. Let's go back over to Access. I am going to close this and reopen it. Let's see what we have. Open location map. Click. Look at that. It opened up my browser, put me in a new tab, and brought me right to that pin, complete with an image. Yes, the fact that it is satellite view is saved in this hyperlink somewhere. It is in all this big address stuff. Somehow this code tells exactly what you want to see. That is how I would recommend it if you are doing cemeteries and you want to store a pin for each individual plot. I would definitely use this location option here. So Cecil, thank you. Cecil is a new platinum member. He emailed me before I knew he was a member, and I gave him some brief information on how to use this. I have a seminar called the Open Other Program Seminar. I teach you how to launch Acrobat Writer and Reader, Word, and Excel, and how to launch different applications. One of them is launching your web browser based on information in your Access database. Of course, this is a very simple method you can use as well, and I think this works just fine for what you are doing. The only thing is, it is a little bit of a manual back and forth because you have to copy this stuff yourself. Yes, you can use a hyperlink control that you can actually put inside of Access, but I do not like it because when it opens up, unless you make it a really big window, you have all this other stuff on the screen. I have tried it and played with it, and I would rather just open up the browser, copy this and paste it myself. Is there an automated way to do it? Yes, but it involves a lot of code, so I think this is the simplest method. Interested in learning more about mapping and Google Maps in Access? In the extended cut edition for members, I show you how to open a map to any address in your database. If you have an address field or fields, I will show you how to open up Google Maps directly to that address. You can drop that button on any form in your database. How do you become a member? Just click on that join button down below the video. You get access to all of my extended cut TechHelp videos if you are a Silver member and up, live video and chat sessions, and lots of other perks. There is a big list of all the different supporter levels. Thank you for watching. Videos like this will always be free. I am going to keep making these free TechHelp videos as long as I can. Make sure you like this video and drop me a comment. Feel free to subscribe to my channel. Click the bell icon; that way you will get notifications whenever I release new videos. Check down below the video window for more links and resources. YouTube does a good job of hiding that. There is a little show more link there. If you have not yet tried my Access Level 1 course, try it right now. It is free and three hours long. You can find it on my website or on YouTube. If you like Level 1, Level 2 is just a dollar and that is free for members as well. If you want to see your question answered in a video like this, go to my TechHelp page and drop it there. Also feel free to check out my Access Forum. You can always drop me an email if you want to, but I prefer you leave a comment on my website. There are all my other cool resources. All these links will be down below, by the way. Thanks a lot. I hope you learned something today, and we will see you next time. QuizQ1. What is the main purpose of the technique demonstrated in the video?A. To store and map precise physical locations in an Access database using Google Maps B. To calculate distances between points in Google Maps using Access C. To print physical cemetery maps from Access D. To generate driving directions within Access Q2. What information does Google Maps provide when you drop a pin somewhere on the map? A. Only the name of the location B. Only the street address C. The exact latitude and longitude, plus additional map details D. Only the city and country Q3. Where do you find the link containing the exact location information after dropping a pin? A. In the browser's footer B. In the Google Maps address (URL) bar after clicking the coordinates C. In the Help section of Google Maps D. On the left panel of Google Maps Q4. When storing this Google Maps link in Access, what data type should you use for the best compatibility? A. Number B. Short Text C. Date/Time D. Long Text Q5. Why should you avoid using Notepad to store your Google Maps link before pasting it into Access? A. Notepad may introduce line breaks, splitting the link B. Notepad can change the latitude and longitude formatting C. Notepad converts URLs into clickable text D. Notepad encrypts the content Q6. Which Access feature is NOT recommended by the instructor for storing and opening these map links? A. Plain text field B. VBA button with Application.FollowHyperlink C. Hyperlink data type field D. Standard long text field Q7. What VBA command does the video suggest to open a saved map link from Access? A. DoCmd.OpenMap B. Application.OpenURL C. Application.FollowHyperlink D. Shell("chrome.exe") Q8. What is a potential drawback of using the Hyperlink data type in Access as mentioned in the video? A. It cannot store URLs longer than 50 characters B. It triggers errors when used in forms C. The user interface is cluttered and less flexible D. It automatically deletes broken links Q9. If you want to automate generating the Google Maps link, what does the instructor mention about this process? A. It is not possible in Access B. It requires a lot of coding and is more advanced C. Access has a built-in wizard for it D. Google Maps API integration is mandatory Q10. In the database example, which form feature is added to allow the user to launch Google Maps with one click? A. A dropdown menu B. A command button with an event procedure C. A combo box bound to the location field D. A macro-triggered report Answers: 1-A; 2-C; 3-B; 4-D; 5-A; 6-C; 7-C; 8-C; 9-B; 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. SummaryToday's video from Access Learning Zone shows how you can use Google Maps alongside your Microsoft Access database to map and store any location, such as a cemetery plot, and then display that location with a single click.I recently received a question from a viewer named Cecil, who is working on a cemetery database. He pointed out that local cemeteries do not have organized maps or records showing exactly where people are buried. He wanted to know if there was a way to keep track of grave sites with Access and Google Maps, since there seemed to be no existing solution that fit his needs. In situations like this, cemeteries may have plot numbers or basic directions, but it can still be tough to find the precise location of a grave. My approach is to use Google Maps to drop a pin on the exact spot, and then store that pin's information inside your Access database. To do this, you first find the location you want in Google Maps. You can even switch to satellite view for better accuracy. Once you have zoomed in on the exact spot, click to place a pin. A window will appear with the latitude and longitude coordinates. Clicking on that gives you a complete link in your browser's address bar, including any specific details like the zoom level and satellite view settings. It is helpful to copy that entire link, which contains everything Google needs to return you to that exact spot. You might find it practical to temporarily paste the link into Notepad to review it. Just make sure there are no line breaks in the address—you want to keep it as one continuous string. You could break out the latitude and longitude as separate fields, but saving the whole Google Maps link is easier and keeps all of the details intact. Now, to store this information in Access, add a new long text field to your table. I call it 'Location'. Since these links can be longer than 255 characters, a long text field is a better choice than short text. Simply add this new field to your form as you would with any other text field. You do not need to make the box especially large on the form. After you have added this new field to your form, copy the complete Google Maps link and paste it directly into the 'Location' box for each relevant record. Again, ensure there are no line breaks, and it should paste in as a single hyperlink string. To view the map from within Access, you can create a button that opens the saved Google Maps link in your default web browser. Although Access offers a hyperlink data type, I have found it unreliable for this purpose. I prefer using a standard text field and then using a button with a simple line of code to launch the link. All you need is a button whose click event will launch the Application.FollowHyperlink command, pointing at the 'Location' field. This will open the corresponding spot on Google Maps in your web browser, zoomed to exactly where you placed the pin. This approach allows you to store precise grave or plot locations, or any other site you need, directly in your database. It is fast and effective. The only manual step is copying the link from Google Maps to your record, but for most use cases this is sufficient. You could automate more of the process, but that would require more complicated programming. If you want to go further, I show how to map any address from your database in the Extended Cut video for members. In that lesson, I cover opening Google Maps directly to any address stored in your database, which can be very useful if you are tracking homes, businesses, or other locations. If you are interested in accessing extended videos, join as a Silver member or higher. This provides access to all extended TechHelp lessons, live streams, and more. You can find details about membership and benefits on my website. Thanks for tuning in today. My goal is to keep these TechHelp videos free for everyone. If you enjoyed this lesson, please leave a comment, like, and subscribe for updates on new content. You will find more information and resources in the description below the video. If you are new to Access, consider starting with my free Access Level 1 course, which is about three hours long and available on my website and YouTube. If you want your own question answered, visit my TechHelp page or the Access Forum. For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends. Topic ListMapping exact locations with Google MapsObtaining latitude and longitude from Google Maps Copying Google Maps URLs for specific map locations Storing map links in a Microsoft Access database Using a long text field to store location data Adding a location field to an Access table Updating Access forms to include a location field Inserting Google Maps links into Access form controls Opening map links with a command button in Access Using Application.FollowHyperlink in VBA to launch URLs Displaying stored locations in a web browser via Access |
||||||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access map location application.followhyperlink google maps PermaLink Map a Location in Access in Microsoft Access |
