Open Ms Access from Google Earth

[JT Kent]

Hello Darbid,
Thanks for the compliment but am not quite there yet (with respect to my code).

I replaced my string KML file with yours like you told me and still refered to my HTML File in my code to but i kept on getting the following error message:

Error Dialog: An error has occured in the script on this page (Do you wish to continue running the script?)
Eroor: Object Expected

This later leads to Automation Error if you select "yes"

Why should this occur since all KML files refer in the same Earth browser (if you understand what i mean here).

Secondly, i then refered to your HTML file from my code because of the error message above. It initially showed Autralia in a pop-up like you said but further attempts to try it again have yielded no results. I can only see the yellow icon on Autralia.

But what i really want is for you to click on the button that loads the KML file and the earth automatically takes you to that point on the map. For example, when your KML File Loads, i expect the browser to take you directly to Australia not for you to look for Austrailia yourself.

I hope this is possible.

You've been great.
Thanks again.

 

[darbid]

Hi

I have no idea why you are getting that error. I downloaded your files, and just put my string in it. For me it worked.

Once again your first error is a webpage specific error and I am not good at that either. If I were in your position and a forum could not help me I would start with a very simple function with a msgbox in the webpage and see if I could get that to fire.

I am not really going to be able to help you with your KML coding you need to go to the Google Earth API forum for that. But I am pretty sure there is a "flyto" which does that.

I think the Google Earth Program will record your movements to a kml file so you could use that as well. This would be like recording macros in MS programs.

 

[JT Kent]

Hi,
I have some other questions i wish to ask you.
What version of GE are you using? Mine is GE Pro 4.2.0180.1134 (beta). (I think this mught be an issue).

Finally, i realised that in one of your mails, you mentioned that my KML codes might be faulty.

I adjusted the look of the KML code cos when i insert the "& _" string to continue on a new line, Ms Access complains that there are too many line continuations. So i had to improvise by making the code length shorter. Now i want to place the KML code in Access (as it appears in GE) without tampering with it.

Whats my best bet?
Thanks a lot Darbid for all your help.

 

[JT Kent]

Hi,
Finally, i realised that in one of your mails, you mentioned that my KML codes might be faulty.

I adjusted the look of the KML code cos when i insert the "& _" string to continue on a new line, Ms Access complains that there are too many line continuations. So i had to improvise by making the code length shorter. Now i want to place the KML code in Access (as it appears in GE) without tampering with it.

Whats my best bet?

Hello Darbid,
I realised that my KML code was actually faulty? The Pop-up comes out now. The only issues i have are:

1) I want the browser and the "push-pin" icon that identifies a specific Location to display the Location name once not twice or more. For instance, when you load your KML file from the browser, Australia is displayed twice, when u click on the icon, 2 icons appear and 2 "Australia" names appear. I want this displayed only once.

2) I added a hyperlink to one of my placemarks which opens Ms Excel but in the browser, the name of the link is displayed in the pop-up dialog but no hyperlink appears. Any ideas?

3)Well your last mail tells me you cannot help me on this issue. I would like the browser to fly me to the location selected automatically without having to search for it.

I am grateful for the progress i have made.
CHEERS!!!

 

[darbid]

Whilst Google updater is annoying is saves wasting time when someone is using last years version. UPDATE.

 

[darbid]

1) I want the browser and the "push-pin" icon that identifies a specific Location to display the Location name once not twice or more. For instance, when you load your KML file from the browser, Australia is displayed twice, when u click on the icon, 2 icons appear and 2 "Australia" names appear. I want this displayed only once.

I am guessing you have clicked the button twice which will add the pin twice in the same place.

2) I added a hyperlink to one of my placemarks which opens Ms Excel but in the browser, the name of the link is displayed in the pop-up dialog but no hyperlink appears. Any ideas?

You really need to walk before you run. This is the first i have heard about excel. You have kml coding problem. These things we can get sorted once you update your GE and post an example of your kml that is not working.

