Google Earth- Making KML files (1 Viewer)

[jdraw]

Does anyone have a sample data base in mdb format showing the code within this thread that is working? If so, could they please post a copy?
Thanks

 

[rexmorgan]

Nevermind, I solved my user error.

Sorry I did not get back to you sooner. I am glad you were able to figure out your error.

 

[rexmorgan]

Re: Multiple Layers

I was wondering if you could give an example of the batch file you use to open the kml file with VBA. I'm using windows 7 and the simple batch file I'm trying to use is:

@Echo off

Start "C:\mycomputer\.......\MyGoogle.kml"


all it does is open the black command window.


Dim RetVal
RetVal = Shell("O:\opengoogleearth.bat", 1)
'Runs a batch file to open the KML file in google earth
End Sub

[/CODE]

[/QUOTE]

I am calling Google Earth from access. I use some vba attached to a hidden command button. The hidden command button code is called from a visible command button. I will post the code below. I hope this helps you out. If not, reply back and I will try to help out.

Private Sub cmdAllSpottersPlot_Click()
    
    'Written by Rex Morgan with help from Access World Forums
    'this version as of 8-21-2010
    'Declaring the variables
    Dim db As DAO.Database
    Dim QD As DAO.QueryDef
    Dim where As Variant

    'assigning value to db
    Set db = CurrentDb

    'Delete existing dynamic query, trap error if it does not exist.
    On Error Resume Next
    'deleting the last version of "MyQuery"
    db.QueryDefs.Delete ("MyQuery")
    On Error GoTo 0



    'assigning value to QD
    Set QD = db.CreateQueryDef("MyQuery", "SELECT * FROM Spotter ORDER BY Spotter.State,Spotter.County,Spotter.City,Spotter.LastName;")

    'executing the command to Open a Query titled "MyQuery"
    DoCmd.OpenQuery "MyQuery", acViewNormal

    'setting up the message box asking whether or not you want to map the results
    kmlAnswer = MsgBox("Would you like to map this query in Google Earth?", vbApplicationModal + vbYesNo + vbDefaultButton2, "Map in Google Earth")
    
    If kmlAnswer = vbYes Then
        Command91_Click
    End If

    'executing the command to Close the form
    DoCmd.Close acForm, Me.Name

End Sub

Private Sub Command91_Click()

'This creates a text file with a KML extension that Google Earth can read
'Created by Rex Morgan 11/21/2010
'This is a hidden command button and is launced from the 'visible' Plot All Spotters button

'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
Dim iFile As Integer
    QryOrTblDef = "MyQuery"
    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset(QryOrTblDef)
    iFile = FreeFile
    
    Open "//ggw-s-win/home/rex.morgan/My Documents/KML/PlotAllSpotters.kml" For Output Shared As #iFile
    
    Print #iFile, "<?xml version=""1.0"" encoding=""UTF-8""?>"
    Print #iFile, "<kml xmlns=""http://earth.google.com/kml/2.1"">"
    Print #iFile, "<Document>"
    Print #iFile, " <name>AllSpotters.kml</name>"
    Print #iFile, " <Folder>"
    Print #iFile, " <name>Spotters</name>"
    Print #iFile, " <open>1</open>"
    Print #iFile, " <description><![CDATA[]]></description>"
    With MyRS
        Do Until .EOF
                
        'The creates the popup baloon i.e. when you click a pincushion
        'this is the text that you see Or the details that you see.
        Print #iFile, " <Placemark>"
                strText = " <description><![CDATA[Name: " & MyRS.Fields(1) & " " & MyRS.Fields(2) & "<br>City: " & MyRS.Fields(6) & "<br> County: " & MyRS.Fields(7) & "<br>Phone: " & MyRS.Fields(14) & "<br>Cell Phone: " & MyRS.Fields(15) & "<br>Location: " & MyRS.Fields(16) & "]]></description>"
                Print #iFile, strText
                
        'The produces the text that is displayed for the point.
                strText = " <name>" & MyRS.Fields(1) & " " & MyRS.Fields(2) & "</name>"
                Print #iFile, strText
            
        
                'This produces the Latitude and Longitude so that Google Earth has coordinates to plot from
            Print #iFile, "<Point>"
            strText = "<coordinates>" & MyRS.Fields(20) & "," & MyRS.Fields(19) & "</coordinates>"
            Print #iFile, strText
            Print #iFile, " </Point>"
                Print #iFile, " </Placemark>"
                .MoveNext
        Loop
    End With
    Print #iFile, " </Folder>"
    Print #iFile, " </Document>"
    Print #iFile, "</kml>"
    Close #iFile
    MyRS.Close
    Set MyRS = Nothing
    Set MyDB = Nothing
    Call Shell("explorer.exe " & "z:\My Documents\KML\PlotAllSpotters.kml", vbNormalFocus)


