Google Earth- Making KML files

ccg_0004 · 06-24-2008, 08:54 AM

Hi all- I got this code off of a blog. What I want to do is make a .KML file based on the "address" field in the table "GoogleEarth". I do not understand, however, how to get this code to work for my needs.

the first error I come across is that it tells me the script needs a End Sub to it. When I add the End Sub at the end of the code it still fails with the same error.

and even where to put this code? behind a button on a form?

Thanks for any help.

Code:

Option Compare Database
 
Function XMLAttributeOutputToFile()
 
Dim MyDB As Database
Dim MyRS As Recordset
Dim fld As Field
Dim strText As String
Dim MyTableName As String
Dim QryOrTblDef As String
 
QryOrTblDef = "toKML"
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(QryOrTblDef)
 
Open "C:\" & QryOrTblDef & ".kml" For Output Shared As #1
 
Print #1, "<?xml version=""1.0"" encoding=""UTF-8""?>"
Print #1, "<kml xmlns=""http://earth.google.com/kml/2.1"">"
Print #1, "<Document>"
 
With MyRS
Do Until .EOF
   Print #1, "   <Placemark>"
   Print #1, "      <Point>"
   strText = "         <coordinates>" & MyRS.Fields(0) & "," & ...
                                      MyRS.Fields(1) & "</coordinates>"
   Print #1, strText
   Print #1, "      </Point>"
   Print #1, "   </Placemark>"
   .MoveNext
Loop
End With
 
Print #1, "</Document>"
Print #1, "</kml>"
 
Egress:
On Error Resume Next
Close #1
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
Exit Function
 
ErrHandler:
MsgBox Err.Description
Resume Egress
End Function

ccg_0004 · 06-25-2008, 08:41 AM

Here is what I came up with. The code runs a query that gets the addresses from the customers table. Then the code runs that generates a new KML file that can be opened from Google Earth.

Code:

Option Compare Database
Private Sub Command33_Click()
DoCmd.OpenQuery "ENTERYOURQRYORTABLEHERE"
'Export data to text file
 
Dim MyDB As Database
Dim MyRS As Recordset
Dim fld As Field
Dim strText As String
Dim MyTableName As String
Dim QryOrTblDef As String
QryOrTblDef = "ENTERYOURQRYORTABLEHERE"
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(QryOrTblDef)
 
Open "ENTERKMLFILENAME" For Output Shared As #1
 
Print #1, "<?xml version=""1.0"" encoding=""UTF-8""?>"
Print #1, "<kml xmlns=""http://earth.google.com/kml/2.1"">"
Print #1, "<Document>"
Print #1, "  <name>tools.kml</name>"
Print #1, "     <Folder>"
Print #1, "        <name>Tools</name>"
Print #1, "        <open>1</open>"
Print #1, "        <Folder>"
Print #1, "            <name>Tools</name>"
Print #1, "            <open>1</open>"
Print #1, "            <Snippet maxLines=""2"">Tools</Snippet>"
Print #1, "            <description><![CDATA[]]></description>"
With MyRS
Do Until .EOF
Print #1, "            <Placemark>"
'Print #1, "                <Point>"
strText = "                 <description><![CDATA[" & MyRS.Fields(3) & "<br><br>" & MyRS.Fields(4) & 
"]]></description>"
Print #1, strText
strText = "                 <address> " & MyRS.Fields(0) & "</address>"
Print #1, strText
strText = "                 <name>" & MyRS.Fields(1) & "</name>"
Print #1, strText
'Print #1, "                    <Snippet maxLines="; 2; "></Snippet>"
'Print #1, "                    <description><![CDATA[]]></description>"
'Print #1, "                    <LookAt>"

'Print #1, "                    </LookAt>"
'Print #1, "                </Point>"
Print #1, "            </Placemark>"
.MoveNext
Loop
End With
Print #1, "        </Folder>"
Print #1, "     </Folder>"
Print #1, "   </Document>"
Print #1, "</kml>"
Close #1
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
'Shell "ENTERKMLFILENAMEHERE"
End Sub

georgedwilkinson · 06-25-2008, 09:02 AM

This is pretty slick. So, you can put a link in your database for one of your users to take a look at a customer's location information. Very nice.

