<%dim crumb crumb="VB Google Earth" Crumb2="Access XML Export Google Earth" crumb1="How to export Microsoft Access data to Google Earth " %> Google Earth Data Transfer from Access
Home  Fees/Services  Access 2010 Template  Access Tutorial  Access Download  Articles  Search  Contact  Privacy  Links

<%if len(crumb2)>2 then response.write crumb2 else response.write ">
<% response.write crumb1 %>

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
Find Database Path
Detail-Master Update
Data Field Validation
Field Value New-Old
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:


name is optional
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
 <description>Some<a href="http://www.geochemie.uni-bremen.de/kml/images/8509_RFA.jpg">data</a> </description>


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."
    Set rst = Nothing
    Exit Sub
End If

' OK, we have data so start creating the XML file
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

' Write out the ending records of the XML file
f.Write "</Document></kml>" & vbCrLf

' Cleanup
Set rst = Nothing

' 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

Contact Information

<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>

Microsoft Access 2007, 2010, 2013 & 2016