End Sub

 

[jdraw]

Rex,
What version of Access is your database?
Do you have anything as mdb?

I followed the thread and worked with George Wilkinson's material.
The issue I have is when entering Coordinates, the mapping is all wrong- and I don't see anything obvious.

I think the difficulty is not knowing exactly what the associated MyRs structure is.

You have fields 20 and 19 in your coordinates; but in your post#35 you had

strText = "<coordinates>" & MyRS.Fields(4) & "</coordinates>"

I am trying to use 2 points which are located in Eastern Ontario, Canada
but they are showing up in Antarctica.

My coordinate parms are

<coordinates>45.2719275,-75.7736501</coordinates>
<coordinates>44.9765549,-76.5509205</coordinates>

My kml file generated is

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.1">
<Document>
 <name>KMLTest.kml</name>
 <Folder>
 <name>Spotters</name>
 <open>1</open>
 <Folder>
 <name>Daniels County</name>
 <open>1</open>
 <description><![CDATA[]]></description>
 <Placemark>
 <description><![CDATA[Name: Jack<br>City: Ottawa<br>Phone: 1234]]></description>
 <name>Jack</name>
<Point>
<coordinates>45.2719275,-75.7736501</coordinates>
 </Point>
 </Placemark>
 <Placemark>
 <description><![CDATA[Name: JackAndPat<br>City: Cottage<br>Phone: 5678]]></description>
 <name>JackAndPat</name>
<Point>
<coordinates>44.9765549,-76.5509205</coordinates>
 </Point>
 </Placemark>
 </Folder>
 </Folder>
 </Document>
</kml>

Any advice will be appreciated.

 

[rexmorgan]

OK jdraw. One thing I noticed right away is that you have your latitude first (in your generated kml file). I do not know why Google Earth wants the longitude first but, it does. I am going to include a snipped of a generated kml file from my application.

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.1">
<Document>
 <name>AllSpotters.kml</name>
 <Folder>
 <name>Spotters</name>
 <open>1</open>
 <Folder>
 <description><![CDATA[]]></description>

 <Placemark>
 <description><![CDATA[Name: Aaron Boone<br>City: Fairview]]></description>
 <name>Aaron Boone</name>
<Point>
<coordinates>-104.0421,47.857</coordinates>
 </Point>
 </Placemark>
 <Placemark>
 <description><![CDATA[Name: Agnes Job<br>City: Wibaux]]></description>
 <name>Agnes Job</name>
<Point>
<coordinates>-104.201379,46.981364</coordinates>
 </Point>
 </Placemark>

 </Folder>
 </Folder>
 </Document>
</kml>

 

[rexmorgan]

jdraw have you had any luck? I thought I might try to explain further. The MyRS is a dynamic recordset. The recordset created is from a query titled 'MyQuery'. The SQL statement for the 'MyQuery' query is

SELECT *
FROM Spotter
WHERE (((Spotter.[FirstName])='Rex'));

This is just one example of the dynamic query. But essentially it is a SELECT * (select all fields from table) FROM Spotter (Spotter is the name of the table that holds all info on the weather spotters). The WHERE part of the statement is dependent upon which text field is completed. There are text fields for 'FirstName' and 'LastName', in addition to those 2 there are also some combo boxes that create dynamic queries with things like 'County', 'City', etc.