DCrake · 06-26-2008, 12:57 AM

Quote:

Option Compare Database
Private Sub Command33_Click()
DoCmd.OpenQuery "ENTERYOURQRYORTABLEHERE"
'Export data to text file

Dim MyDB As Database
Dim MyRS As Recordset
Dim fld As Field
Dim strText As String
Dim MyTableName As String
Dim QryOrTblDef As String
QryOrTblDef = "ENTERYOURQRYORTABLEHERE"
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(QryOrTblDef)

Open "ENTERKMLFILENAME" For Output Shared As #1

Print #1, "<?xml version=""1.0"" encoding=""UTF-8""?>"
Print #1, "<kml xmlns=""http://earth.google.com/kml/2.1"">"
Print #1, "<Document>"
Print #1, " <name>tools.kml</name>"
Print #1, " <Folder>"
Print #1, " <name>Tools</name>"
Print #1, " <open>1</open>"
Print #1, " <Folder>"
Print #1, " <name>Tools</name>"
Print #1, " <open>1</open>"
Print #1, " <Snippet maxLines=""2"">Tools</Snippet>"
Print #1, " <description><![CDATA[]]></description>"
With MyRS
Do Until .EOF
Print #1, " <Placemark>"
'Print #1, " <Point>"
strText = " <description><![CDATA[" & MyRS.Fields(3) & "<br><br>" & MyRS.Fields(4) &
"]]></description>"
Print #1, strText
strText = " <address> " & MyRS.Fields(0) & "</address>"
Print #1, strText
strText = " <name>" & MyRS.Fields(1) & "</name>"
Print #1, strText
'Print #1, " <Snippet maxLines="; 2; "></Snippet>"
'Print #1, " <description><![CDATA[]]></description>"
'Print #1, " <LookAt>"

'Print #1, " </LookAt>"
'Print #1, " </Point>"
Print #1, " </Placemark>"
.MoveNext
Loop
End With
Print #1, " </Folder>"
Print #1, " </Folder>"
Print #1, " </Document>"
Print #1, "</kml>"
Close #1
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
'Shell "ENTERKMLFILENAMEHERE"
End Sub

