UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Plotting Addresses On Maps    
 
   
inneedofaccesshe...
post Jun 3 2011, 12:28 PM
Post #1

UtterAccess Enthusiast
Posts: 78



I am trying to determine a method for plotting addresses on a map (e.g. Google or Bing Maps).
I am using Access 2007. Some of the templates have a module called "modMapping" (e.g. the Contacts Template) that uses the following code to plot a single address on a map. I am interested in plotting multiple points (potentially hundreds) of addresses on a single map.
The code for the modMapping module I reference is:
CODE
Function OpenMap(Address, City, State, Zip, Country)
Dim strAddress As String
strAddress = Nz(Address)
strAddress = strAddress & IIf(strAddress = "", "", ", ") & Nz(City)
strAddress = strAddress & IIf(strAddress = "", "", ", ") & Nz(State)
strAddress = strAddress & IIf(strAddress = "", "", ", ") & Nz(Zip)
strAddress = strAddress & IIf(strAddress = "", "", ", ") & Nz(Country)

If strAddress = "" Then
MsgBox "There is no address to map."
Else
Application.FollowHyperlink "http://maps.live.com/default.aspx?where1=" & strAddress
End If
End Function

Any ideas?
Go to the top of the page
 
+
StephenErnst
post Jun 4 2011, 06:44 PM
Post #2

New Member
Posts: 7
From: Seattle



I hope someone knowledgeable replies to this as I would like to do the same thing. I can write code to iterate over a recordset of addresses but the problem seems to me to be how to maintain the persistence of the target map.
Go to the top of the page
 
+
Aquadevel
post Jun 4 2011, 07:42 PM
Post #3

UtterAccess VIP
Posts: 6,902
From: Earth...



A 'UtterAccess member who is a fireman in NY/NYC did an app that did this.
opefully, someone will remember his SN, info.
Good luck with your projects!!
Go to the top of the page
 
+
HiTechCoach
post Jun 5 2011, 04:05 PM
Post #4

UtterAccess VIP
Posts: 18,666
From: Oklahoma City, Oklahoma



I would recommend looking in the Access Code Archive forum.
Go to the top of the page
 
+
inneedofaccesshe...
post Jun 6 2011, 12:44 PM
Post #5

UtterAccess Enthusiast
Posts: 78



Hmm...
Well if anyone cares, it looks like Google allows you to do this using GoogleDocs.
The end result is essentially what I am looking to do using Access. (Ideally, I'd like to use an overlay on the map [e.g. political districts] but that's a battle for a different day...)
Does anyone have any idea how I might be able to perform what the link above does but in Access rather than GoogleDocs?
-Ryan
Go to the top of the page
 
+
HiTechCoach
post Jun 6 2011, 12:45 PM
Post #6

UtterAccess VIP
Posts: 18,666
From: Oklahoma City, Oklahoma



Ryan ,
id you get a chance to look in the Access Code Archive forum?
Osearched the Access Code Archive forum and it turned up some examples shared by UtterAccess members:
Google Maps™ map and street view utility.
Google Maps Class Module
Google Map Utility
Go to the top of the page
 
+
mandrews
post Jun 6 2011, 04:23 PM
Post #7

UtterAccess Guru
Posts: 603



Sorry didn't mean to devalue the concept of learning how to do something from scratch. I just had the exact solution to the question asked so thought it made sense to mention it. There was already a post about looking at all the google map examples in this forum ahead of mine.
When I have used this forum as a business user, I usually want to get my access project done so I love to get third party references, as well as anyone that will give me the exact code needed just so I have all the options.
Here's a small contribution to learning, the shell for how to do a Google Earth file (probably calls a few functions not included, but hope it helps whoever is looking at this a little):
There are google earth and kml instructions online for the structure of KML files (which are just really XML).
Public Sub MakeGoogleEarthFile(strSQL As String)
'Note needs Microsoft Scripting Runtime reference
If IsDebugMode = 0 Then On Error GoTo MakeGoogleEarthFile_Error
Dim strfilename As String
Dim oFS As FileSystemObject
Dim oTS As TextStream
Dim rs As DAO.Recordset
Dim strName As String
Dim strDescription As String
Dim strAddress As String
'get the folder where the google earth file will be saved
strfilename = BrowseFolder("Select a directory to store your GoogleEarth File")

