You can use Google Maps with an Acess DB

Discussion:

(too old to reply)

Wayne-I-M

2008-01-23 02:17:23 UTC

How to open a Google Maps showing a location from your database.

Not sure if anyone is interested but it was asked a couple of days ago and I
didn’t know a good answer - just in case you have looked at my 1st attempt
:-) so I looked into it and came up with this. There will be other methods –
so this is just one.

To open Google Maps with the pointer placed on any location in your database.

Notes:
I assume you have a field called PostCode – if not then change the OnClick.
The field must contain a real postcode (the link will fail if the postcode
does not exist)
This will work for anywhere in the world – provided it has a UK postcode ;-)

Create a button on your form (called MapSearch) put this OnClick

Private Sub MapSearch _Click()
Dim MyHyperlink As String
Dim strGoogleLocation As String
strGoogleLocation = Replace([Postcode], " ", "+")
MyHyperlink =
"http://maps.google.co.uk/maps?g=q&hl=en&geocode=&time=&date=&ttype=&q=" &
strGoogleLocation & "&ie=
UTF8&ll=54.800685,-3.911133&spn=12.028783,29.619141&z=5&om=0"
Application.FollowHyperlink MyHyperlink
End Sub

IMPORTANT - this is all on one line
MyHyperlink =
"http://maps.google.co.uk/maps?g=q&hl=en&geocode=&time=&date=&ttype=&q=" &
strGoogleLoaction & "&ie=
UTF8&ll=54.800685,-3.911133&spn=12.028783,29.619141&z=5&om=0"

You can refine this code
This section opens the map to show the whole of the uk
UTF8&ll=54.800685,-3.911133&spn=12.028783,29.619141&z=5&om=0
So change this if you want the map to open showing another country. To find
out the code for this. Open Google Maps. Sign in for an account. Display
the map how you want it and then save the map. Google will provide you with
a hyperlink – copy this in to word (that’s what I did) then cut and paste the
“whole of the URL” after UFT.

Have fun.

--
Wayne
Manchester, England.

Tom Wickerath

2008-01-23 09:57:01 UTC

Permalink

Hi Wayne,

Sorry to burst your bubble, but the UK is not the center of the universe
<smile>.

Here is my contribution towards your Google Maps endevors:

'**********Begin Code************************

Option Compare Database
Option Explicit

Sub GoogleMap(strStartingPoint As String, strDestination As String)

' http://www.utteraccess.com/forums/showflat.php?Cat=&Number=1356166

' Given a FROM address and TO address, will start Internet Explorer,
' and call up GoggleMaps to give directions with total mileage estimate!

' Example of calling from Immediate window to get directions from
' the Space Needle in Seattle to the White House in Wash. DC, USA:

' Call GoogleMap("1600 Pennsylvania Avenue, Washington, DC", _
"400 Broad St, Seattle, WA 98109")

Dim strURL As String
Dim ie As Object

'Convert spaces to + sign
strStartingPoint = Replace(strStartingPoint, " ", "+")
strDestination = Replace(strDestination, " ", "+")

strURL = "http://maps.google.com/maps?f=d&hl=en&saddr=" _
& strStartingPoint & "&daddr=" _
& strDestination & "&ie=UTF8&z=5&om=1"

Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate strURL
ie.Visible = True

End Sub

Public Function Mapper_Click _
(strCity As Variant, strState As Variant, _
strAddress As Variant, strZip As Variant) As Variant

' Example of calling from Immediate Window for the Space Needle
' located in Seattle, WA., USA:

' Call Mapper_Click ("Seattle", "WA", "400 Broad St", "98109")

On Error GoTo Err_Mapper_Click

Dim strFullAddress As Variant
Dim strMapquest As Variant

strFullAddress = strAddress & "&city=" & strCity _
& "&state=" & strState _
& "&zipcode=" & strZip

strMapquest = "http://www.mapquest.com/maps/map.adp?searchtype=" _
& "address&formtype=search&countryid=US" _
& "&addtohistory=&country=US&address=" _
& strFullAddress & "&historyid=&submit=Get Map"

'Remove trailing spaces
strMapquest = Trim(strMapquest)

'Change imbedded spaces with plus signs
strMapquest = Replace(strMapquest, " ", "+", 1, , vbTextCompare)

Application.FollowHyperlink strMapquest, , True

strMapquest = vbNullString

Exit_Mapper_Click:
Exit Function

Err_Mapper_Click:
MsgBox Err.Number & "-" & Err.Description
Resume Exit_Mapper_Click

End Function