Can you explain what ![CDATA[] does or what is is?

Also could you repost solution with sample addresses instead of rsFields(n)

ccg_0004 · 06-26-2008, 06:42 AM

The CData reference is used to make the text inside of the brackets not be read by a browser as a markup or reference tag. That is, if the tag <bold>Crtl+B</bold> was inside the brackets the browser would not read it as a html code. Here it doesn't appear to be used correctly. Thanks for finding that.

The myRS.Fields(n) is simply a reference to the field number in the query recordset. The query returns addresses from the customer list and then concates them into a few fields in the googleearth query. Then the VB loads the query data and goes through the specified fields in the recordset code. If you only want the first field, use myRS.Fields(0), etc.

I am going to post more expanded code for adding layers later...

ccg_0004 · 06-26-2008, 10:52 AM

Here is the finished code I have for using multiple layers (in this case four layers) in the KML file. Enjoy!

Code:

Option Compare Database
Private Sub Command33_Click()
DoCmd.OpenQuery "qry_Layer1"
DoCmd.OpenQuery "qry_Layer2"
DoCmd.OpenQuery "qry_Layer3"
DoCmd.OpenQuery "qry_Layer4"
'Export data to text file
 
Dim MyDB As Database
Dim MyGS As Recordset
Dim MyTS As Recordset
Dim MyAD As Recordset
Dim MyMWD As Recordset
Dim fld As Field
Dim strText As String
Dim MyTableName As String
Set MyDB = CurrentDb
Dim QryGS As String
Dim QryTS As String
Dim QryMWD As String
Dim QryAD As String
QryGS = "qry_Layer1"
QryTS = "qry_Layer2"
QryAD = "qry_Layer3"
QryMWD = "qry_Layer4"
Set MyGS = MyDB.OpenRecordset(QryGS)
Set MyTS = MyDB.OpenRecordset(QryTS)
Set MyAD = MyDB.OpenRecordset(QryAD)
Set MyMWD = MyDB.OpenRecordset(QryMWD)
'This opens the recordset of the 4 returned queries
 
Open "O:\Map.kml" For Output Shared As #1
 
Print #1, "<?xml version=""1.0"" encoding=""UTF-8""?>"
Print #1, "<kml xmlns=""http://earth.google.com/kml/2.1"">"
Print #1, "<Document>"
Print #1, "  <name>Map.kml</name>"
Print #1, "     <Folder>"
Print #1, "        <name>COMPANY NAME</name>"
Print #1, "        <open>1</open>"
Print #1, "        <Folder>"
Print #1, "            <name>Layer1</name>"
Print #1, "            <open>1</open>"
Print #1, "            <Snippet maxLines=""2"">LAYER DESCRIPTION</Snippet>"
Print #1, "            <description><![CDATA[]]></description>"
With MyGS
Do Until .EOF
Print #1, "            <Placemark>"
'Print #1, "                <Point>"
strText = "                 <description><![CDATA[FIELDNAME3:" & MyGS.Fields(3) & "<br>FIELDNAME4:" & MyGS.Fields(4) & 
"<br>FIELDNAME5:" & MyGS.Fields(5) & "<br>FIELDNAME6:" & MyGS.Fields(6) & "]]></description>"
Print #1, strText
strText = "                 <address> " & MyGS.Fields(0) & "</address>"
Print #1, strText
strText = "                 <name>" & MyGS.Fields(1) & "</name>"
Print #1, strText
'Print #1, "                    <Snippet maxLines="; 2; "></Snippet>"
'Print #1, "                    <description><![CDATA[]]></description>"
'Print #1, "                    <LookAt>"
'Print #1, "                    </LookAt>"
'Print #1, "                </Point>"
Print #1, "            </Placemark>"
.MoveNext
Loop
End With
MyGS.Close
Print #1, "        </Folder>"
Print #1, "        <Folder>"
Print #1, "            <name>Layer2</name>"
Print #1, "            <open>1</open>"
Print #1, "            <Snippet maxLines=""2"">LAYER DESCRIPTION</Snippet>"
Print #1, "            <description><![CDATA[]]></description>"
With MyTS
Do Until .EOF
Print #1, "            <Placemark>"
'Print #1, "                <Point>"
strText = "                 <description><![CDATA[FIELDNAME3:" & MyTS.Fields(3) & "<br>FIELDNAME4:" & MyTS.Fields(4) & 
"<br>FIELDNAME5:" & MyTS.Fields(5) & "<br>FIELDNAME6:" & MyTS.Fields(6) & "]]></description>"
Print #1, strText
strText = "                 <address> " & MyTS.Fields(0) & "</address>"
Print #1, strText
strText = "                 <name>" & MyTS.Fields(1) & "</name>"
Print #1, strText
'Print #1, "                    <Snippet maxLines="; 2; "></Snippet>"
'Print #1, "                    <description><![CDATA[]]></description>"
'Print #1, "                    <LookAt>"
'Print #1, "                    </LookAt>"
'Print #1, "                </Point>"
Print #1, "            </Placemark>"
.MoveNext
Loop
End With
MyTS.Close
Print #1, "        </Folder>"
Print #1, "        <Folder>"
Print #1, "            <name>Layer3</name>"
Print #1, "            <open>1</open>"
Print #1, "            <Snippet maxLines=""2"">LAYERDESCRIPTION</Snippet>"
Print #1, "            <description><![CDATA[]]></description>"
With MyMWD
Do Until .EOF
Print #1, "            <Placemark>"
'Print #1, "                <Point>"
strText = "                 <description><![CDATA[FIELDNAME3:" & MyMWD.Fields(3) & "<br>FIELDNAME4:" & MyMWD.Fields(4) & 
"<br>FIELDNAME5:" & MyMWD.Fields(5) & "<br>FIELDNAME6:" & MyMWD.Fields(6) & "]]></description>"
Print #1, strText
strText = "                 <address> " & MyMWD.Fields(0) & "</address>"
Print #1, strText
strText = "                 <name>" & MyMWD.Fields(1) & "</name>"
Print #1, strText
'Print #1, "                    <Snippet maxLines="; 2; "></Snippet>"
'Print #1, "                    <description><![CDATA[]]></description>"
'Print #1, "                    <LookAt>"
'Print #1, "                    </LookAt>"
'Print #1, "                </Point>"
Print #1, "            </Placemark>"
.MoveNext
Loop
End With
MyMWD.Close
Print #1, "        </Folder>"
Print #1, "        <Folder>"
Print #1, "            <name>AD</name>"
Print #1, "            <open>1</open>"
Print #1, "            <Snippet maxLines=""2"">LAYER4</Snippet>"
Print #1, "            <description><![CDATA[]]></description>"
With MyAD
Do Until .EOF
Print #1, "            <Placemark>"
'Print #1, "                <Point>"
strText = "                 <description><![CDATA[FIELDNAME3:" & MyAD.Fields(3) & "<br>FIELDNAME4:" & MyAD.Fields(4) & 
"<br>FIELDNAME5:" & MyAD.Fields(5) & "<br>FIELDNAME6:" & MyAD.Fields(6) & "]]></description>"
Print #1, strText
strText = "                 <address> " & MyAD.Fields(0) & "</address>"
Print #1, strText
strText = "                 <name>" & MyAD.Fields(1) & "</name>"
Print #1, strText
'Print #1, "                    <Snippet maxLines="; 2; "></Snippet>"
'Print #1, "                    <description><![CDATA[]]></description>"
'Print #1, "                    <LookAt>"
'Print #1, "                    </LookAt>"
'Print #1, "                </Point>"
Print #1, "            </Placemark>"
.MoveNext
Loop
End With
MyAD.Close
 
Print #1, "        </Folder>"
Print #1, "     </Folder>"
Print #1, "   </Document>"
Print #1, "</kml>"
 
Close #1
Set MyGS = Nothing
Set MyTS = Nothing
Set MyAD = Nothing
Set MyMWD = Nothing
Set MyDB = Nothing
'PauseTime = 5    ' Set duration.
'Start = Timer    ' Set start time.
'Do While Timer < Start + PauseTime
'    DoEvents    ' Yield to other processes.
'Loop
'Finish = Timer    ' Set end time.
'TotalTime = Finish - Start    ' Calculate total time.
Dim RetVal
RetVal = Shell("O:\opengoogleearth.bat", 1)
'Runs a batch file to open the KML file in google earth
End Sub

kidrobot · 06-26-2008, 11:05 AM

can you link the blog you found this in?

ccg_0004 · 06-26-2008, 11:23 AM

Here's the blog:
http://seunet.blogspot.com/2006/11/e...gle-earth.html

Lightwave · 03-11-2009, 08:17 AM

This is way cool.

rio · 03-16-2009, 09:58 PM

Hai.
I love to help. But I'm also a Beginner User and my English also bad.
here i put sample db that i make by combine all knowledge that i get from this forum and other website.

roninftp · 05-01-2009, 05:54 AM

Greetings all, I have a question concerning Microsoft Access and Google Earth KML Files. I have a table called EmployeeTravel that consists of EmployeeID, EmployeeName, VisitStartDate, VisitEndDate, CountryName, and CityName. I would like to be able to map each City Name and have the employee name in the description if there is anybody at the location. Everything that I have seen so far maps out each individual employee but I don't want a map filled with a couple hundred entries. Does anybody have an idea?

honda882000 · 05-01-2009, 09:06 AM

This is awesome :-)