MyRS is defined in my vba code as

Set MyRS = MyDB.OpenRecordset(QryOrTblDef)

Where QryOrTblDef is set as

QryOrTblDef = "MyQuery"

The MyQuery is set in another bit of vba code and is as follows.

'assigning value to QD
    Set QD = db.CreateQueryDef("MyQuery", "SELECT * FROM Spotter ORDER BY Spotter.State,Spotter.County,Spotter.City,Spotter.LastName;")

It would also be worth noting that the 'MyQuery' is used in several instances. What I mean by that is there is seperate vba code for each field such as, the search by first name, search by last name and the example above is select everyone from the Spotter table.

One other thing to keep in mind is that the recordset fields begin with 0. That is the first field is referenced by (in my case) MyRS.Fields(0). Additionally the fields MyRS.Fields(20) & MyRS.Fields(19) are the longitude & latitude fields respectfully. As I indicated in my earlier post for some reason Google Earth wants the longitude first, then the latitude.

 

[jdraw]

Rex, Thanks. It was the Long, Lat???? Who'd have guessed that?

 

[rexmorgan]

Rex, Thanks. It was the Long, Lat????

Glad to help out. I really don't understand why Google Earth wants it that way in the kml file. Because is you open up Google Earth and type in coordinates it want the lat first then long. It is very strange to me why they have it differ like that.

 

[TicMar]

Hi RexMorgan,

I know your post was from a while ago but I'm hoping you can help. I am trying to do something very similar to what you've described, generating a KML file from an Access DB.
I donwloaded your sample database but I am having problems with it: I don't see the form 'Login', nor the 'KMLtest' button. Unfortunately, I have Access 2003 so I am using a free viewer to open your file and maybe that's the issue. Would it be possible for you to save that sample to Access 2003? Hopefully the script will run also in the older version.

Thanks very much in advance!

I am attaching a zipped file of a sample database. It took me a while to delete sensitive info and replace with bogus data. I believe all of the personal info has been scrubbed.


After you unzip go to the form named 'Login'.
Don't forget to enable content on the security Warning thing.
Click the 'KMLtest' button.
This will generate a KML file that you can open with Google Earth.

Another feature you may find handy is the mapping using the Bing mapping.
You can got to the form named 'SpotterForm'.
Click the picture of the world map (just right of 'Map this address')
This will launch your default web browser and bring up the address in Bing Maps. A pretty handy feature.

Some of the things in this database will not work but the KML part you are interested in does work. I have a completed database with all of the things on it working but I have not attempted to scrub all the personal data out of it yet. If you are still interested in some of the other stuff I will make an attempt to get you another sample that you could use. Hope this helps. If you have any questions just ask, I will do my best to answer.

 

[rexmorgan]

I have changed a number of things since this last post. I will attempt to clean up the latest version of the database into something usable.

Is there a particular step you are having trouble with or were you simply hoping to take my database sample and modify it? Just curious. If you are going to design your own anyway and are stuck at a particular step perhaps I could help.

Either way I will attempt to clean up my database of personal info and upload something for you to use. I hope you find the database helpful.

 

[rexmorgan]

Well it looks like I cannot save my database into a 2003 version. When I attempt to do so it get the following:

'You cannot save this database in a earlier version format, because it uses features that require the current file format.

These features include attachments, multi-valued fields, offline data, data macros, calculated columns, links to unsupported external files, newer sort orders, newer encryption types, and navigation controls.'

If there is any particular item I can help with please let me know. I really enjoyed working on this particular project and am very pleased with the end result.

 

[TicMar]

Thanks for your quick reply.
I am totally new to this, don't know much of VB and only some KML so I was hoping to take a look at your sample and learn from that and possibly use some modified parts for my project.
Any chance you can post your latest version, even though it requires the latest version of Access? I can try to find another computer to run it on.

Cheers!

Well it looks like I cannot save my database into a 2003 version. When I attempt to do so it get the following:

'You cannot save this database in a earlier version format, because it uses features that require the current file format.