'exit if export cancelled
If Nz(strfilename, "") = "" Then Exit Sub
'append our standard filename (you could change this code to use custom filenames)
strfilename = strfilename & "\GoogleEarth.kml"
Set oFS = New Scripting.FileSystemObject
Set oTS = oFS.OpenTextFile(strfilename, ForWriting, True)
'write the beginning of the file
oTS.WriteLine ("<?xml version=""1.0"" encoding=""ISO-8859-1""?>")
oTS.WriteLine ("<kml xmlns=""http://earth.google.com/kml/2.0"">")
oTS.WriteLine ("<Document>")
oTS.WriteLine ("<Style id=""A""><IconStyle><scale>0.8</scale><Icon><href>http://maps.google.com/mapfiles/kml/pal4/icon57.png</href></Icon></IconStyle><LabelStyle><scale>0</scale></LabelStyle></Style>")
oTS.WriteLine ("<Style id=""B""><IconStyle><scale>1.0</scale><Icon><href>http://maps.google.com/mapfiles/kml/pal4/icon57.png</href></Icon></IconStyle><LabelStyle><scale>1</scale></LabelStyle></Style>")
oTS.WriteLine ("<StyleMap id=""C"">")
oTS.WriteLine ("<Pair><key>normal</key><styleUrl>#A</styleUrl></Pair>")
oTS.WriteLine ("<Pair><key>highlight</key><styleUrl>#B</styleUrl></Pair>")
oTS.WriteLine ("</StyleMap>")
'loop thru all contacts creating placemarks for each contact
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do While Not rs.EOF
strAddress = Nz(StripMapChars(rs("MapAddress")), "")
strName = Nz(StripMapChars(rs("MapName")), "")
strDescription = strName & vbCrLf & strAddress
'strDescription = "&lt;div&gt;" & strName & "&lt;br&gt;" & strAddress & "&lt;/div&gt;"
'strDescription = "&lt;div&gt;" & strName & "&lt;br&gt;" & strAddress & "&lt;/div&gt;" & "span class=&quot;l&quot;&gt;"
If Nz(rs("AddressStreet1"), "") <> "" And Nz(rs("AddressCity"), "") <> "" And Nz(rs("AddressState"), "") <> "" And Nz(rs("AddressZip"), "") <> "" Then
'write each placemark
oTS.WriteLine ("<Placemark>")
oTS.WriteLine ("<name><![CDATA[" & strName & "]]></name>")
oTS.WriteLine ("<description><![CDATA[" & strDescription & "]]></description>")
oTS.WriteLine ("<styleUrl>#C</styleUrl>")
oTS.WriteLine ("<address><![CDATA[" & strAddress & "]]></address>")
oTS.WriteLine ("</Placemark>")
End If
rs.MoveNext
Loop
End If
'write end of file
oTS.WriteLine ("</Document>")
oTS.WriteLine ("</kml>")
'tell the user what happend and ask them if they want to open it
If MsgBox("Output to Google Earth KML file a success! Would you like to open the file?", vbYesNo, "Success!") = vbYes Then Call fHandleFile(strfilename, WIN_NORMAL)
MakeGoogleEarthFile_Exit:
Set rs = Nothing
Exit Sub
MakeGoogleEarthFile_Error:
Call ErrorLog(Err.Description, Err.Number, "ModMapping", Erl, "MakeGoogleEarthFile")
Resume MakeGoogleEarthFile_Exit
End Sub
Go to the top of the page
 
+
mandrews
post Jun 6 2011, 04:31 PM
Post #8

UtterAccess Guru
Posts: 603



The guy that runs this website is a good guy to talk to:
http://batchgeo.com/
He is very knowledgable about the google maps webservice, as well as looking at those google map examples on this forum, that's how I started when I had the same goal as you.
shoot me an email if you need help.
Remember to delay a bit before each call when geocoding.
Mark
Go to the top of the page
 