3)Well your last mail tells me you cannot help me on this issue. I would like the browser to fly me to the location selected automatically without having to search for it.

I pretty much wrote the example with KML with an early version of the API - flyto was all there was. I am not sure if they still have it. There is also Touring. http://code.google.com/apis/earth/documentation/index.html

Once again I would download the GE program and/or get other peoples working KML files to test.

I would also be trying to get access to stream a kml file into a string and then add that to GE, this will mean you can then point it to any kml file to test.

 

[JT Kent]

Hi,
Thanks for your help thus far.

I realised from your example that the Australia placemark shows the yellow icon but if i close the browser and re-open it, the yellow icon no longer appears neither do i see the dialog.

In my case, there is a button that re-loads the browser but when i don this, the yellow icon no longer appears. Is this an issue with the version of GE being used.

Finally could you let me know the version exaclty of GE i should update to?
I will be expecting your help as stated in your last mail.

Thanks

 

[darbid]

When you close an open of course you see nothing cause you make a new instance with nothing added to GE.

I do not know the versions but you are 4 and it is up in the 5's now. Uninstall you version, then go to a website that has GE, it will show a button to install the latest version.

 

[JT Kent]

Hi,
There are some other issues i need your assistance with.

1) I need to load an image from my computer to the dialog in my Placemark. For instance, in your Australia example, i might want to load an image in the
Australia dialog where the image is a .jpg file in the local disk on my computer.

How can i adjust the KML file residing in VBA Access to load the images (from my Local disk to the placemark dialog)?

2) The 2nd issue is very similar. I want to open a word document resident in my Local disk from my placemark just like in question 1. Everytime i paste the
path: file:///C:/....... to the hyperlink tag, it shows no link but just the text to the path. Please how can i correct this?

3) Finally, i remember you mentioned that there could be a "flyto" tag that addresses the issue of the browser travelling to a particular Placemark. I have tried to adjust the KML file to do this but to no avail. Do you think i will need to write a function (in my HTML which will be called in Access) to do this.

CHEERS!!!

 

[darbid]

Hi,
There are some other issues i need your assistance with.

1) I need to load an image from my computer to the dialog in my Placemark. For instance, in your Australia example, i might want to load an image in the
Australia dialog where the image is a .jpg file in the local disk on my computer.

As far as I know it is never gonna happen. Due to the potential security risk GE is not going to get access to a local drive. Remember that normally the way GE works is you go to an internet site that has GE and all the functions. Imagine if they could code it to get access to your local drive. To load images it is the same as how I load a pin if you put the yellow pin URL into IE you will get that pic.

You need to get your own website. There are many free ones where you can save your pictures and then you simply use that URL.

2) The 2nd issue is very similar. I want to open a word document resident in my Local disk from my placemark just like in question 1. Everytime i paste the
path: file:///C:/....... to the hyperlink tag, it shows no link but just the text to the path. Please how can i correct this?

First you cannot put a word document on GE. Second if you want to click on something which then opens a word document you simply use my fake URL method which sends a code to your Webbrowser Control in access then get access to open the document.

3) Finally, i remember you mentioned that there could be a "flyto" tag that addresses the issue of the browser travelling to a particular Placemark. I have tried to adjust the KML file to do this but to no avail. Do you think i will need to write a function (in my HTML which will be called in Access) to do this.

CHEERS!!!

When I learnt all I needed to learn it was in the early stages. at the time KML files had a flyto. It might be the case that it was replaced by Touring. You need to check with Google Reference for what they currently have.

My work around for KMLs not working was to use functions like this below.

//this is used to look at each contry based on the lat,long and alt which depends on the size of the country
function countryview(po_Lat,po_Long,po_Alt){
	var lookAt = ge.createLookAt('');
      lookAt.set(po_Lat,po_Long, 0, ge.ALTITUDE_RELATIVE_TO_GROUND,
              0, 0, po_Alt);
      ge.getOptions().setFlyToSpeed(.2);        
      ge.getView().setAbstractView(lookAt);
}

