Home  Fees/Services  Access 2010 Template  Access Tutorial  Access Download  Articles  Search  Contact  Privacy  Links

Access XML Export Google Earth
How to export Microsoft Access data to Google Earth






Visual Basic Tutorials:
Access-Google Earth
Age Calculation
Change To Proper Case
Email via Gmail #1
Send Gmail Email #2
Inactivity Logout Code
Outlook Email
Read Email Access
Email Attachment
Send Outlook Email
Running Sum
Denormalize Records
Stock Quotes
CreateQueryDef
Find Database Path
Detail-Master Update
Data Field Validation
Field Value New-Old
FindFirst
Access Version
Global Variable Parameter
Global Variables
Active Labels
Files List Box
Mail Merge
Quick Sort
Recordset Filters
Reference Form Field
Select Case
Access Transactions


Visual Basic Function Examples



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:

 



Microsoft Access is a fully capable database and it's primary limitations is the number of concurrent users it will support. Generally, we recommend a limit of 10 to 15 users.

We create databases large and small. Some of our databases help run entire small businesses. We also handle unique businesses such as flyrod manufacturing, cheese making, marina management, and cable tv inventory.


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:\MyDocuments\earth.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.

 










Blue Claw Database Design Downloadable Tutorial:
Running Sum Query Method  








A Blue Claw Software Design Template:

Airline Reservations Database Template
 










A Blue Claw Database Design Article:

Learn How To Select A Consultant
 

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


Contact Information

Copyright 2000-2017 Blue Claw Database Design

Microsoft Access 2007, 2010, 2013 & 2016