+
HiTechCoach
post Jun 7 2011, 10:00 PM
Post #9

UtterAccess VIP
Posts: 18,666
From: Oklahoma City, Oklahoma



Mark,
OW! Great stuff.
Go to the top of the page
 
+
inneedofaccesshe...
post Jun 8 2011, 09:36 AM
Post #10

UtterAccess Enthusiast
Posts: 78



Having trouble with the items from the Code Archive. None of them really seem to offer what I am looking for, which is plotting multiple points.
The batchgeo.com link is excellent and does exactly what I want, similar to what GoogleDocs does... but I have no earthly idea how to "link" to this from Access...
Go to the top of the page
 
+
HiTechCoach
post Jun 8 2011, 10:23 AM
Post #11

UtterAccess VIP
Posts: 18,666
From: Oklahoma City, Oklahoma



Your original post stated: "I am trying to determine a method for plotting addresses on a map". The Google Maps Class Module does have a ethod for plotting addresses on a map.
Is you can see, Access does not have any built in method for mapping. You will have to write some VBA code to pull off.
The Google Map APIs methods can be found at Google here: Google Maps API Family
The Bing Map API methods can be found at the Microsoft here: Bing Maps for Developers
Is this what you wanted? If not, to help you we really need a lot more detail on exactly what you are trying to do.
Go to the top of the page
 
+
inneedofaccesshe...
post Jun 10 2011, 12:40 PM
Post #12

UtterAccess Enthusiast
Posts: 78



The Google Maps Class Module that I looked at only seems to allow you to do one of the following (unless I am mistaken...):
Only plot 1 pushpin at a time, but you can set it up so that when you scroll thruogh the recordset the map automatically shows the next address in the recordset (but the original one dissapearS)
- Get directions with a handful of pushpins taht are connected by polygons
What I am trying to do is basically:
- Use a form to set the criteria for a select query
- The query selects addresses that are stored in an Access table
- The query results are displayed on the form
- At the push of a "Map It" button, which either an Internet browser or a object frame on the form plots the addresses on a map
The code I posted at the top:
Is it possible to modify that so that it basically follows this logic: "For every X in Y, do Z" where X represents a single address, Y represents a recordset, and Z represents the map.
Go to the top of the page
 
+
HiTechCoach
post Jun 10 2011, 01:30 PM
Post #13

UtterAccess VIP
Posts: 18,666
From: Oklahoma City, Oklahoma



I really don't think so. It is not that simple or everyone would already be doing it that way..
The code you posted is building the URL just like is you went to bing's map and did a search. Have you figure out how to search for two or more address at the same time?
AFAIK the way you map multiple points with Google's map engine is to create a .kml file. This file is passed to the mapping engine.
So "For every X in Y, do Z is how you build the .kml file.
Go to the top of the page
 
+
mandrews
post Jun 15 2011, 02:33 PM
Post #14

UtterAccess Guru
Posts: 603



You can use VBA code which interacts with a htm file to produce the map on an Access form using the web browser control (it works very similar to the
batchgeo.com website I listed before), except the htm file is stored on your C: drive right next to your front-end database.
FOr you can go the KML way (but that is google Earth) not google maps.
inneedofaccesshelp I told you (in private chat) I could help in more detail if you just email me. Difficult to explain this topic (because it is a bit tricky).
From my memory I thought one of the examples on UtterAccess got me most of the way there when I tackled this exact issue.
They all use a htm file as well, just maybe do variious different things. It could be a little trciky if you don't have the right background with html, javascript etc...
Ocommonly add functionality to just put all your client addresses on a map (or a filtered list of clients etc...).
Mark
Go to the top of the page
 
+
mkhayrat
post Jan 7 2014, 05:40 PM
Post #15

New Member
Posts: 1



Hi, this is good but do your also have the sub functions that make sit work "BrowseFolder", "StripMapChars", etc.?
Also, do you a shell function that creates a google map file not google earth?
Thanks very much...
MK
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 2nd October 2014 - 05:25 AM