I show companies in my MDB with Google earth. The User chooses his companies and all of them are put onto the earth.

Then I use code similar to the above. I open a recordset and then send the first lat long to GE. GE then responds when it has reached that point and the next record of the recordset is fired off. Thus creating the look that they are flying around to all the points. Very cool i must say.

 

[JT Kent]

My work around for KMLs not working was to use functions like this below.

//this is used to look at each contry based on the lat,long and alt which depends on the size of the country
function countryview(po_Lat,po_Long,po_Alt){
	var lookAt = ge.createLookAt('');
      lookAt.set(po_Lat,po_Long, 0, ge.ALTITUDE_RELATIVE_TO_GROUND,
              0, 0, po_Alt);
      ge.getOptions().setFlyToSpeed(.2);        
      ge.getView().setAbstractView(lookAt);
}

I show companies in my MDB with Google earth. The User chooses his companies and all of them are put onto the earth.

Then I use code similar to the above. I open a recordset and then send the first lat long to GE. GE then responds when it has reached that point and the next record of the recordset is fired off. Thus creating the look that they are flying around to all the points. Very cool i must say.

Hi,
I have placed the function in my HTML file, declared values for the Longitude, Latitude and Altitude variables and called them with the following line of code

Call mydoc.parentWindow.execScript("countryview('" & po_Lat & "','" & po_Long & "','" & po_Alt & "')", "Jscript")

I keep getting the following error message:
Run-time error 91
Object variable or with block variable not set

What could be responsible for this?

 

[JT Kent]

Hi,
Please ignore my previos mail.

For the first time in a few days, i can finally smile.
The code does work. Thanks again.

I however need more explanation on how i can use that same code to create a tour (GE moving to different Locations via the browser).

I am yet to implement opening documents through the fake URL concept but will be in touch anytime i encounter issues.

Thanks again Darbid.

 

[darbid]

I however need more explanation on how i can use that same code to create a tour (GE moving to different Locations via the browser).

You are now into new territory for me. I want to but have never done this.

The way I would do this is to record a tour in Google Earth Program. This will record a KML file.

As I made a free internet site I would have the kml file save there.

A tour will be a massive string.

Remember what I said before. My workaround was to loop thought a Recordset of places, using your above code to go to each place.

 

[JT Kent]

You are now into new territory for me. I want to but have never done this.

The way I would do this is to record a tour in Google Earth Program. This will record a KML file.

As I made a free internet site I would have the kml file save there.

A tour will be a massive string.

Remember what I said before. My workaround was to loop thought a Recordset of places, using your above code to go to each place.

Hello Darbid,
I agree that a tour will be a massive string. I am not sure that would be an effective way of dealing with the tour issue.

I prefer to loop through the function Call. Do u think it would be more efficient to store the Latitude, Longitude and Altitude of the different points in an array and loop through or is there an easier way of doing this.

Remember that i am still a newbie to coding in VBA and JScript.

Thanks again

 

[darbid]

I prefer to loop through the function Call. Do u think it would be more efficient to store the Latitude, Longitude and Altitude of the different points in an array and loop through or is there an easier way of doing this.

I only store Long and Lat in my database, but then depending on what I am looking at I set the altitude.

The Loop Flying is a little complicated. I will explain it to you, but I am thinking that I really need to update my example. It wont be this week or next but soon.

In the mean time I will explain it to you here.

 

[JT Kent]

Hi Darbid,
it's been a while. Thanks so much for all your support.

I know you mentioned it might take a while before you get back to me on creating a tour by looping through recordsets but if the code is taking a while to update, can you just explain the concept to me?

I have tried to work around it on my own by storing the placemarks in a table and through the use of an array and recordsets, loop through the navigation code (in your last post).

I think i am not looping correctly.
Kindly explain the concept behind your code.

Thanks

 

[darbid]

