PDA

View Full Version : MS Access to Mappoint



graviz
08-25-2009, 11:02 AM
I have a database that automatically outputs a table into a new Excel spreadsheet. In the spreadsheet it contains both addresses and Lat/Long. Is there a way to have a line of VBA code in my access database to also create a new MapPoint file and map all the the addresses in the table/spreadsheet? Thanks in advance for your help!

Eric Frost
08-25-2009, 11:04 AM
Yes! This should be easy to do. You'll find some VBA / MapPoint examples at Working With Excel and MapPoint - MP2K Magazine (http://www.mp2kmag.com/excel)

The Access VBA code that deals with MapPoint should be identical to Excel VBA code.

Eric

graviz
08-25-2009, 11:39 AM
So I'm tyring their tutorial on just opening MapPoint and I'm already running into an error: "ActiveX component can't create object" Here is my code

Private Sub Command25_Click()
Dim oApp As MapPoint.Application

Set oApp = CreateObject("MapPoint.Application.NA.11") <---errors on this
oApp.Visible = True
End Sub

Eric Frost
08-25-2009, 11:40 AM
Take off the .11 . I think it is explicitly trying to reference MapPoint 2004.

If you take off the .11, it will just open whatever version is installed.

Eric

graviz
08-25-2009, 11:44 AM
It didn't error out however nothing happens. It looks like something displays for 1/60 of a second and then disappears. Sorry if I'm doing something basic wrong. I'm still pretty green with the whole VBA.

Eric Frost
08-25-2009, 12:10 PM
No problem. :-) Yes, it's instantiating and then it immediately goes out of scope and then closes. Trying declaring the Application object outside of the Click subroutine. e.g.


Dim oApp As MapPoint.Application

Private Sub Command25_Click()
Set oApp = CreateObject("MapPoint.Application.NA")
oApp.Visible = True
End Sub

hope this helps,
Eric

graviz
08-25-2009, 12:27 PM
Thanks, that worked perfectly! Do you know the reason behind why it would do that?

Eric Frost
08-25-2009, 12:42 PM
When a variable goes out of scope it goes away... the subroutine ended.

It is the same with any other variable you declare in a subroutine. When the subroutine ends, it cleans up all the variables and frees the memory.

You just have to declare the Application object outside of the subroutine if you want to leave it open after the subroutine ends. Hope that makes sense.

Eric

krenkey
08-25-2009, 04:16 PM
Eric do you do the same steps to make this happen with a lat and long in a table

graviz
08-26-2009, 02:55 PM
So I've read through the tutorials and I'm still having trouble with trying to find where to start. I've successfully opened MapPoint with VBA in access but I'm not sure where to go from here. The tutorials seem to focus only on Excel and not access. I've tried to look at the code and figure out how to do it in access but I'm stuck. Does anyone have any tutorials on vba (Access) for the absolute beginner?

Eric Frost
08-26-2009, 03:28 PM
Do you know how to iterate over a table in Access?

Is it really how to automate Access using VBA that you need to learn? I ask because all the MapPoint statements should be the same between Access and Excel. It is the automating Access or Excel VBA part that would be different, right? :-)

Anyway, there are some Access examples in the Articles section on MP2Kmag

MapPoint Articles - MP2K Magazine (http://www.mp2kmag.com/articles.asp)

Just search in the text for "access".

hope this helps,
Eric