rio · 05-03-2009, 04:24 PM

Quote:

Originally Posted by roninftp

Greetings all, I have a question concerning Microsoft Access and Google Earth KML Files. I have a table called EmployeeTravel that consists of EmployeeID, EmployeeName, VisitStartDate, VisitEndDate, CountryName, and CityName. I would like to be able to map each City Name and have the employee name in the description if there is anybody at the location. Everything that I have seen so far maps out each individual employee but I don't want a map filled with a couple hundred entries. Does anybody have an idea?

Hai... I like to help but... still don't understand what u want. maybe u should give some example.

roninftp · 05-05-2009, 06:09 AM

Lets see if I can give you a a good example.

I have a database with several tables.

(Employee Table)
EmployeeID, Name, Position

Example Data:
001, John Smith, CEO
002, Jane Smith, Department Head
003, Bill Nye, Project Lead

Totalling 1313 Employees

(Movement Tracker)
ID,EmployeeID,StartDate,EndDate,Country,Location,P urpose,Department

Example Data:
1,001, 3 Jan 09, 10 Feb 09, Germany, Berlin, Conference, Plans
2,002, 10 Jan 09, 14 Jan 09, USA, Washington DC., Purchases, Sales
2,003, 10 Jan 09, 14 Jan 09, USA, Washington DC., Tech Expo, R&D