Ok here we go.

You need to create a FORM recordsource variable - "GE_flight_rs"
And a Boolean as a Form variable as well "GE_flight_ok_move_next". (this means at the top of a form)

Behind your fly button - you open GE_flight_rs with your SQL - move to the first record and set GE_flight_ok_move_next = True

Then I call the following function - this is basically the way to move through your RS and send the new flypoint to GE

Public Sub GE_next_flight_point()
On Error GoTo Err_GE_next_flight_point


Debug.Print "*********************GE_next_flight_point****************"

Dim po_Lat As Double
Dim po_Long As Double
Dim po_Alt As Double
Dim ge_Speed As String

Dim conID As String


'1 appears to take about 1.5 sec
'thus 0.5 will take about 3 sec
'thus 0.25 will take about 6 seconds

'ge_Speed = str_GE_FLY_SPEED

    ge_Speed = 1


ge_Speed = Replace(ge_Speed, ",", ".")

po_Alt = 600000

If Not GE_flight_rs.EOF Then
   
   'two important goto points
    po_Lat = GE_flight_rs![main_lat]
    po_Long = GE_flight_rs![main_long]
    
    conID = Nz(GE_flight_rs![main_contract_ID])
    Debug.Print "*************** " & Nz(GE_flight_rs![main_licensee_name])
    Debug.Print "*************** " & Nz(GE_flight_rs![main_contract_ID])
 
Else
    GE_flight_rs.Close
    Set GE_flight_rs = Nothing
    Exit Sub
End If

If GE_flight_ok_move_next Then
        GE_flight_rs.MoveNext
    End If

Debug.Print "****po_Lat***** " & po_Lat
Debug.Print "****po_Long**** " & po_Long
Debug.Print "****ge_Speed**** " & ge_Speed
Debug.Print "****po_Alt**** " & po_Alt


Call myHTMLDoc.parentWindow.execScript("pointtour('" & po_Lat & "','" & po_Long & "','" & po_Alt & "'," & _
                         ge_Speed & ")", "Jscript")


Exit_GE_next_flight_point:
    Exit Sub

Err_GE_next_flight_point:
    MsgBox Err.Description
    Resume Exit_GE_next_flight_point
End Sub

Now to this point you will only fly to the first point. We need GE to tell us when it gets to the first point and then fire the above Function which will move to the next record.

Here is the code on your HTML page - as you can see it sets up a GE event listener which when the event is fired it tries to navigate to
"window.location = ('03')" My code is "03"

/** 
 * Creating a fake tour.  Each place is sent to the plugin
 * the eventlistener sends a message back once it has reached this place
 *which fires the next call for a new position
 */ 
 function pointtour(po_Lat,po_Long,po_Alt,ge_Speed){
             var lookAt = ge.createLookAt('');
      lookAt.set(po_Lat, po_Long, 30, ge.ALTITUDE_RELATIVE_TO_GROUND,
              0, 40, po_Alt);
      ge.getOptions().setFlyToSpeed(ge_Speed);
      ge.getView().setAbstractView(lookAt);
      window.google.earth.addEventListener(ge.getWindow(), 'mousedown',stopFlight);
      google.earth.addEventListener(ge.getView(), "viewchangeend", pointreached);
     
}


function stopFlight(){
        google.earth.removeEventListener(ge.getView(), "viewchangeend", pointreached);
       window.google.earth.removeEventListener(ge.getWindow(), 'mousedown',stopFlight);
    window.location = ('10');    
}

function pointreached(){
        google.earth.removeEventListener(ge.getView(), "viewchangeend", pointreached);
        window.google.earth.removeEventListener(ge.getWindow(), 'mousedown',stopFlight);
    window.location = ('03');    
}

function goalreached(){
    window.location = ('03');    
}

So in the BeforeNavigate2 event of the webbrowser
I have

    Case 3  'is the point tour returned - there is a counter there which send GE to the next point
        GE_flight_ok_move_next = True
        Call GE_next_flight_point

