Link to home
Start Free TrialLog in
Avatar of Corey Haecker
Corey HaeckerFlag for Canada

asked on

How can I plan a route with addresses in an Access database?

Hi there,

I have a client that would like me to automatically plan a route based on selected customers in her database.  She would prefer to use streets and trips. Is it possible? I know that from streets and trips, you can import from an access database, but I would prefer to have a button in access that would initiate the mapping from there.

Thanks,
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Well, of course it's 'possible', however most likely would require quite a bit of coding and you would need an algorithm to follow, and probably tap into Microsoft Map Point and/or Google Maps API.

As to 'how', not really seeing a simple concise answer ...

mx
Avatar of Corey Haecker

ASKER

OK, Well maybe it will help if I explain my line of thought. I can construct a route in google with access by building a web link such as. http://maps.google.com/maps?f=d&hl=en&saddr=33%20Mill%20St,Toronto,Ontario&daddr=10%20Queen%20St%20E,%20Toronto,%20Ontario+to:100%20City%20Center%20Drive%20St%20E,%20Mississauga,%20Ontario+to:55%20Markham%20Rd,%20Toronto,%20Ontario The only issue here is that they do not seem to offer a way to optimize the route. Therefore, would I be able to pull up a map in a similar fashion with Streets and Maps? Or could I use another service that would provide something similar?

Thanks for the help!
Well, since I have not done this before, I won't be of much help.  However, someone here probably has/  If you don't get a response within say 24 hours, hit the Request Attention button and ask the mods to send out an APB (they will).

mx
Avatar of puppydogbuddy
puppydogbuddy

I tend to agree with the very first comment that was entered by DatabaseMX.
I have done this sort of thing and the basics of what you need to do are as follows:
  1. Develop the list of locations to be visited;
  2. Determine the "distance" (which can be expressed as time or physical separation) between each combination of two locations;
  3. Designate the start location;
  4. Iterate through a process that selects the closest "next location" to the current location, starting with the start location.
Essentially, this is somewhat like a Critical Path problem that doesn't have any defined predecessor tasks but requires all tasks to be performed in a sequential order.
I might use Access as the database for storing the data; however, I would NOT use Access as the code basis for performing the calcuations and doing the iterative processing.  I might use C# for the actual processing code; however, I could probably develop the code faster in Delphi. ;-)

create a form with a activex webbrowser control. on the form add a button and two textboxes one for a start address one for an end address. also add a command button

in the on load  event of your form have your webbrowser navigate to google maps
 me.yourwebbrowser.navigate2 "http://maps.google.com/maps?hl=en&tab=wl"

in a command button's onclick event put this code

me.yourwebbrowser.Document.getElementById("d_launch").Click
me.yourwebbrowser.getElementById("d_d").focus
me.yourwebbrowser.getElementById("d_d").Value = Me.txtStart
me.yourwebbrowser.getElementById("d_daddr").focus
me.yourwebbrowser.getElementById("d_daddr").Value = Me.txtEnd
me.yourwebbrowser.getElementById("d_sub").Click


make sure you change the "yourwebbrowser" to the actual name of your webbrowser.
you dont have to use textboxes you can use anything that will hold an address.
you may need a wait command in there but that should do it let me know if you need any help.
You can launch an IE page externally with something like the following:


Dim appName As String
Dim sourceAaddress AS String
Dim destAaddress AS String
sourceAddress=replace(Address1 & " " & City1 & " " & State1, " ", "%20)
destAddress=replace(Address2 & " " & City2 & " " & State2, " ", "%20)
    appName = Environ("ProgramFiles") + "\Internet Explorer\iexplore.exe http://maps.google.com/maps?f=d&hl=en&saddr=" & sourceAddress & "&daddr=" & destAddr
    'MsgBox appName
    Call Shell(appName, 1)

Open in new window

or you could open internet explorer and do the same thing. check out my sample database for an example of this.
GoogleMapsplanrout.mdb
my bad Bodestone:

posted that before i read yours.
No problem. You provided a working example where I kind of took a best guess hacking the code from an instance where I launch a web based reporting tool.
Thanks for the help. Basically I have already created a database that plans a route from A to B. But what I need is a route that is optimized that goes from A to B to C ... with a possibility of 20 destinations. Google will allow you to have multiple destinations, I can do that no problem by constructing a link such as this.
http://maps.google.com/maps?f=d&hl=en&saddr=33%20Mill%20St,Toronto,Ontario&daddr=10%20Queen%20St%20E,%20Toronto,%20Ontario+to:100%20City%20Centre%20Dr,%20Mississauga,%20Peel%20Regional,%20Ontario+to:55%20Markham%20Rd,%20Toronto,%20Ontario

But what Google doesn't do is sort the destinations to plan an optimal route. I would like to find a site or service that could do this for me.

Thanks,
StreamllineIM,
Did you look at the links I provided for you?
Yes Actually, I have been reviewing them. Thanks alot for those! Of all of them, findthebestroute.com seems the most promising. Any Idea if there is a way to manipulate the web address with Microsoft access so that I can pull up a route with all the address with the click of a button in access?
If I understand you correctly, you want to be able to:
1. select/enter a destination (location) on an ms access Trip Destinaton Form (I assumed that you want to rely on Routing Service and not maintain a routing database in ms access).
2. have Access log into the google (or other) routing service and pass it the Destination info
3. have the routing service determine the optimum route and display the map for viewing or printing
4. on logoff from the Routing service, return focus to the Access Trip Destinaton Form

above could probably be modified to provide for a persistent connection to the Routing Service URL and require fewer logons and logoffs from the routing service url.

Is this what you envisioned?
Yes, that is correct. I am not worried about passing focus back and forth, or logon and log off. I would just like to create a link based on the addresses in my db, and send it to the web browser. I can very easily do that with google maps by creating a link such as I posted above, but they do not optimize.
What I am not understanding is why you are saying that google does not provide the optimum route.  If you read the posts in the first link (among other links) I provided, you should see the following:
"It's (the optimum route api) now part of the google maps v3 API. I've just written up a post on how to use it."       http://grabity.blogspot.com/2010/03/route-optimization-using-google-maps.html 
 
I understand that they have API's that handle this, but they ( like the example in the blog) do not have the functionality of google maps. I would much prefer to have a way, even if there was a button on google maps to optimize multiple destinations. I would like to send the user to a page from Microsoft Access like this. http://maps.google.com/maps?f=d&hl=en&saddr=33%20Mill%20St,Toronto,Ontario&daddr=10%20Queen%20St%20E,%20Toronto,%20Ontario+to:100%20City%20Centre%20Dr,%20Mississauga,%20Peel%20Regional,%20Ontario+to:55%20Markham%20Rd,%20Toronto,%20Ontario
Then they can make any alterations that they need,

Or maybe it would be possible to come up with an api that would sort the optimal route, and then direct me to google maps with those destinations in the same order.
Conceptually, if there is a work-around for the problems discussed below, I believe you could accomplish most of what you want with an MS Access form that utilizes textboxes representing segments of the address for the input of the destination address.  

The segments would consist of BuildingNumber, StreetNumber, City, State/Province.  The segment structure is needed because the destination addresses do not have a fixed delimiter to enable parsing into the segments via code.

You could then use another series of textboxes on your form to algorithimically build a string from the data entered in the address segment textboxes for each destination, and concatentate the strings for each destination  into a combined string which will be used to create a hyperlink such that when the hyperlink is clicked, you will be taken to  the google map page just as if you had entered the Search info directly to your Browser.  

The other complications come in because google is where the mileage and optimum route data is maintained/calculated. Research would be required to deternine how the mileage and optimum route detail could be passed to the Access form.

Yes, thanks for your help. I can already do all of this

"Conceptually, if there is a work-around for the problems discussed below, I believe you could accomplish most of what you want with an MS Access form that utilizes textboxes representing segments of the address for the input of the destination address.  

The segments would consist of BuildingNumber, StreetNumber, City, State/Province.  The segment structure is needed because the destination addresses do not have a fixed delimiter to enable parsing into the segments via code.

You could then use another series of textboxes on your form to algorithimically build a string from the data entered in the address segment textboxes for each destination, and concatentate the strings for each destination  into a combined string which will be used to create a hyperlink such that when the hyperlink is clicked, you will be taken to  the google map page just as if you had entered the Search info directly to your Browser.  
"

That is no problem, it is finding a way to optimize that is the issue. Or if I could do the above with the site findthebestroute.com that would also be great
findthebestroute.com is a flashsite and I dont know if it is possible to automate.  a way might be with send keys but that is so sketchy/hacky it’s not worth doing.
If you have accepted not having the mileage data or optmum route data in Access, I think findthebestroute.com  might be your best bet....see below.  You should contact them for the url and parameters needed to open to their route optimizer with the destination addresses passedfrom your ms access db.

From findthebestroute.com "About Us"
We created this route optimizer to help individuals and businesses find the best route between several addresses. The directions and maps are generated using Google Maps so the data is accurate. We plan on adding more features and improving this application if there is a demand. Please send us feed back on how we can improve this site or if there are any additional features that you would like to see added.
all findthebestroute.com is doing is simply looping through the destinations of city's and returning the time/distance trying different combinations. you could do the same thing.

http://maps.randmcnally.com/  seems to have the aprox time + mileage displayed on their search- also it seems to be quick. So what i would do is automate that page the same way as in the database  i posted with Google maps. try different combinations just like they do in findthebestroute.com - once your code finds the quickest route navigate to Google maps and plug them in.
Ok, just to keep you up to date. I found something that does exactly what i wanted. http://www.mapquestapi.com/link-to-mapquest/#parameters mapquest would have been great, except i showed it to my client, and she informed me that she would also like to plug in start times, and times for specific stops, and then how long the stops will be, and hopefuly have the software break up use the requirements to plan 1, 2 or 3 routes based on an 8 hour working day. This is obviously way more sophisticated than i origigally thought. She is willing to pay for something though. Any thoughts? I'm lost!!
good grief.  its hard to find a site that will find your quickest route.
try and find one that also has up to the min construction delays -  stoplight timing settings - and could predict the number of cars that are going to be in the McDonalds drive through that day for lunch

the quickest drive time between two points isn’t going to change as far as Google is concerned no matter what time of day. i would pic an option and then use a gps to recalculate if things are getting hairy out on the road.


that mapquest site does look like a good option. and it does have things on there like avoid highways. you would have to still use some guesstimation but looks like a good option
that might be your answer. you might try using tomtom.com or one of the other gps sites. i think thoes might try and account for construction.
http://routes.tomtom.com

looks kinda like what your client wants- verrry cool
I am not pushing findthebestroute.com, but you need to be aware of what they have.
At the bottom of the page, they have a hyperlink "Show options" which lets you choose the following before calculating the optimum route:
Language:English, French, German, Japanese, Spanish
Mode: Driving, walking
Shortest Route computation: Distance, Time
Avoid Highways: Yes/no checkbox
End location same as Start?
ASKER CERTIFIED SOLUTION
Avatar of Corey Haecker
Corey Haecker
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This is what worked for me