Access 2007 table or query conversion in .kml file and view in Google Earth



This is working example of my code that converts Access 2007 table or query in
Google Earth .kml file
It is fully functional and I’m using it in my sales database.

Of course you should have stored coordinates – longitude/latitude
also – name and description of poi database which have to be exported.

[php]
Private Sub Command0_Click()

DoCmd.OpenQuery "QrySim"

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset

Dim fld As Field
Dim strText As String
Dim MyTableName As String
Dim QryOrTblDef As String
Dim iFile As Integer

QryOrTblDef = "QrySim"
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(QryOrTblDef)
iFile = FreeFile

Open "C:\Documents and Settings\ivantassev.UMC\Desktop\EXCELtoKML\DB\KMLIvan.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.0"">"
Print #iFile, "<Document>"

Print #iFile, " <Folder>"
Print #iFile, " <name>QrySim</name>"
With MyRS
Do Until .EOF

Print #iFile, " <Placemark>"
strText = " <description><![CDATA[" & MyRS.Fields(1) & "]]></description>"
Print #iFile, strText

strText = " <name><![CDATA[" & MyRS.Fields(0) & "]]></name>"

Print #iFile, strText

Print #iFile, "<Point>"

strText = "<coordinates>" & MyRS.Fields(3) & "," & MyRS.Fields(2) & ",0</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

‘resest the recordset and database to blank

‘launches google earth
Call Shell("explorer.exe " & "C:\Documents and Settings\ivantassev.UMC\Desktop\EXCELtoKML\DB\KMLIvan.kml", vbNormalFocus)
End Sub
[/php]

It is posible using the princip of this code also to export any kind of POI formats – .gpx , .plt and etc.
If you have any questions I opened a topic here.

Leave a Reply