That should be enough to get you into trouble

 

[darbid]

By the way you will see that I also have a stopflight function event listener. THis is because if you have a long flight time the user would not be able to stop it. So I have a general mouse click listener. If they click this the code "10" would be sent to the webbrowser BeforeNavigate2 and I then end the RS and stop the flying.

 

[JT Kent]

Here is the code on your HTML page - as you can see it sets up a GE event listener which when the event is fired it tries to navigate to
"window.location = ('03')" My code is "03"

Hi Darbid,
Thanks for your response. I have studied your code but i do have a question.
What does "window.location = ('03')" mean? What information were you trying to pass across when you mentioned: "My code is "03" "?

When i leave the window.location = (03) in my HTML code i get an error message from the browser saying "there was an error in the script. Do wish wish to continue ruuning the script?"

But when i comment that part of the code (including window.location('10') part of the stopflight function), the browser navigates to only the first location.

Thanks for your help.

/
function stopFlight(){
        google.earth.removeEventListener(ge.getView(), "viewchangeend", pointreached);
       window.google.earth.removeEventListener(ge.getWindow(), 'mousedown',stopFlight);
    window.location = ('10');    
}

function pointreached(){
        google.earth.removeEventListener(ge.getView(), "viewchangeend", pointreached);
        window.google.earth.removeEventListener(ge.getWindow(), 'mousedown',stopFlight);
    window.location = ('03');    
}

function goalreached(){
    window.location = ('03');    
}

 

[darbid]

/** 
 * Creating a fake tour.  Each place is sent to the plugin
 * the eventlistener sends a message back once it has reached this place
 *which fires the next call for a new position
 */ 
 function pointtour(po_Lat,po_Long,po_Alt,ge_Speed){
             var lookAt = ge.createLookAt('');
      lookAt.set(po_Lat, po_Long, 30, ge.ALTITUDE_RELATIVE_TO_GROUND,
              0, 40, po_Alt);
      ge.getOptions().setFlyToSpeed(ge_Speed);
      ge.getView().setAbstractView(lookAt);
      window.google.earth.addEventListener(ge.getWindow(), 'mousedown',stopFlight);
      google.earth.addEventListener(ge.getView(), "viewchangeend", pointreached);
     
}


function stopFlight(){
        google.earth.removeEventListener(ge.getView(), "viewchangeend", pointreached);
       window.google.earth.removeEventListener(ge.getWindow(), 'mousedown',stopFlight);
    window.location = ('10');    
}

function pointreached(){
        google.earth.removeEventListener(ge.getView(), "viewchangeend", pointreached);
        window.google.earth.removeEventListener(ge.getWindow(), 'mousedown',stopFlight);
    window.location = ('03');    
}

function goalreached(){
    window.location = ('03');    
}

This is a function - very similar to a function in VBA or any other language. It first sets a lookat point and then simply looks at that view. You need to review the Google API to understand it better.

The function also sets two event listeners.

window.google.earth.addEventListener(ge.getWindow(), 'mousedown',stopFlight);

This is a window wide (whole webbrowser) event listener, it is listening for the mousedown event and when that happens it calls the "stopFlight" function.

google.earth.addEventListener(ge.getView(), "viewchangeend", pointreached);

similarly this is a google earth wide listener which listens for a special event that GE throws and that is "viewchanged" this event is thrown when the GE reaches the viewpoint. Then it calls the function "pointreached". This is the event listener which is the key to making your flight.

The functions then remove the eventlisteners WHICH IS IMPORTANT and then fires a navigation change. Did you put window.location into google search and read??? This is going to try and navigate to window.location = ('03'); a page named "03" which of course does not exist. You are going to CANCEL the navigation see in the URL what page it is (in this case 03) and know that the next point needs to be given to GE.

Whilst it is all a VBA/Access project I really must say again that the Javascript stuff is not really for this forum and I only learnt enough to be dangerous with GE.