Bing API and Access

[wezel99]

Hi, I have an excel document that uses the bing API to pull distance for zip codes. I have VBA code

Function GetDistance(sPCode As String, ePcode As String) As Double
Dim t As String
Dim re As XMLHTTP


t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=mykey"
Set re = New XMLHTTP

re.Open "get", t, False
re.send
Do
DoEvents
Loop Until re.readyState = 4

With re
    s = Split(.responseText, "<TravelDistance>")
End With

GetDistance = Val(s(1))

End Function


Function GetTimeinMins(sPCode As String, ePcode As String) As Double
Dim t As String
Dim re As XMLHTTP

t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=mykey"

Set re = New XMLHTTP
re.Open "get", t, False
re.send
Do
DoEvents
Loop Until re.readyState = 4

With re
    s = Split(.responseText, "<TravelDuration>")
End With

GetDistance = Val(s(1)) / 60

End Function

I wanted to apply this to a access database. I have a tblzipcode with a list of all the zip codes. I wanted to put 2 lookup boxes in a form referencing this table. When selected I want another box to calculate distance.

The excel formula is =getDistance(G34,G35) with g34 and g35 being the two zip codes. How do I make this formula work in access? I tried just reference the text boxes txtzipcode1 and txtzipcode2 but it didnt work.

Can this be done? If so, what am I missing.

 

[vbaInet]

It's not really using an API. It's only sending a URL with some parameters to Bing and reading the returned URL from Bing to (hopefully) get the coordinates.

If it works in Excel, the same way you use it in Excel is the same way you use it in Access. You'll need a reference to one of the MS XML vX.0 to get it working.

Does it compile?

 

[wezel99]

ugh, i cant believe i forgot to turn reference microsoft xml

Thanks!

 

[vbaInet]

Remember to always Debug > Compile. If you had done that it would have prompted you to it.

 

[jdraw]

wezel99,

For an Access approach to distance between 2 zips, 2 lat/longs or any combination see post #38 at this link

Good luck.