Automating Access and Mappoint

I have a client who I built an Access database for. The systems manages a days worth of client appointments, approx. 10 per day. I'm trying to automate Access to open Mappoint, send the addresses of the 10 appointments, and then have Mappoint create the best route for the day.

Does anyone have experience with this or sample code I can check out. Although I'm good with Access, I've never used Mappoint before.

Any help would be appreciated.

Robby

Hi,

If you do a search on this forum to VBA, Excel, Access, .. it should give you already some articles to start. You have 2 approach, taht is using the object model where you use Mappoint for calculations eventually invisible, The other is the ocx control that you can drop on a form.

But dont hesitate to ask here when you get into some problem :)

There's a few articles that come to mind on MP2Kmag that can help you get started --

Tracking P.F. Chang’s in the Midwest - MapPoint Articles - MP2K Magazine (http://www.mp2kmag.com/a71--pf.changs.access.mappoint.html)

Home Locator Sample Application - MapPoint Articles - MP2K Magazine (http://www.mp2kmag.com/a74--home.locator.mappoint.html)

Using MapPoint from Word, Access and Excel - MapPoint Articles - MP2K Magazine (http://www.mp2kmag.com/a39--word.access.excel.mappoint.html)

You could probably use any one of them to get started, but you will probably like the examples of all three.

Eric

I used the HomeLocator sample and learned much from it. The problem is that it just displays the multiple locations on the map but doesnt plan the route. I can do this manually but would rather have it done for me.
How can I extend the sample code to automatically include the route.
Thanks

Hi,

// set start point
Location loc = map.GetLocation(Lat, Lon, Alt);
wp = route.Waypoints.Add(loc, "");
wp.SegmentPreferences = GeoSegmentPreferences.geoSegmentPreferred;

// set waypoints
loc = map.GetLocation(Lat, Lon, Alt);
wp = route.Waypoints.Add(loc, "");
wp.SegmentPreferences = GeoSegmentPreferences.geoSegmentPreferred;

// set end point
loc = map.GetLocation(Lat, Lon, Alt);
wp = route.Waypoints.Add(loc, "");
wp.SegmentPreferences = GeoSegmentPreferences.geoSegmentPreferred;

// set preferred roads
route.DriverProfile.set_PreferredRoads(GeoRoadType .geoRoadArterial, 0.1);
route.DriverProfile.set_PreferredRoads(GeoRoadType .geoRoadFerry, 0.1);
route.DriverProfile.set_PreferredRoads(GeoRoadType .geoRoadInterstate, 0.9);
route.DriverProfile.set_PreferredRoads(GeoRoadType .geoRoadOtherHighway, 0.6);
route.DriverProfile.set_PreferredRoads(GeoRoadType .geoRoadToll, 0.2);

// set preferred speed
route.DriverProfile.set_Speed(GeoRoadType.geoRoadA rterial, 50);
route.DriverProfile.set_Speed(GeoRoadType.geoRoadI nterstate, 120);
route.DriverProfile.set_Speed(GeoRoadType.geoRoadL imitedAccess, 120);
route.DriverProfile.set_Speed(GeoRoadType.geoRoadO therHighway, 90);
route.DriverProfile.set_Speed(GeoRoadType.geoRoadS treet, 50);

route.DriverProfile.StartTime = System.DateTime.Now;
route.Calculate();
// this hide the direction form
mp.ItineraryVisible = false;

Hello

Can anyone please:-( let me know how the map point can be displayed in an access 2003 form as it keeps on giving errors like "Map point failed to load" etc.

I've searched on this forum but i couldnt find the solution. Some people says its because of XP SP2 incompabilities. Some people says use Access 2000. The latter solution didnt work infact with the removal of SP2 underlying OS would be vulnerable which clients cant bear.

I'm trying to integrate Access2003 with MP2004. What i want to do is to calculate the shortest distance between the two streets as selected in the drop down combo boxes and the map point would automatically calculate the shortest distance as soon as the two fields are populated.

Any help and coding would really be admired and appreciated.

regards
AUR

Hi,

I have no knowledge about VBA, but did you tryed to show the form with only the activex control on it ? If this works then you are already sure all references are ok. next step is to load a map with NewMap method. Do you get that far ? Does mappoint standalone executable runs fine ?

Hello Wilfried
thanks for replying

i' using this code on form's open event

Form_testform!ActiveXCtl10.NewMap geoMapEurope

this works just fine when you place a control for the first time from the toolbox and open the form at runtime and the map gets loaded in activex control, but when you close the form and reopen it again , it starts giving suspicious errors identified previously.

Also my standalone mp2004 is working fine.

i've read several posts, some people are successful in integrating this but i dont know whats the actual code to open it and close.

May be something needs to be done when you'r closing the form , for example i'm using the following code on form's unload event

Form_testform!ActiveXCtl10.ActiveMap.Saved = True

I believe there's not enough issue with the code which is just intended for
loading the map point but can anyone highlight on the issues regarding XP SP2 incompabilities identified previously etc.

thanks

Hi,

I will try. Ok, so the first time it works ok, second time it dont. So this let met concluding that all there is needed to load is ok, because otherwise it should not load without errors the first time.

So maybe something is wrong when the form is destroyed. Can you do the following test:

- Open task manager and search for mappoint.exe it should not be there. if it is then kill it.
- Start your application, still no mappoint.exe should be there.
- Create your form where the control i sitting on. Now you should have 1 inctance of mappoint.exe in memory.
- Destroy the form and check again. There should be no instances left of mappoint.exe

If there is now still an instance of mappoint.exe then there is something wrong when the form is destroyed. But that we see eventually in next step.

Yes i can see an instance which gets started when my form is loaded and destroyed when my form is destroyed. So instance creation of Mappoint.exe is normal in the task manager is normal.

Again when i load my form i see the run time error message

'Map point failed to load. The cause of this failure is unknown'

Again i'm using Access 2003 with mp2004 using active x control 11.0 which comes with it.
OS: WinXP SP2


HELP!:(

Hi,

I found following link: Getting started with the MapPoint Control (http://msdn2.microsoft.com/en-us/library/aa723410.aspx) and it is stated there: "With MapPoint 2004, the MapPoint Control is supported only in Microsoft Visual Basic 6.0 or later. This does not include Visual Basic for Applications (for example, the Visual Basic for Applications included with Microsoft Office)"

But the good news is that I also have found this:

Quote from the article "the application does not use the MapPoint OCX control. It uses an instance of MapPoint that is “pulled” into an Access form."
http://www.mp2kmag.com/articles.asp?ArticleID=74

But unfortionally the mp2kmag server seems down at the moment. But please try to check it. Also check the post form Eric (a little upper in this page). I think this is the solutions for you.

You can get to the article text from Google's cache --

Home Locator Sample Application - MapPoint Articles - MP2K Magazine (http://72.14.203.104/search?q=cache:8g-rECneDygJ:www.mp2kmag.com/articles.asp%3FArticleID%3D74+http://www.mp2kmag.com/articles.asp%3FArticleID%3D74&hl=en&gl=us&ct=clnk&cd=1)

Give it a minute or so to load as Google doesn't cache the images and it tries to load them from the server (that is down).


BTW, I did talk with iPowerWeb tech support again and they said the issue with my dedicated server has been escalated but to give them another 48 hours (since it is Christmas) to come up with a resolution which might mean migrating to another server... which will also take time. Could be a week! I do have a recent backup, maybe I should just started setting up on another ISP.

Hi

Well i do agree with you its not supported for VBA as far as the documentation says but my experience and some postings from other users on this forum suggests that it can work with Access form and I hardly managed to run this with access form.
How ? when you switch to design view directly while the form is open and reopen it it gives such an error like Mappoint failed to load etc however closing the form by using the window close button makes the mappoint map obect persistent in the OCX control.:razz: May be its working just for me but this is what i experienced.


That was the step1 of my work. My next step is to calculate the distance between two streets as selected in the text fields or combo boxes.

The Findresults method and others calculate and returns the collection
whereas i want to calculate the distance between streets selected exactly in the combo boxes.

1- What i want is to have all streets which can be selected from a drop down box with their latitudes and longitudes so that map point takes me directly to the location using GOTO method. The question is can mappoint import all streets with their lat/long for a specific region with few clicks to an excel sheet so that later i can import it in an access table and then populate my combo boxes.
2- Calculate the distance and cost between two streets. I do believe that it can be done using Route planner in map point, but how programmatically
that is when someone dont want to use the find/address tab of the mappoint for individual streets but want to select streets just from the drop down box and have cost/mile and distance automatically populated into another field.


Any help would really be admired and appreciated.

regards
AUR

Hi,

So it finally works. Good. It's not very clear to me wy it works now and not before, and the article say it doesnt. But I do not know VBA environment.

About exporting all streets to Excel you can easy do that. What you have to do is scan the map in a given area with ObjectsFromPoint, adding streetnames to a list, and filter out duplicates. A Location object in MP2006 has a latitude/longitude property but since you are using MP2004 you have to reverse geocode it yourself. Also easy done, do a search to CalcPos on this forum. Code examples are in VB.

After you have the list with names you can store it into an Access table directly.

For calculating route look at the Route object. You can add waypoints to a route (see WayPoints object). And finally each WayPoint has his SegmentPreferences object. You can all do this from your own application.

BTW, I did talk with iPowerWeb tech support again and they said the issue with my dedicated server has been escalated but to give them another 48 hours (since it is Christmas) to come up with a resolution...

They got it back up! At least temporarily.. they haven't told me what the problem was yet :-(

Eric

Thanks eric and wilfried

Wilfried can u please elaborate further "ObjectsfromPoint" i tried in the mappoint standalone but i couldnt see anything regarding this. Infact i see Pushpins which can help in exporting all the data sets but here in Pushpins how to include all streets simultaneously at a time and then with just one single click i can have it exported in an excel sheet.

Because when you select a place in the map it is marked with a pushpin symbol , how can i mark several places to be included in my pushpin.

Also when i select an area in the map, right click and select 'Export to excel' it says 'No data found'.

Again Whats the best, quickest and only optimal way to export all streets?

many thanks

Hi,

I meanth totally in code, not in the standalone mappoint. ObjectsFromPoint returns a collection of Location objects. For each of this collection you can gather the streetnames.

So you start in code at a certain point, then step in increments of eg 25 meter next point and so on. Every street you sort in a list and filter on duplicates. After your whole area is scanned you save them in your database.

Does this make sence, because English is not my native language. If not re-ask, and I will try again :)

Hi

There is no object in his control,

This is the message i used to receive in Access2003/VBA form integrated with MP2004 whenever i closed the form either manually or programmatically.

The form is working fine with all mappoint functionality, but i've to place the control again on the form.

This normally occurs when ur OCX control isnt displayed in the ACtive Xcontrols list, however its still there and i'm now stuck why it happens.

Any help would be greatly appreciated.
many thanks.
AUR

Hi,

I assume this is posted in wrong thread ? If not then please clarify as I dont follow :(

New to this forum and Mappoint

I am trying to automate the importation of data either from Aces or Excel into mappoint.

I saw the home locator example, and that looked really great, I just do not know how to get my own data in there, or change the source table, or even see the code behind it all.

Ant help would be appreciated.

Thnaks in Advance,

Ross