(Country Table)
ID,Country Name

Example Data:
01, USA
02, Germany
03, UK

(City Table)
ID,Country_Code,City_Name,Latitude,Longitude
01,01,Washington DC., SOME NUMBER N, SOME NUMBER E
02,02,Berlin, SOME NUMBER N, SOME NUMBER W
03,02,Frankfurt, SOME NUMBER N, SOME NUMBER W

My movement tracker currently has 263 records in various countries/cities.

Surfing the internet I have seen examples where I can export a report as XML and transform the XML with XSL to format the file as a KML. The only problem with that is that I would end up having 263 spots on my Google Earth map. What I am trying to figure out is if there is a way to organize it where there is only one spot on my Google Earth Map for Washington DC, USA and have in the details of that one spot a listing of the names for 2 people instead of having 2 spots on my Google Earth Map, one for each employee. All of the VB Scripts I have seen perform the same actions as the export I mentioned before.

Any ideas? All my programming experience is Java and Perl, so SQL and VB are new to me. If someone could just give me a basic idea of how I might accomplish this, that would be awesome. But if someone has some extra time on their hands and comes up with the code for this project, that would be even better, but less of a learning experience for myself.

rio · 05-05-2009, 04:38 PM

Quote:

Originally Posted by roninftp

Lets see if I can give you a a good example.

I have a database with several tables.

(Employee Table)
EmployeeID, Name, Position

Example Data:
001, John Smith, CEO
002, Jane Smith, Department Head
003, Bill Nye, Project Lead

Totalling 1313 Employees

(Movement Tracker)
ID,EmployeeID,StartDate,EndDate,Country,Location,P urpose,Department

Example Data:
1,001, 3 Jan 09, 10 Feb 09, Germany, Berlin, Conference, Plans
2,002, 10 Jan 09, 14 Jan 09, USA, Washington DC., Purchases, Sales
2,003, 10 Jan 09, 14 Jan 09, USA, Washington DC., Tech Expo, R&D

(Country Table)
ID,Country Name

Example Data:
01, USA
02, Germany
03, UK

(City Table)
ID,Country_Code,City_Name,Latitude,Longitude
01,01,Washington DC., SOME NUMBER N, SOME NUMBER E
02,02,Berlin, SOME NUMBER N, SOME NUMBER W
03,02,Frankfurt, SOME NUMBER N, SOME NUMBER W

My movement tracker currently has 263 records in various countries/cities.

Surfing the internet I have seen examples where I can export a report as XML and transform the XML with XSL to format the file as a KML. The only problem with that is that I would end up having 263 spots on my Google Earth map. What I am trying to figure out is if there is a way to organize it where there is only one spot on my Google Earth Map for Washington DC, USA and have in the details of that one spot a listing of the names for 2 people instead of having 2 spots on my Google Earth Map, one for each employee. All of the VB Scripts I have seen perform the same actions as the export I mentioned before.

Any ideas? All my programming experience is Java and Perl, so SQL and VB are new to me. If someone could just give me a basic idea of how I might accomplish this, that would be awesome. But if someone has some extra time on their hands and comes up with the code for this project, that would be even better, but less of a learning experience for myself.

This is what can i advice u. first don't think about kml. make the database first. then make the query that have all data u need to put into the kml. for example:

ID COUNTRYNAME LONGITUDE LATITUDE EMPLOYEENAME
1 USA 102.123 5.8927 John, robert, tan
2 GERMANY 72.8264 5.2437 Rizal, Norafiza, Kamal
3 UK 88.2937 4.9234 Risoky, Bhat, Jessy, Lan

after u clear this all out... so u can start with kml.

(sorry my English not so good, hopefully u understand)