title: Batch request Bing Maps Route API from within Microsoft Access
body: <p>My question centres on using a batch request to resolve the distance between a single postcode in relation to many others, using the Microsoft Bing Maps API within a Microsoft Access database.</p>
  
  <p>I have two tables within the database, <code>I_BasePostcode</code> and <code>I_Postcodes</code>. The <code>I_BasePostcode</code> holds a single postcode for a point of interest (a hospital in this case) and the <code>I_Postcodes</code> hold thousands (up to 30,000) of other postcodes as well as their respective distances from the <code>I_BasePostcode</code>.</p>
  
  <p>The <code>I_BasePostcode</code> looks like:</p>
  
  <pre><code>+----------+
  | Postcode |
  +----------+
  | LS1 3EX  |
  +----------+
  </code></pre>
  
  <p>and an extract from the <code>I_Postcodes</code> table looks like:</p>
  
  <pre><code>+----------+--------------------+
  | Postcode | DistanceFromBase   |
  +----------+--------------------+
  | SW13 9EE |                200 |
  | SW13 9EF |                201 |
  | SW13 9EP |                205 |
  +----------+--------------------+
  </code></pre>
  
  <p>The VBA code which makes this possible is:</p>
  
  <pre><code>Const BASEURL As String = "http://dev.virtualearth.net/REST/V1/Routes/Driving"
  Const BINGKEY As String = "It's a secret"
  
  Public Function GetDistance(FirstLocation As String, SecondLocation As String)
  
      Dim JSON As Object
      Dim EditedLocation As String
  
      ' Parse the JSON output
      Set JSON = JsonConverter.ParseJson(HttpRequestToBing(FirstLocation, SecondLocation))
  
      ' If we get an error on the first pass it's because of Bing not liking 8 digit postcodes
      If JSON("statusCode") &lt;&gt; 200 Then
          If Len(SecondLocation) = 8 Then
              EditedLocation = Left(SecondLocation, 4)
              Set JSON = JsonConverter.ParseJson(HttpRequestToBing(FirstLocation, EditedLocation))
          End If
          If Len(SecondLocation) = 7 Then
              EditedLocation = Left(SecondLocation, 3)
              Set JSON = JsonConverter.ParseJson(HttpRequestToBing(FirstLocation, EditedLocation))
          End If
      End If
  
      ' Catch any errors from the second pass
      If JSON("statusCode") &lt;&gt; 200 Then GoTo ErrorHandl
  
      ' Nasty Bing JSON formatting makes accessing the distance difficult
      GetDistance = Trim(JSON("resourceSets")(1)("resources")(1)("travelDistance"))
  
      Exit Function
  
  ErrorHandl:
      GetDistance = ""
  
  End Function
  
  Function HttpRequestToBing(FirstLocation As String, SecondLocation As String)
  
      Dim BingURL As String
      Dim http As Object
  
      ' Setup the Map URL
      BingURL = BASEURL &amp; "?wp.0=" &amp; URLEncode(FirstLocation) &amp; "&amp;wp.1=" &amp; URLEncode(SecondLocation) &amp; "&amp;avoid=minimizeTolls&amp;du=mi&amp;key=" &amp; BINGKEY
  
      ' Setup the request and authorization
      Set http = CreateObject("MSXML2.ServerXMLHTTP")
      http.Open "GET", BingURL, False
      http.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
      http.send
  
      HttpRequestToBing = http.responseText
  
  End Function
  </code></pre>
  
  <p>Please note I have some additional modules which parse the JSON output which are not part of the scope of my question and I've not included them.</p>
  
  <p>Finally, the query I use to match the postcodes in <code>I_Postcodes</code> to Microsoft Bing is:</p>
  
  <pre><code>UPDATE I_Postcodes, I_BasePostcode 
  SET I_Postcodes.DistanceFromBase = GetDistance([I_BasePostcode].[Postcode],[I_Postcodes].[Postcode])
  WHERE (((I_Postcodes.DistanceFromBase) Is Null));
  </code></pre>
  
  <p>Whilst my query works, it takes about 50 minutes to parse 9,000 postcodes and I'm wondering if there is a way this can be batched to be more efficient?</p>