Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

Subscribe to receive our newsletter.
Subscribe Unsubscribe
Results 1 to 9 of 9

Access VBA Programming Part I - Geocoding with MapPoint

This is a discussion on Access VBA Programming Part I - Geocoding with MapPoint within the MP2K Magazine Articles forums, part of the Map Forums category; A lot of the examples and tools on this web site use Excel VBA with MapPoint. Programming with Access VBA ...

  1. #1
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Access VBA Programming Part I - Geocoding with MapPoint

    A lot of the examples and tools on this web site use Excel VBA with MapPoint.

    Programming with Access VBA with MapPoint is very similar it's just rather than working with Cells and keeping track of Row and Column variables, you iterate over tables and perform Updates and Inserts to store results.

    We are going to demonstrate two simple tools performing common functions with MapPoint - first geocoding and then getting road distance calculations.

    For sample data to work with, I grabbed the current Al's Beef locations from their web site. Al's Beef - Chicago's #1 Italian Beef Franchise Some readers might recognize this as the same restaurant chain used as sample data for this article on MSDN -- SQL Server and MapPoint - Making MapPoint 2010 and SQL Server Spatial Work Together. I guess working with relational databases makes me hungry for hot giardiniera and Italian beef.

    For this first demonstration we will geocode the locations and store several fields of information including Lat/Lon, of course, and also the Matched Quality and resulting matched address. See screenshot of the table below.



    So, if it's not already apparent, for this example we will be doing and Edit or Update to existing records. (For the next example we will be doing an Insert, creating a new record.)

    The code is fairly self-explanatory so I'll get out of the way and just show it to you.

    Code:
    Sub Geocode()
      Dim APP As MapPoint.Application
      Dim MAP As MapPoint.MAP
      Dim FAR As MapPoint.FindResults
      Dim LOC As MapPoint.Location
      
      Set APP = CreateObject("MapPoint.Application")
      APP.Visible = True
      Set MAP = APP.ActiveMap
      
      Dim rs As Recordset
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Al's Beef]")
    
      Do Until rs.EOF = True
        Set FAR = MAP.FindAddressResults(rs("Address"), rs("City"), , rs("State"), rs("Zip"))
        Set LOC = FAR(1)
        rs.Edit
        rs!MP_Latitude = LOC.Latitude
        rs!MP_Longitude = LOC.Logitude
        rs!MP_MatchedTo = GetGeoFieldType(LOC.Type)
        rs!MP_Quality = GetGeoQuality(FAR.ResultsQuality)
        rs!MP_Address = LOC.StreetAddress.Value
        rs.Update
        rs.MoveNext
      Loop
    End Sub
    The program uses the FindAddressResults method and writes the results into the MP_ fields.

    GetGeoFieldType and GetGeoQuality are a couple of helper functions that translate the numeric code into text.



    As you can see from the results, generally everything is OK, but record 10 matched to 551 E Lincoln Highway as opposed to the input address of 551 W Lincoln Highway, so this needs some investigation to see if the input address was incorrect or if it matched to the wrong location.

    In the next article, we will calculate a distance matrix between all of the stores and write all of the results into a new table.
    Last edited by Eric Frost; 09-11-2013 at 05:25 PM.
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  2. #2
    htamraz is offline Junior Member White Belt
    Join Date
    Oct 2013
    Posts
    3

    Re: Access VBA Programming Part I - Geocoding with MapPoint

    Hi

    Where would we find the code for the helper functions referenced in the above example?
    GetGeoFieldType
    GetGeoQuality

  3. #3
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: Access VBA Programming Part I - Geocoding with MapPoint

    htamraz,

    Great question, and welcome to the forums! I hope you find the site & resources useful.

    It's nothing sophisticated, just a lookup to translate the codes to strings that are listed in the help file, see below.

    I have now posted the a complete working Access database with the example data and all the code in our Downloads section ($99).

    Access VBA Programming Example - Geocoding and Calculating a Distance Matrix

    Happy Mapping!
    Eric

    Code:
    Function GetGeoFieldType(code As Long) As String
    Select Case code
     Case -1
      GetGeoFieldType = "Address"
     Case 5
       GetGeoFieldType = "Latitude and Longitude"
     Case 7
       GetGeoFieldType = "Street Address"
     Case 8
       GetGeoFieldType = "City"
     Case 9
       GetGeoFieldType = "Census Tract"
     Case 10
      GetGeoFieldType = "Census Metropolitan Area"
     Case 12
       GetGeoFieldType = "ZIP code"
     Case 17
       GetGeoFieldType = "County"
     Case 18
      GetGeoFieldType = "State"
     Case 19
      GetGeoFieldType = "Country"
    End Select
    End Function
    
    Function GetGeoQuality(code As Long) As String
    Select Case code
     Case 0
      GetGeoQuality = "All Results Valid"
     Case 1
       GetGeoQuality = "First Result Good"
     Case 2
       GetGeoQuality = "Ambiguous Results"
     Case 3
       GetGeoQuality = "No Good Result"
     Case 4
       GetGeoQuality = "No Results"
    End Select
    End Function
    Last edited by Eric Frost; 10-22-2013 at 11:56 AM.
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  4. #4
    htamraz is offline Junior Member White Belt
    Join Date
    Oct 2013
    Posts
    3

    Re: Access VBA Programming Part I - Geocoding with MapPoint

    Eric,

    This example worked for me, but what I really need is to have is a sample database that shows me how to create a data map in MapPoint using Access VBA.

    It would be an even bigger plus/bonus if I could embed this map in an access report.

    Thank you

  5. #5
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: Access VBA Programming Part I - Geocoding with MapPoint

    Would you be interested in working with me to do parts 3 & 4 of this article ??

    Part III - Importing Data from An Access Table and Using DisplayDataMap to Do a Multiple Symbol Map (or whatever). There were recently some articles on using DisplayDataMap so I don't want to cover it too much, maybe just one example similar to whatever you are looking to do.

    Part IV - Pasting a Map Into An Access Report. This article would be a somewhat simpler.

    Let me know what you think!

    Eric
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  6. #6
    htamraz is offline Junior Member White Belt
    Join Date
    Oct 2013
    Posts
    3

    Re: Access VBA Programming Part I - Geocoding with MapPoint

    Yes, count in for this one. How do you want to get started?

  7. #7
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: Access VBA Programming Part I - Geocoding with MapPoint

    Send me an e-mail with some sample data and describe what you want to do. eric@mp2kmag.com

    Also, let me know if this sample data can be published with the article, otherwise I can find something similar to swap in.

    Eric
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  8. #8
    BessiePayne is offline Junior Member White Belt
    Join Date
    Oct 2013
    Location
    Dallas
    Posts
    11

    Re: Access VBA Programming Part I - Geocoding with MapPoint

    Welcome to the forums, i think you got your solution.



    123Movies
    Last edited by BessiePayne; 08-22-2017 at 01:32 AM.

  9. #9
    gdaniels is offline Junior Member White Belt
    Join Date
    Sep 2016
    Posts
    1

    Re: Access VBA Programming Part I - Geocoding with MapPoint

    Eric, I was able to use your code successfully to obtain latitude and longitude for a list of addresses using Access and VBA. I'd like to be able to use a similar process to reverse geocode, have an Access table with fields for latitude and longitude, then run the code to obtain the Address, City, County, State, and Zip Code. Any way your code could be modified to do this?

    Thanks in advance.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to generate drive time matrix rathe than distance matrix in EXCEL
    By john453 in forum MapPoint Desktop Discussion
    Replies: 3
    Last Post: 08-17-2011, 04:08 PM
  2. Mappoint macro getting error on calculating distance
    By shahdelsol in forum MapPoint Desktop Discussion
    Replies: 39
    Last Post: 08-29-2009, 02:29 AM
  3. Calculating automatically with matrix
    By kikolino in forum Development
    Replies: 0
    Last Post: 04-28-2008, 01:40 AM
  4. Distance matrix question
    By routesmith in forum Bing Maps and MapPoint Web Service
    Replies: 1
    Last Post: 04-10-2006, 02:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66