Geocoding using Microsoft Access VBA

Article is on mapping a particular geographical location into global Co-Ordinate values known as Latitude and Longitude. This article will help to understand basic concept behind GPS system and its other counterparts like Russian GLONASS System and Indian IRNSS. In order to complete this functionality we will use Google Map API to track Latitude and Longitude of a particular location. This article can be used for distance calculation between two geographical positions on earth. As shown in screen below to complete this article, first create a table named tblGeoCoding along with few fields.

Geocoding using Microsoft Access VBA Fig 1.1


Now in next step create a simple form frmFatchData, As shown in screen below form will also contain a subform ( Bounded to table created above ) and a Button with Caption Fetch Coordinates

Geocoding using Microsoft Access VBA Fig 1.2


To calculate latitude and Longitude of a particular geographical location, first user needs to fill following information as shown in screen below.

Geocoding using Microsoft Access VBA Fig 1.3


After filling all above details user needs to click on Fetch Coordinate Button. As result of this intended result will be captured into remaining half of above screen (Shown Below).

Geocoding using Microsoft Access VBA Fig 1.4


VBA Code:-

Code associated with On Click event of Fetch Coordinate Button.

Private Sub fatchCoordinates_Click()
Dim tGeoObj As tGeocodeAddressResult, rst As Recordset
DoCmd.Hourglass True
If DCount("ID", "tblGeoCoding") = 0 Then
MsgBox "Enter Address to Calculate latitude and longitude", vbInformation, "information"
DoCmd.Hourglass False
Exit Sub
End If
Set rst = CurrentDb.OpenRecordset("Select * from tblGeoCoding")
While Not rst.EOF
tGeoObj = GeocodeAddress(rst!Address, , rst!zip, rst!State, rst!Country)
With tGeoObj
rst!Latitude = .dLatitude
rst!Longitude = .dLongitude
rst!Accuracy = .sAccuracy
rst!status = .sStatus
End With
DoCmd.Hourglass False
End Sub

We have created a Module named MdlGeoCoding. This module composed of main functionality to enriching description of a given location.

Public Type tGeocodeAddressResult
dLatitude As Double
dLongitude As Double
sRetAddress As String
sAccuracy As String
sStatus As String
End Type

Public Function GeocodeAddress(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) As tGeocodeAddressResult

On Error GoTo ErrHndlr
Dim oXmlDoc As Object
Dim strUrl As String, sFormatAddress As String
If Not IsNull(vAddress) Then vAddress = Replace(vAddress, ",", " ")
sFormatAddress = (vAddress + ",") & (vTown + ",") & (vRegion + ",") & (vPostCode + ",") & sCountry

strUrl = "" & sFormatAddress & "&sensor=false"

Set oXmlDoc = CreateObject("Microsoft.XMLDOM")
With oXmlDoc
.async = False
If .Load(strUrl) And Not .selectSingleNode ("GeocodeResponse/status") Is Nothing Then
GeocodeAddress.sStatus = .selectSingleNode ("GeocodeResponse/status").Text
If Not .selectSingleNode ("GeocodeResponse/result") Is Nothing Then
GeocodeAddress.sRetAddress = .selectSingleNode ("//formatted_address").Text
GeocodeAddress.sAccuracy = .selectSingleNode ("//location_type").Text
GeocodeAddress.dLatitude = Val(.selectSingleNode("//location/lat").Text)
GeocodeAddress.dLongitude = Val(.selectSingleNode("//location/lng").Text)
End If
End If
End With

Set oXmlDoc = Nothing
Exit Function
Set oXmlDoc = Nothing
Err.Raise Err.Number, , Err.Description
End Function