'****************End Code*********************

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Post by Wayne-I-M
How to open a Google Maps showing a location from your database.
Not sure if anyone is interested but it was asked a couple of days ago and I
didn’t know a good answer - just in case you have looked at my 1st attempt
:-) so I looked into it and came up with this. There will be other methods –
so this is just one.
To open Google Maps with the pointer placed on any location in your database.
I assume you have a field called PostCode – if not then change the OnClick.
The field must contain a real postcode (the link will fail if the postcode
does not exist)
This will work for anywhere in the world – provided it has a UK postcode ;-)
Create a button on your form (called MapSearch) put this OnClick
Private Sub MapSearch _Click()
Dim MyHyperlink As String
Dim strGoogleLocation As String
strGoogleLocation = Replace([Postcode], " ", "+")
MyHyperlink =
"http://maps.google.co.uk/maps?g=q&hl=en&geocode=&time=&date=&ttype=&q=" &
strGoogleLocation & "&ie=
UTF8&ll=54.800685,-3.911133&spn=12.028783,29.619141&z=5&om=0"
Application.FollowHyperlink MyHyperlink
End Sub
IMPORTANT - this is all on one line
MyHyperlink =
"http://maps.google.co.uk/maps?g=q&hl=en&geocode=&time=&date=&ttype=&q=" &
strGoogleLoaction & "&ie=
UTF8&ll=54.800685,-3.911133&spn=12.028783,29.619141&z=5&om=0"
You can refine this code
This section opens the map to show the whole of the uk
UTF8&ll=54.800685,-3.911133&spn=12.028783,29.619141&z=5&om=0
So change this if you want the map to open showing another country. To find
out the code for this. Open Google Maps. Sign in for an account. Display
the map how you want it and then save the map. Google will provide you with
a hyperlink – copy this in to word (that’s what I did) then cut and paste the
“whole of the URL” after UFT.
Have fun.
--
Wayne
Manchester, England.

Wayne-I-M

2008-01-23 10:11:01 UTC

Permalink

Hi Tom

Post by Tom Wickerath
Sorry to burst your bubble, but the UK is not the center of the universe

I agree - this is :-)

http://maps.google.co.uk/maps/ms?hl=en&ie=UTF8&msa=0&ll=46.291829,10.850544&spn=0.003151,0.007231&t=h&z=17&om=0&msid=103780121206034963667.00044460d57e1dea96b9d

Call in for a cappuccino at any time

The code I made was specific to our needs (we don't need the from / to). We
use coaches to pickup clients from accross the UK and sometime we don't know
where they live - so I have added this button just so our admin team can
"see" where they live and allocate them to the nearest pickup.

Mind you - not today (or for the next 6 weeks until then end of March) as I
am working in the Alps. It stopped snowing 2 days ago and it now looks like
a ski brochure.
--
Wayne
Manchester, England.

Post by Tom Wickerath
Hi Wayne,
Sorry to burst your bubble, but the UK is not the center of the universe
<smile>.
'**********Begin Code************************
Option Compare Database
Option Explicit
Sub GoogleMap(strStartingPoint As String, strDestination As String)
' http://www.utteraccess.com/forums/showflat.php?Cat=&Number=1356166
' Given a FROM address and TO address, will start Internet Explorer,
' and call up GoggleMaps to give directions with total mileage estimate!
' Example of calling from Immediate window to get directions from
' Call GoogleMap("1600 Pennsylvania Avenue, Washington, DC", _
"400 Broad St, Seattle, WA 98109")
Dim strURL As String
Dim ie As Object
'Convert spaces to + sign
strStartingPoint = Replace(strStartingPoint, " ", "+")
strDestination = Replace(strDestination, " ", "+")
strURL = "http://maps.google.com/maps?f=d&hl=en&saddr=" _
& strStartingPoint & "&daddr=" _
& strDestination & "&ie=UTF8&z=5&om=1"
Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate strURL
ie.Visible = True
End Sub
Public Function Mapper_Click _
(strCity As Variant, strState As Variant, _
strAddress As Variant, strZip As Variant) As Variant
' Example of calling from Immediate Window for the Space Needle
' Call Mapper_Click ("Seattle", "WA", "400 Broad St", "98109")
On Error GoTo Err_Mapper_Click
Dim strFullAddress As Variant
Dim strMapquest As Variant
strFullAddress = strAddress & "&city=" & strCity _
& "&state=" & strState _
& "&zipcode=" & strZip
strMapquest = "http://www.mapquest.com/maps/map.adp?searchtype=" _
& "address&formtype=search&countryid=US" _
& "&addtohistory=&country=US&address=" _
& strFullAddress & "&historyid=&submit=Get Map"
'Remove trailing spaces
strMapquest = Trim(strMapquest)
'Change imbedded spaces with plus signs
strMapquest = Replace(strMapquest, " ", "+", 1, , vbTextCompare)
Application.FollowHyperlink strMapquest, , True
strMapquest = vbNullString
Exit Function
MsgBox Err.Number & "-" & Err.Description
Resume Exit_Mapper_Click
End Function
'****************End Code*********************
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Tom Wickerath