These features include attachments, multi-valued fields, offline data, data macros, calculated columns, links to unsupported external files, newer sort orders, newer encryption types, and navigation controls.'

If there is any particular item I can help with please let me know. I really enjoyed working on this particular project and am very pleased with the end result.

 

[TicMar]

Hi rexmorgan,

Would you be willing to share the latest version of your database with the script for KML generation?

Thanks!

 

[ivandgreat]

...
Some of the things in this database will not work but the KML part you are interested in does work. I have a completed database with all of the things on it working but I have not attempted to scrub all the personal data out of it yet. If you are still interested in some of the other stuff I will make an attempt to get you another sample that you could use. Hope this helps. If you have any questions just ask, I will do my best to answer.

Hi rexmorgan,

Do you have any sample db that will open only specific KML file in a GE that is embedded in access form?

thanks.

 

[Varvaroudis]

Thanks, rexmorgan for this great tool! I finally managed to create a working kml out of a client query, but I have one problem I can't solve...

My clients' names are in Greek (Non Unicode). When I export a kml with vba and open it in GE, it shows the points correctly, but messes up the names. If I use names with Latin characters, all is OK.

If I create a point manually in GE with a greek name and save it in kml, and then edit it in notepad++, it displays "Unix", while the files exported by VBA display "DOS/Windows".
If I copy-paste the content from the exported kml (by VBA) to the kml created by GE (with "Unix") and then open it in GE, the Greek names display fine.

Any ideas how I create a "Unix" txt with VBA????

Thanx!

 

[sp2231]

WOW! I know this thread is old but this has helped me so much! This is a really great tool for my purposes. Now with Google Earth Pro being free, I can finally do some neat stuff without having to purchase additional software.

I have a question about this section

strText = " <description><![CDATA[Name: " & MyRS.Fields(1) & " " & MyRS.Fields(2) & "<br>City: " & MyRS.Fields(6) & "<br> County: " & MyRS.Fields(7) & "<br>Phone: " & MyRS.Fields(14) & "<br>Cell Phone: " & MyRS.Fields(15) & "<br>Location: " & MyRS.Fields(16) & "]]></description>"
Print #iFile, strText


I would like one of the MyRS.Fields to appear as a hyperlink. How can I do that?

 

[sp2231]

WOW! I know this thread is old but this has helped me so much! This is a really great tool for my purposes. Now with Google Earth Pro being free, I can finally do some neat stuff without having to purchase additional software.

I have a question about this section

strText = " <description><![CDATA[Name: " & MyRS.Fields(1) & " " & MyRS.Fields(2) & "<br>City: " & MyRS.Fields(6) & "<br> County: " & MyRS.Fields(7) & "<br>Phone: " & MyRS.Fields(14) & "<br>Cell Phone: " & MyRS.Fields(15) & "<br>Location: " & MyRS.Fields(16) & "]]></description>"
Print #iFile, strText


I would like one of the MyRS.Fields to appear as a hyperlink. How can I do that?

Instead of editing the VBA module, I ended up making a new field in the query so when in the information gets dumped into the description, Google Earth recognizes what it is supposed to do with it.

Photo2: "<a href=file:///" & Trim([PhotoPath]) & ">" & Trim([Photopath]) & "</a>"

 

[mtbl]

Hi guys i'm new here and i'm also a newbie in accses. i am quit interested in this topic because i myself is planing to create a database that can call a .kml file. i am using AutoCAD civil 3D to create my .kml file. is it possible to call .kml file in access if it is can anyone help me. thanks

 

[theDBguy]

Hi guys i'm new here and i'm also a newbie in accses. i am quit interested in this topic because i myself is planing to create a database that can call a .kml file. i am using AutoCAD civil 3D to create my .kml file. is it possible to call .kml file in access if it is can anyone help me. thanks

Hi. Welcome to AWF! You might consider starting out a new thread and just refer to this one. Cheers!

 

[mtbl]

sorry guys as i have mentioned i'm new.
joining in forums is also new to me.
by the way how can i start a new thread.
thanks for the reply.