Nevermind, I solved my user error.
[/QUOTE]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]
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
strText = "<coordinates>" & MyRS.Fields(4) & "</coordinates>"
<coordinates>45.2719275,-75.7736501</coordinates>
<coordinates>44.9765549,-76.5509205</coordinates>
<?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>
<?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>
SELECT *
FROM Spotter
WHERE (((Spotter.[FirstName])='Rex'));
Set MyRS = MyDB.OpenRecordset(QryOrTblDef)
QryOrTblDef = "MyQuery"
'assigning value to QD
Set QD = db.CreateQueryDef("MyQuery", "SELECT * FROM Spotter ORDER BY Spotter.State,Spotter.County,Spotter.City,Spotter.LastName;")
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.Rex, Thanks. It was the Long, Lat????
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.
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.
...
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.
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?
Hi. Welcome to AWF! You might consider starting out a new thread and just refer to this one. Cheers!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