Results 1 to 8 of 8
  1. #1
    Join Date
    May 2009
    Posts
    41

    Unanswered: Is it possible to map addresses in a table to a mapping program?

    I have a list of customers and their addresses, and I was wondering if anyone has tried mapping addresses to something like Google Maps/Earth.

    The purpose would be to view routes for several customers in the same area... I would want to run a query to map those customers' locations.

    I'm guessing I would have to store the coordinates in the table, but as far as linking the two, I'm not quite sure.

  2. #2
    Join Date
    Feb 2002
    Posts
    108
    I dont know how to do what you are asking for, but I use MS Streets & Trips and it has an import function. I have several scheduling programs that I wrote export routines to send a group of addresses to a file for the map program to import. It then places pinpoints all over the map showing the locations. Streets & Trips will also allow you to place more info in the file so that the "bubble" that points to each point shows info like customer name, phone, etc...

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    One thing you could do in vba is open google maps/earth and send the address to the website. I have some vba code which does this but I'll have to see if I can find where it is. Basically it's just sending the address info parameters to google map.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    May 2009
    Posts
    41
    I haven't tried it yet, but I found these sites that look like they might work...

    Single location:
    You can use Google Maps with an Acess DB - microsoft.public.access | Google Groups

    Multiple locations w/ KML file:
    Google Earth- Making KML files - Access World Forums

    I think I'll have to use the KML solution, which can then be imported to Google Earth.

  5. #5
    Join Date
    Apr 2009
    Location
    RI
    Posts
    31
    I won't take credit for this function because I didn't write it, however I don't know who did. I found it on a website at some point:

    Code:
    Function MapIt(Address As String, City As String, State As String, Zip As String)
    'Purpose of this function is to take an address that's passed in,
    'and send it to google maps to view
    
        Dim sAddress As String
        sAddress = Nz(Address)
        sAddress = sAddress & IIf(sAddress = "", "", ", ") & Nz(City)
        sAddress = sAddress & IIf(sAddress = "", "", ", ") & Nz(State)
        sAddress = sAddress & IIf(sAddress = "", "", ", ") & Nz(Zip)
        
        If sAddress = "" Then
            MsgBox "There is no address to map."
        Else
            Do While InStr(sAddress, " ")
                sAddress = Replace(sAddress, " ", "+")
            Loop
                'After modifying the address, launch google maps
                Application.FollowHyperlink "http://maps.google.com/maps?f=q&hl=en&geocode=&q=" & sAddress
        End If
        
    End Function

  6. #6
    Join Date
    Mar 2013
    Posts
    26
    Please HELP..
    I have tweeked this code to show me a document/file on my computer for and address. These are a pdf files and for each address I have info on and are named as the address. It could be used for images too. Works great!

    Function OpenPDF(Address, City, State, Zip, Country)

    Dim strAddress As String
    strAddress = Nz(Address)
    strAddress = strAddress & IIf(strAddress = "", "", " ") & Nz(City)
    strAddress = strAddress & IIf(strAddress = "", "", " ") & Nz(State)
    strAddress = strAddress & IIf(strAddress = "", "", " ") & Nz(Zip)
    strAddress = strAddress & IIf(strAddress = "", "", " ") & Nz(Country)
    strAddress = strAddress & IIf(strAddress = "", "", ".pdf")
    If strAddress = "" Then
    MsgBox "There is no address to map."
    Else
    Application.FollowHyperlink "C:\Users\David\Pictures\Lairig Ghru\" & strAddress
    End If

    End Function

    It works perfectly until there is an address with no file named for that address. If there is no file I get this error message;
    Macro Single Step
    error no 31665.

    How do I get an error message box to say "There is no file for this address?" instead of the error?

    Thanks in advance for any help...
    Last edited by kerrmuir; 03-28-13 at 15:32.

  7. #7
    Join Date
    Mar 2013
    Posts
    26
    FIXED!

    Function OpenPDF(Address, City, State, Zip, Country)

    On Error GoTo EndNow

    Dim strAddress As String
    strAddress = Nz(Address)
    strAddress = strAddress & IIf(strAddress = "", "", " ") & Nz(City)
    strAddress = strAddress & IIf(strAddress = "", "", " ") & Nz(State)
    strAddress = strAddress & IIf(strAddress = "", "", " ") & Nz(Zip)
    strAddress = strAddress & IIf(strAddress = "", "", " ") & Nz(Country)
    strAddress = strAddress & IIf(strAddress = "", "", ".pdf")
    If strAddress = "" Then
    MsgBox "There is no address to map."
    Else
    Application.FollowHyperlink "C:\Users\David\Pictures\Lairig Ghru\" & strAddress
    End If

    Exit Function
    EndNow:
    MsgBox "There is no PDF", vbInformation, "No PDF Found!"

    End Function

  8. #8
    Join Date
    Mar 2013
    Posts
    26
    Instead of error trapping code you could use this, thanks to Sinndho

    Code:
    If strAddress = "" Then
    MsgBox "There is no address to map."
    ElseIf Len(Dir(strAddress)) = 0 Then
    MsgBox "There is no PDF", vbInformation, "No PDF Found!"
    Else
    Application.FollowHyperlink "C:\Users\MyDocs\Pictures\" & strAddress
    End If

Posting Permissions

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