2008-01-23 10:22:02 UTC

Permalink

Hi Wayne,

Post by Wayne-I-M
Call in for a cappuccino at any time

Smile.

Post by Wayne-I-M
The code I made was specific to our needs (we don't need the from / to)....

Did you see the second function, named "Mapper_Click"? This sounds similar
to what you are using.

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Post by Wayne-I-M
Hi Tom

Post by Tom Wickerath
Sorry to burst your bubble, but the UK is not the center of the universe

I agree - this is :-)
http://maps.google.co.uk/maps/ms?hl=en&ie=UTF8&msa=0&ll=46.291829,10.850544&spn=0.003151,0.007231&t=h&z=17&om=0&msid=103780121206034963667.00044460d57e1dea96b9d
Call in for a cappuccino at any time
The code I made was specific to our needs (we don't need the from / to). We
use coaches to pickup clients from accross the UK and sometime we don't know
where they live - so I have added this button just so our admin team can
"see" where they live and allocate them to the nearest pickup.
Mind you - not today (or for the next 6 weeks until then end of March) as I
am working in the Alps. It stopped snowing 2 days ago and it now looks like
a ski brochure.
--
Wayne
Manchester, England.

Wayne-I-M

2008-01-23 10:22:01 UTC

Permalink

Hi again Tom

Just for you (not sure if you're in the US) I have altered the code to give
US Zip Codes - this will display the wqhole of the US when clicked. This
code is very simple to change for different countries. If you're not in the
US give me the county and a sample of a postcode / zipcode ect and I'll give
it.

For the USA - You need a textbox called ZipCode somewhere on the form and a
button called MapSearch

Private Sub MapSearch_Click()
Dim MyHyperlink As String
Dim strGoogleLoaction As String

strGoogleLoaction = Replace([ZipCode], " ", "+")
MyHyperlink =
"http://maps.google.co.uk/maps?g=q&hl=en&geocode=&time=&date=&ttype=&q=" &
strGoogleLoaction & "&ie=
UTF8&msa=0&ll=52.802761,-111.972656&spn=45.773251,118.476562&t=h&z=3&om=0&msid=103780121206034963667.00044460d57e1dea96b9d"
Application.FollowHyperlink MyHyperlink
End Sub
--
Wayne
Manchester, England.

Tom Wickerath

2008-01-23 10:56:01 UTC

Permalink

Yep, I'm in the US.
Thanks.

Ummm.....I hope you aren't the one busy marking your own posts as helpful.
Seems kind of conincidental that your first posting is already marked with "5
out of 6 people found this post helpful", while your last reply is marked
with 3 out of 3 people. Very few people ever take the time to mark any posts,
so this just kind of looks a bit suspicious to me. And for anyone else
reading this, one only sees this if they are viewing this thread using
Internet Explorer, here:

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.access&mid=e91a8de3-996a-4fe8-81df-60dc9af89728

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Post by Wayne-I-M
Hi again Tom
Just for you (not sure if you're in the US) I have altered the code to give
US Zip Codes - this will display the wqhole of the US when clicked. This
code is very simple to change for different countries. If you're not in the
US give me the county and a sample of a postcode / zipcode ect and I'll give
it.
For the USA - You need a textbox called ZipCode somewhere on the form and a
button called MapSearch
Private Sub MapSearch_Click()
Dim MyHyperlink As String
Dim strGoogleLoaction As String
strGoogleLoaction = Replace([ZipCode], " ", "+")
MyHyperlink =
"http://maps.google.co.uk/maps?g=q&hl=en&geocode=&time=&date=&ttype=&q=" &
strGoogleLoaction & "&ie=
UTF8&msa=0&ll=52.802761,-111.972656&spn=45.773251,118.476562&t=h&z=3&om=0&msid=103780121206034963667.00044460d57e1dea96b9d"
Application.FollowHyperlink MyHyperlink
End Sub
--
Wayne
Manchester, England.

Wayne-I-M

2008-01-23 11:11:01 UTC

Permalink

I "always" make a point of clicking Helpful when someone answer one of my
questions - and I have asked some "really" stupid question :-)
I don't think you can click your own posts - never tried but just assumed.
Maybe (like happens on some of our company websites) someone is mes around.
Oh well it well it make them happy.

I have noticed an error in the code - quite simple to change though. You
get an error message if there is no post/zip code in the field. So I have
changed mine to give a cancel message about this.

I did look at the MVP site about the google thing before I did my own but it
seems to be baseisd toward directions, which we don't need. This is why I
did this - also as someone asked about this a few days ago and I don't like
not knowing how to do something.

Much of the google "stuff" is based on geolocations and API scripts and I
just wanted a simple stand along button without reference to a dll or module.
It works for us so I think I'll leavce it on the forms. The biggest problem
is that (in the UK) for some really mad reason the post office have
copywrited to entire postcode system and so you need to work with latitude
and lontitude(geolocations). My button gets round this. I think you don't
have the same probelm in the US. I googled this and can't see any reference
to copywriting zip codes.

Off to work now - got another happy bunch of people wanting to learn how
"not" to crash into trees :-)
--
Wayne
Manchester, England.

Post by Tom Wickerath
Yep, I'm in the US.
Thanks.
Ummm.....I hope you aren't the one busy marking your own posts as helpful.
Seems kind of conincidental that your first posting is already marked with "5
out of 6 people found this post helpful", while your last reply is marked
with 3 out of 3 people. Very few people ever take the time to mark any posts,
so this just kind of looks a bit suspicious to me. And for anyone else
reading this, one only sees this if they are viewing this thread using
http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.access&mid=e91a8de3-996a-4fe8-81df-60dc9af89728
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

j***@googlemail.com

2015-08-06 11:23:34 UTC

Permalink

Hi Wayne,

Very useful bit of code, many thanks. Now can you suggest how I map extra points onto the same map?!!

best

Jeff Wells

Larry Linson

2008-01-28 09:38:00 UTC

Permalink

Post by Tom Wickerath
Sorry to burst your bubble, but the UK is not
the center of the universe <smile>.

Actually, it may well be, Tom.

I know for a fact that my hometown, Palestine, Anderson County, Texas is the
"Exact Center of the Known World", at least to most of the citizens of
Anderson County. <SMILE>

Larry

Josh

2009-02-13 17:40:04 UTC

Permalink

We're a Microsoft-only shop. Has anyone done this using Microsoft Live Maps,
Microsoft Virtual Earth, Microsoft Streets & Trips, or other Microsoft
mapping tool?

If so, can you point me in the right direction or provide some sample code?

Thanks.

roccogrand

2009-02-13 18:58:16 UTC

Permalink

Josh,

I found the following code to display an address in VE. (I use Access 2007.)

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

Explanation: the address parts are in my table.
A Click event is enabled via a macro with three Actions: (1) MsgBox ="To
use this functionality the database must be trusted." & Chr(13) & Chr(10) &
"If you see the 'Security Warning' bar above, click 'Options' and then
'Enable this content.'"
(2) StopMacro
(3) RunCode
OpenMap([HomeAddress],[HomeCity],[State],[HomePostalCode],[Country])

I can't remember where I found the code but it works. I am now adding it to
all of my apps that contain addresses.

I am also looking for additional code because I would like to map more than
one address and automatically turn on 3D mapping. So if you figure that out,
let me know.

David

Post by Josh
We're a Microsoft-only shop. Has anyone done this using Microsoft Live Maps,
Microsoft Virtual Earth, Microsoft Streets & Trips, or other Microsoft
mapping tool?
If so, can you point me in the right direction or provide some sample code?
Thanks.

Josh

2009-02-13 20:48:36 UTC

Permalink

Thanks! I'll see what I come up with.

Josh

Post by roccogrand
Josh,
I found the following code to display an address in VE. (I use Access 2007.)
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
Explanation: the address parts are in my table.
A Click event is enabled via a macro with three Actions: (1) MsgBox ="To
use this functionality the database must be trusted." & Chr(13) & Chr(10) &
"If you see the 'Security Warning' bar above, click 'Options' and then
'Enable this content.'"
(2) StopMacro
(3) RunCode
OpenMap([HomeAddress],[HomeCity],[State],[HomePostalCode],[Country])
I can't remember where I found the code but it works. I am now adding it to
all of my apps that contain addresses.
I am also looking for additional code because I would like to map more than
one address and automatically turn on 3D mapping. So if you figure that out,
let me know.
David