Solved

Memory Leakage In Mappoint 2004 Automation

Posted on 2008-10-27
3
389 Views
Last Modified: 2009-02-13
Hi Experts,

I am running VB code within Access 2002 to reference the Mappoint 2004 programming interface. In a nutshell we have a engineers based within a fixed location (Full UK Postcode - eg UB8 2LA), I am using Mappoint to find out which other postcodes (down to the Postcode District level e.g. UB4, UB5, UB6 etc) are within either a specified driving distance or a driving time.

The program works great (all beit a little slowly), however the resource usage of the Mappoint.exe increases rather alarmingly as the routine progresses (going from around 20Mb to over 500Mb as the routine nears completion) The code simply cycles through all valid UB3, UB4, UB5 type postcodes (approx 2500 of them ) which are in an access table and sets Loc1 as the engineers base postcode and Loc2 as the Postcode District we are looking at, it then calculates driving time and distance and moves onto the next etc.

I can modifty the routie to quit mappoint and re-start it after every day 50 postcodes however this slow the process down considerably, I'm wondering whether I'm missing clearing something after each postcode which is why the resource usage is going sky high ?

Anyone any ideas ?

Public Function CalculatePostcodesWithinDrivingDistance()
 

Dim db1 As DAO.Database

Dim qdf1 As DAO.QueryDef

Dim rst1 As DAO.Recordset
 

Dim DrivingTime  As Double

Dim DrivingDistance As Double
 

Dim GeoOneMinute As Double

Dim GeoOneSecond As Double

GeoOneMinute = 1 / 1440

GeoOneSecond = 1 / 86400
 

Dim EngineersBasePostcode As String

Dim DestinationPostcode As String
 

Set db1 = CurrentDb

Set objMapPoint = CreateObject("Mappoint.application")
 

' Make Map Visible and Allow Edits

objMapPoint.Visible = True

objMapPoint.UserControl = True
 

' Set Map Objects

Set objMap = objMapPoint.ActiveMap

Set objRoute = objMap.ActiveRoute

Set objWaypoints = objRoute.Waypoints

    

EngineersBasePostcode = "UB8 2LA"
 

' Cycle through all valid Second Level Postcodes

QueryName1 = "POSTCODES - VALID SECOND LEVEL POSTCODES (QUERY)"

Set qdf1 = db1.QueryDefs(QueryName1)

Set rst1 = qdf1.OpenRecordset

        

MappointMaxDistance = 10

MappointMaxTime = 30
 

Forms![BROWN GOODS - VIEW BY COMPANY]![MappointPostcodesFoundWithinLimits] = Null
 

    If Not rst1.EOF Then rst1.MoveFirst

                

    ' Loop For Each Record To Be Processed

    Do Until rst1.EOF

                        

        DistanceCalculated = 0

        TimeCalculated = 0

        DistanceCalculatedWithinLimits = False

        TimeCalculatedWithinLimits = False

                

        DestinationPostcode = rst1("[SecondLevelPostcode]")

        DestinationTopLevelPostcode = StripToTopLevelPostcode(rst1("[SecondLevelPostcode]"))

        ' Set Start Point as EngineersBasePostcode, call it Loc1

        Set objResults = objMap.FindAddressResults("", "", EngineersBasePostcode)

        If objResults.Count >= 1 Then Set StartPoint = objWaypoints.Add(objResults.Item(1), "Loc1")
 

        ' Set End Point as DestinationPostcode, call it Loc2

        Set objResults = objMap.FindAddressResults("", "", DestinationPostcode)

        If objResults.Count >= 1 Then Set MidPoint1 = objWaypoints.Add(objResults.Item(1), "Loc2")

 

        ' Calculate The Route

        objRoute.Calculate

        

        DistanceCalculated = objRoute.Distance

        If DistanceCalculated > 0 And DistanceCalculated <= MappointMaxDistance Then DistanceCalculatedWithinLimits = True

        

        TimeCalculated = Int(objRoute.DrivingTime / GeoOneMinute)

        If TimeCalculated > 0 And TimeCalculated <= MappointMaxTime Then TimeCalculatedWithinLimits = True

                   

        If DistanceCalculatedWithinLimits = True AND TimeCalculatedWithinLimits = True Then

	Forms![ENGINEERS_POSTCODES]![PostcodesFoundWithinLimits] = Forms![ENGINEERS_POSTCODES]![PostcodesFoundWithinLimits] & NewLineChar() & DestinationTopLevelPostcode

        End If
 

        ' Clear The Route

        objRoute.Clear

        

        ' Move To Next Destination Postcode

        rst1.MoveNext

    

    Loop

    

    rst1.Close

                    

    Set rst = Nothing

    Set db = Nothing
 

End Function

Open in new window

0
Comment
Question by:MDIRECT
  • 2
3 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 22811119
Personally I would set every object to Nothing at the end of the function.
Whatever the theory, I think Access has always been a little unreliable in respect of clearing up.
0
 

Author Comment

by:MDIRECT
ID: 22811729
Hi Peter57r,

Thanks for the reply, I have adjusted the code to clear the waypoints as follows...

' Delete The Waypoints
        MidPoint1.Delete
        Startpoint.Delete
       
        ' Clear The Route
        objRoute.Clear
       
        Set objResults = Nothing

However this has not made any difference. One thing I have noticed however is that as long as the Map is visible with the two lines
    objMapPoint.Visible = True
    objMapPoint.UserControl = True

Then whilst the routine is running, if i manually click on the Mappoint Map and then minimize it, the memory usage drops back to the normal 30-40mb however then climbs back gradually after each route calculation, howver if the application is restored and then minimized again, each time the memory usage can be brough back down again. I have tried performing this manouvre programmatically however it doesn't haev the same effect ! - Anyone any ideas ?

The resource usage of the MSACCESS.EXE process running the Mappoint routine remains constant throughout so I don't think this is an Access issue ?
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 22812026
What I meant was:

Set objMapPoint =nothing
Set objMap =nothing
Set objRoute =nothing
Set objWaypoints =nothing
Set qdf1 =nothing
Set rst1 = =nothing
Set objResults =nothing
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

914 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

21 Experts available now in Live!

Get 1:1 Help Now