Google Earth Data Transfer from Access

 

Google Earth Data Transfer from Access

Displaying point data in Google Earth from Microsoft Access is easy once you have seen the complete VBA code.Whether you have a property management database or an environmental sampling database displaying point data is demonstrated in the following visual basic routine. From the Google Earth documentation we are shown the basic layout of the XML file:

name is optional

<?xml version=”1.0″ encoding=”UTF-8″?>
<kml xmlns=”http://earth.google.com/kml/2.0″>
<Document>
<name>MYDATA</name>
<LookAt>
<longitude>-18.7587</longitude><latitude>18.505</latitude>
<range>5000000</range><tilt>0</tilt><heading>0</heading>
</LookAt>
<Placemark><name>GeoB8501-2</name><Point><coordinates>-18.7587,18.505</coordinates></Point></Placemark>
<Placemark><name>GeoB8501-4</name><Point><coordinates>-18.7587,18.505</coordinates></Point></Placemark>
<Placemark><name>GeoB8502-1</name><Point><coordinates>-18.9338,19.221</coordinates></Point></Placemark>
……
<Placemark>
<description>Some<a href=”http://www.geochemie.uni-bremen.de/kml/images/8509_RFA.jpg”>data</a> </description>
<name>GeoB8509-1</name><Point><coordinates>-18.0892,19.451</coordinates></Point>
</Placemark>
<Placemark><name>GeoB8509-2</name><Point><coordinates>-18.089,19.451</coordinates></Point></Placemark>
<Placemark><name>GeoB8516-3</name><Point><coordinates>-17.4012,19.707</coordinates></Point></Placemark>
……
</Document>
</kml>

Here is the Access Visual Basic code used to create the XML/KML file and to pass this file to Google Earth for display:

Private Sub Prop_Earth_View_btn_Click()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim start_path As String
Dim fs As Scripting.FileSystemObject, f As Scripting.TextStream
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Set fs = CreateObject(“Scripting.FileSystemObject”)

‘ note that we have a table called L_Paths that contains the disk/directory where to store
‘ the XML file
start_path = DLookup(“Path”, “L_Paths”, “Doc_Type_Id=1”) & “”
start_path = Replace(start_path, “\”, “”) & “earth.kml”
‘ an example of the value of start_path might be C:MyDocumentsearth.kml

Set db = CurrentDb

‘ Next we open up the Access table that contains the name of the property to be displayed
‘ and the latitude and longitude of the location of the property
Set rst = db.OpenRecordset(“Select PropertyIDX,property_name,latitude,longitude from m_propertys where property_group_id=” & Me.Property_Group_ID & ” and isnull(latitude)=False”)

‘ Note that this routine displays multiple points.  In this database properties are organized
‘ into Property Groups.  In the select statement above we zero in on the particular
‘ property_group_id shown on the form (me.property_group_id).
‘ Now we loop through each record in the recordset and build the XML file as we go.
‘ but first check to make sure we have at least one record to process…
If rst.EOF Or rst.BOF = True Then
MsgBox “No lat/long coords where found.”
rst.Close
Set rst = Nothing
Exit Sub
End If

‘ OK, we have data so start creating the XML file
rst.MoveFirst
Set f = fs.OpenTextFile(start_path, 2, True)

‘ write out the beginning records of the XML file
f.Write “<kml xmlns=” & Chr(34) & “http://earth.google.com/kml/2.0” & Chr(34) & “>” & vbCrLf
f.Write “<Document>” & vbCrLf

‘ Now loop through each record in the recordset and output the coordinates and
‘ annotation data

Do While Not rst.EOF
f.Write ” <Placemark><name>” & rst!PropertyIDX & “:” & rst!Property_Name &             “</name><Point><coordinates>” & rst!Longitude & “,” & rst!Latitude &     “</coordinates></Point></Placemark>” & vbCrLf
rst.MoveNext
Loop

 

‘ Write out the ending records of the XML file
f.Write “</Document></kml>” & vbCrLf

‘ Cleanup
rst.Close
Set rst = Nothing
f.Close

‘ Explorer will automatically open up Google Earth when passed a file named earth.xml
‘ Note that Google Earth needs to be installed on your computer 🙂
Call Shell(“explorer.exe ” & start_path, vbNormalFocus)
End Sub

Below is an image of the Google Earth display based on the Access table data:

Are you spending hours trying to get Visual Basic to do what you need?  Don’t waste you time anymore.  Contact us today and we can connect into your computer via GotoMeeting and get your VBA code running with a small consulting fee.

Microsoft Office:
MS Access 2000 Through 2016 and Office 365 & Sharepoint

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016