Access and Mapping Software

Posted on 2011-05-11
Last Modified: 2012-06-21
I have built a dispatching database for a medical transport company.  They want to integrate it with Google Maps or MapPoint.  I only know Access and Excel VBA.  I know that MapPoint has a SQL Server component, but I don't know SQL Server.  Any thoughts on how I should proceed.  
Question by:Sasha42
    LVL 39

    Assisted Solution

    Try to search Google Maps API and MapPoint API. There are many examples and tutorials. May be one is your case.
    LVL 65

    Expert Comment

    What are your intentions on using this. Is this to map a route or to show places based on zip code or something. You could try this way, not as elegant as coding using google maps api or something though

    create a web browser control in your form then navigate to the url based on the zip code. Obviously you need the url (can test in browser, note url and build that). Use the .navigate method to load urls

    Accepted Solution

    ** This will open Google maps in IE **

    Public Sub OpenGoogleMaps(strAddress1 As String, strAddress2 As String, strAddress3 As String, strAddress4 As String, _
                                strAddress5 As String, strFormName As String, strControlName As String)
        On Error GoTo HandleErrors
        Dim strLinkUrl As String ' address of provider passed to Googlemaps in the URL
        Dim strPath As String ' Google maps URL
        Dim strAddr() As String ' used to split up address string
        Dim strTest As String
        Dim strMsg As String
        Dim i As Integer
        strTest = RTrim(strAddress1) & RTrim(strAddress2) & RTrim(strAddress3) & RTrim(strAddress4) & RTrim(strAddress5)
        If Len(strTest) > 0 Then
            ' this is the URL to Google maps
            strPath = ""
            ' setup the address URL to be passed by including the street, city and state
            'strLinkUrl = RTrim(strStreetAddress) & "+" & RTrim(strStreetAddress) & "+" & RTrim(strStreetAddress)
            strLinkUrl = RTrim(strAddress1) & "+" & RTrim(strAddress2) & "+" & RTrim(strAddress3) & "+" & RTrim(strAddress4) & "+" & RTrim(strAddress5)
            ' split up the address URL so that space between words can be removed.
            strAddr = Split(strLinkUrl, " ", , vbTextCompare)
            ' piece back each address together with "+"
            strLinkUrl = ""
            For i = LBound(strAddr()) To UBound(strAddr())
            strLinkUrl = strLinkUrl & strAddr(i) & "+"
            Next i
            ' remove the trailing "+" and include the Google map URL
            strLinkUrl = strPath & Left(strLinkUrl, Len(strLinkUrl) - 1)
            'Debug.Print strLinkUrl
            ' bring up Google maps passing the address
            Forms(strFormName)(strControlName).HyperlinkAddress = strLinkUrl
            strMsg = "First enter a address."
            MsgBox strMsg, vbCritical, ""
        End If
        Exit Sub
        Call ErrorHandler("basGeneral", "OpenGoogleMaps", "Code Line Number: " & Erl)
        Resume ExitHere
    End Sub

    Author Comment

    als315 - I looked at the Google API. Will I be able to use it only knowing VBA?

    rockiroads - that is exactly what I want to do. I want to be able to pin point several addresses at one time. I have a webrowser control on a form and I have the url. Don't know how to tie them all together.

    AndreSterling - Thanks for the code.  I need it to work for multiple addresses that the vans are going to.

    Thanks to all
    LVL 39

    Expert Comment

    Here you can find good example for MS Access:
    It is good for first step. With API you can do more.
    I think you need Static Maps API:

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now