GPS Latitude and Longitude Geocoding using Microsoft Access

Microsoft Access

Ever wondered to get GPS Latitude and Longitude with Geocoding and Microsoft Access? If you want to enrich a table with GPS Latitude and Longitude it is not so complex, nor so time consuming: you just need the right VBA procedures and functions. Stay with me and you will improve your Access database!

Let’s start describing the user interface. I have a form like this:

GSP Form

In the input section you fill in the address you are looking the coordinates for.

The textboxes are named:

  • txtAddress
  • txtCity
  • txtCountry
  • txtZipCode
  • txtRegion

The command button is named: cmdGeocode

In the result section you will see the coordinates, if found.

The textboxes are named:

  • txtRetAddress
  • txtLatitude
  • txtLongitude
  • txtAccuracy
  • txtStatus

And now the VBA. All the magic is managed by the Geocode command button.

1. Create a new module. (Open Microsoft Visual Basic Editor ALT+F11, then from the menu bar Insert – Module)

2. At the beginning create a public type. It will be used later to store coordinates:

Public Type tGeocodeResult
 dblLatitude As Double
 dblLongitude As Double
 strRetAddress As String
 strAccuracy As String
 strStatus As String
End Type

3. Create the function to get coordinates from Google

Important. The code was created many years ago by Philben. Unfortunately the site is no more available and I regret not to be able to personally give credits to Philben: Philippe, je suis désolé de ne pas pouvoir te remercier!

'---------------------------------------------------------------------------------------
' Procedure : Geocode with Google Geocoding API v3
' Version : 1.01
' DateTime : 03/03/2011
' Author : Philben
' Purpose : converting addresses into geographic coordinates
' Parameter : No mandatory. string format or NULL
' Reference : http://code.google.com/intl/fr-FR/apis/maps/documentation/geocoding/index.html
' Remark : Query limit of 2,500 geolocation requests per day
' : A good accuracy is different of a good geocoding !!!
' : Minimum delay between two queries : >= 200 ms
'---------------------------------------------------------------------------------------
Public Function Geocode(Optional ByVal vAddress As Variant = Null, _
 Optional ByVal vTown As Variant = Null, _
 Optional ByVal vPostCode As Variant = Null, _
 Optional ByVal vRegion As Variant = Null, _
 Optional ByVal sCountry As String = "ITALY") As tGeocodeResult
 On Error GoTo myErr
 Dim oXmlDoc As Object
 Dim sUrl As String, sFormatAddress As String

If Not IsNull(vAddress) Then vAddress = Replace(vAddress, ",", " ")
 sFormatAddress = (vAddress + ",") & _
 (vTown + ",") & _
 (vRegion + ",") & _
 (vPostCode + ",") & _
 sCountry
'To create the URL
 sUrl = "http://maps.googleapis.com/maps/api/geocode/xml?address=" & sFormatAddress & "&sensor=false"
''XMLDOM to get the XML response
 Set oXmlDoc = CreateObject("Microsoft.XMLDOM")
 With oXmlDoc
 .Async = False
 If .Load(sUrl) And Not .selectSingleNode("GeocodeResponse/status") Is Nothing Then
 'Status code
 Geocode.strStatus = .selectSingleNode("GeocodeResponse/status").Text
'If a result is returned
 If Not .selectSingleNode("GeocodeResponse/result") Is Nothing Then
'formatted_address
 Geocode.strRetAddress = .selectSingleNode("//formatted_address").Text
'Accuracy
 Geocode.strAccuracy = .selectSingleNode("//location_type").Text
'Latitude and longitude
 Geocode.dblLatitude = Val(.selectSingleNode("//location/lat").Text)
 Geocode.dblLongitude = Val(.selectSingleNode("//location/lng").Text)
 End If
 End If
 End With
 Set oXmlDoc = Nothing
 Exit Function
myErr:
 Set oXmlDoc = Nothing
 Err.Raise Err.Number, , Err.Description
End Function

4. On the cmdGeocode command button click event paste this code:

On Error GoTo myErr
 Dim tGeo As tGeocodeResult
 
 'Start geocoding
 tGeo = Geocode(PrepareAddress(Me.txtAddress), PrepareAddress(Me.txtCity), _
 PrepareAddress(Me.txtZipCode), PrepareAddress(Me.txtRegion), _
 PrepareAddress(Me.txtCountry))
 'Display results
 With tGeo
 Me.txtRetAddress = .strRetAddress
 Me.txtLatitude = .dblLatitude
 Me.txtLongitude = .dblLongitude
 Me.txtAccuracy = .strAccuracy
 Me.txtStatus = .strStatus
 End With
myEnd:
Exit Sub
myErr:
MsgBox Err.Number & vbCrLf & Err.Description, vbExclamation
Resume myEnd

In short using Geocode function I assign to tGeo all the data that Google API returns.

And finally I assign to each of the output textboxes the values.

As you see in the code there is also a nested function, PrepareAddress, required to remove diacritics from the input textboxes. For this function my credits are to R. Dezan.

Private Function PrepareAddress(ByVal vText As Variant) As Variant ' R. Dezan
 Const csIn As String = "ÀÁÂÃÄÅÈÉÊËÌÍÎÏÑÐÒÓÔÕÖÙÚÛÜÝŸÇ"
 Const csOut As String = "AAAAAAEEEEIIIINOOOOOOUUUUYYC"
 Dim i As Long, j As Long
 Dim sText As String

If Not IsNull(vText) Then
 sText = UCase(vText)
 For i = 1 To Len(sText)
 j = InStr(1, csIn, Mid$(sText, i, 1), vbBinaryCompare)
 If j Then Mid$(sText, i, 1) = Mid$(csOut, j, 1)
 Next i
 PrepareAddress = CVar(Replace(Replace(sText, "Œ", "OE"), "Æ", "AE"))
 End If
End Function

Now what next?

At least 3 more steps:

1) You store the coordinates into a table, or even better, you can loop through all the records already existing in the table and call the Geocode procedure and update the records to fill Longitude and Latitude.

2) you create a KML file with the data

3) You upload the KML file to your places in Google Maps

pushpin

 

This will be covered in another post.

Enjoy it!