mapinfo - access link

[Davros]

Hi Guys
can't quite seem to find what I want so I thought it would be quicker to post.

how would i go about sending a Map Reference from access to mapinfo? we have a new secratary and I would like for her to find a location in the database table, press a button on a form which then opens MapInfo, sends the grid reference to MapInfo which then displays the exact location. easy!!

can anyone help?

thanks in advance

 

[CyberLynx]

Can't remember where I got this but:

Using MapInfo with Microsoft Access
By Scott McManus
On a recent consultation we needed to connect our clients mapping system (MapInfo) to their data base application (Access). Their Users had grown from a single user to up to 5 users connecting at once via MapInfo and up to 6 data entry clerks concurrently updating their database. The setup they were currently using did not allow shared use of live mapping data.
We found out the following about using MapInfo to display spatial data stored in Access.
MapInfo is a GIS package that is in the price range of small to medium users. It offers several ways to display data from your Access database.
In Visual Basic, through reference to a MapInfo Table.
• As an Object Reference when programming in Visual Basic 6 through the MapInfo 5.0 OLE Automation Type Library (Which allows MapInfo to be automated in much the same way as Microsoft Word does through COM technology)
• As a control that can be inserted when programming Visual Basic 6 that displays a table as a map or thematic map.
MapInfo there are several ways you can map your Access table to a MapInfo Table
• Via the File>Open>Table dialog and choose the access table as the type of table you want to open. This then creates the mapping and definition files so that next time you choose the MapInfo table (*.tab) rather than setting up the connection from scratch with the underlying (*.MDB) file. When you open the *.tab file, MapInfo will remember the settings to open and map the Access table. You have the choice of what query or table you whish to open at this initial stage. If you than need to map your data you need to use the Table>CreatePoints menu options so MapInfo knows what columns are the X and Y coordinates as well as which map projection to use. This method is fast and easy to use, fields can be updated back to the database on saving back to the Access file, however in a collaborative work space this method is a let down. When MapInfo opens the file it wants Exclusive access, so if some one has opened the Access database MapInfo will not open the table/file.
• Via ODBC. Using File>Open ODBC Table. This is a preferred method in a collaboration work space, however it can be tricky to set up new data sources and it is a slower method then the previous one. A new Feature that is an improvement over previous versions is the Option to add a new data source from the File>Open ODBC Table rather than from the ODBC control in Control Panel.
The following are the improvements to Access data access through the recent versions of MapInfo Pro.
Ver 4.0 introduced Built-in read/write capabilities to databases through ODBC
Ver 4.1 introduced Native access to Microsoft Access databases to quickly open and save tables
Ver 5.0 introduced Better connectivity to remote databases for accessing and updating data (Live Access through ODBC)

 

[Davros]

thanks for the reply
I can do what you suggested but the secratary cannot [she's not very technical minded]. I was just looking to see if anyone had actually coded a button that would just send the co-ordinate from an access query to mapinfo, open it, display the map/layer in question and then pinpoint the actual location all in one action.

thanks anyway

 

[Pat Hartman]

I've done this with MapQuest. It was pretty easy.

Private Sub cmdCallMapquest_Click()
On Error GoTo Err_cmdCallMapquest_Click

    Call Mapper_Click(Me.txtCity, Me.txtState, Me.txtAddress, Me.txtPostalCode)
Exit_cmdCallMapquest_Click:
    Exit Sub

Err_cmdCallMapquest_Click:
    MsgBox Err.Number & "-" & Err.Description
    Resume Exit_cmdCallMapquest_Click
    
End Sub

Public Function Mapper_Click(strCity As Variant, strState As Variant, strAddress As Variant, strZip As Variant) As Variant
    On Error GoTo Err_Mapper_Click
     
    Dim strFullAddress As Variant
    Dim strMapquest As Variant

    strFullAddress = strAddress & "&city=" & strCity & "&state=" & strState & "&zipcode=" & strZip
    strMapquest = "http://www.mapquest.com/maps/map.adp?searchtype=address&formtype=search&countryid=US&addtohistory=&country=US&address=" & strFullAddress & "&historyid=&submit=Get Map"
    
    'Remove trailing spaces
    strMapquest = Trim(strMapquest)
 
    'Change imbedded spaces with plus signs
    strMapquest = Replace(strMapquest, " ", "+", 1, , vbTextCompare)
    
    Application.FollowHyperlink strMapquest, , True
     
    strMapquest = vbNullString
  
Exit_Mapper_Click:
    Exit Function
     
Err_Mapper_Click:
    MsgBox Err.Number & "-" & Err.Description
    Resume Exit_Mapper_Click
     
End Function

 

[Davros]

thanks pat, I'll give it a go

 

[missinglinq]

I've copied your code to my code vault, Pat! I'll give you credit if I pass it on.

Bridge Players Still Know All the Tricks

Yeah, but some of them are dummies!

 

[Davros]

this code will not work for my problem pat. I have similar code in one of my access database' and it passes on your criteria, [address, road name, town etc etc] to google maps or livemap and then opens them. the software i'm using is a standalone GIS MapInfo and a lot more complicated. it is not a internet application.

thanks anyway

 

[Pat Hartman]

you need to find out what the software expects as arguments and what it returns.

 

[DanBurn]

I'm also trying to do something like this..I can go the opposite way and import from access into mapInfo then create the points. I was going to start using mapBasic (the language that comes with mapInfo) to automate the process so you would just need a way to open mapInfo from Access.

Any Ideas how to do that? can you just load a script in the shell from VBA?

 

[Davros]

Hi
I don't do much with access these days but I am doing a lot with mapinfo. see the link below to your problem. i'd be interested to see your solution.

http://www.access-programmers.co.uk/forums/showthread.php?t=152225&highlight=call+shell

 

[DanBurn]

Right Guys here’s what i've got so far. (I’m self taught so my methods may be messy)

I didn't use the shell way, I registered the MapInfo Reference OLE automation Object.

You can then use all of the functions contained in there.

This code will open any Data Table from access and load it into MapInfo

[COLOR=black][FONT=Verdana]Private Function OpenMapInfo()[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim objMapInfo As Object[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Set objMapInfo = CreateObject("MapInfo.Application")[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]objMapInfo.Visible = True[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'Change all the paths to reflect your applications[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'If your table is already a .Tab and you don't need to export from access you do not need the Register Table Line[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]objMapInfo.Do "Register Table ""c:\YourDataBasePath.mdb"" Type ACCESS Table ""YourTableName"" Into ""YourTablePath.TAB"""[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]objMapInfo.Do "Open Table ""YourTablePath.TAB"" Interactive "[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Function[/FONT][/COLOR]

You need all the Quote Marks as the code has to be within a text String. Also when you look at the loaded MapInfo App it will look empty but if you create a new browser you will see your table is there. or Add

objMapInfo.Do "Browse * From YourTableName"

to the code above.

The MapInfo is in the background (not figured how to setfocus it yet) and I haven’t figured out how to populate the points automatically yet. But hopefully this will give us all a starting point.

From the MapInfo Options if you turn on the map basic window whenever you perform a task it writes the code in the window. So you can cut the code out (similar to Excel Macro Recorder)and then use that in Access. You just use the Do command as sampled above and then drop the code in as a text string.

You should also be able to open the MapInfo on a form within Access but I also havn't figured how to do that.

 

[DanBurn]

I have Now been Able to export Ms Access Table Create The Points From the Eastings and Northings and add a background Raster Map from Access.

I'll Post the Code and I have added some Info/Breakdown Points that should help you all get started at Automated Mapping!

Private Function OpenMapInfo()
'Change tblDumpTest to your Own Tables and Change all Paths to your Own
'Open MapInfo
Dim objMapInfo As Object
Set objMapInfo = CreateObject("MapInfo.Application")
objMapInfo.Visible = True
'Extract Data From Access Table
objMapInfo.Do "Register Table ""N:\Highways\AIU\General Data\Safety Camera Tables\DATABASES\Enforcement devlopment\EditEnforcement.mdb"" Type ACCESS Table ""tblDumpTest"" Into ""N:\Highways\AIU\General Data\Safety Camera Tables\DATABASES\Enforcement devlopment\tblDumpTest.TAB"""
'Opens the Table
objMapInfo.Do "Open Table ""N:\Highways\AIU\General Data\Safety Camera Tables\DATABASES\Enforcement devlopment\tblDumpTest.TAB"" Interactive"
'This Sets the Points To the British National Ordinance Survey Coordinate System(accurate to 1mm) You will have to find your own country for this.If this line isnt here it uses global Long Lat.
objMapInfo.Do "set CoordSys Earth Projection 8, 79, ""m"", -2, 49, 0.9996012717, 400000, -100000"
'Creates Points From Two Fields In the Table (in My case they are called Easting and Northing Change Them to suit your table)
objMapInfo.Do "update tblDumpTest Set obj = CreatePoint(Easting, Northing)"
'Creates Map from Table
objMapInfo.Do "Map From tblDumpTest"
'Opens an Extra Backdrop Layer. Change the Path to your own map locations (Raster/Vector). I use MasterMap
'*Note I Open 4 layers which is why there is 8 Lines of code
objMapInfo.Do "Open Table ""M:\Vectors\MasterMap\ncc\colour\CombinedLayers\MMC_ALLFILL.tab"" Interactive"
objMapInfo.Do "Add Map Auto Layer MMC_ALLFILL"
objMapInfo.Do "Open Table ""M:\Vectors\MasterMap\ncc\colour\CombinedLayers\MMC_ALLLINES.tab"" Interactive"
objMapInfo.Do "Add Map Auto Layer MMC_ALLLINES"
objMapInfo.Do "Open Table ""M:\Vectors\MasterMap\ncc\colour\CombinedLayers\MMC_ALLPOINTS.tab"" Interactive"
objMapInfo.Do "Add Map Auto Layer MMC_ALLPOINTS"
objMapInfo.Do "Open Table ""M:\Vectors\MasterMap\ncc\colour\CombinedLayers\MMC_ALLTEXT.tab"" Interactive"
objMapInfo.Do "Add Map Auto Layer MMC_ALLTEXT"
 
End Function

Cheers!

 

[Davros]

outstanding
I'll give that code a try soon!

thanks

 

[Davros]

one quick question
what event button in access are you putting this code behind?

thanks

 

[DanBurn]

Nothing as of yet its just a function in a module so far, so im just running the code from the compiler.

 

[annabelouise]

Hi
I have used the code discussed above to open a table (.TAB) file via Access with success, however, now I need to open a workspace, but keep getting error message 'Found [workspace] while searching for [file]' when runnig the following code:

Private Sub cmdOpenMapInfo_Click()
Dim objMapInfo As Object
Set objMapInfo = CreateObject("MapInfo.Application")
objMapInfo.Visible = True
objMapInfo.Do "Open Workspace ""C:\File Path\Basic.WOR"" Interactive "
End Sub

 

[DanBurn]

Hi,

I'm not sure you can open a workspace from mapbasic, (although you might it's been a long time since I used it) but as a .WOR File is just a collection of Map basic commands you could just call them all from your VBA routine. If you open your .WOR in notepad you can see the commands and can call them using:

Mapinfo.Do

e.g.

objMapInfo.Do "Set Map CoordSys Earth Projection 8, 79, ""m"", -2, 49, 0.9996012717, 400000, -100000 Center (461036.2819,343997.9344) Zoom 8.972952427 Units ""km"" Preserve Zoom Display Zoom Distance Units ""km"" Area Units ""sq km"" XY Units ""degree"""
objMapInfo.Do "Open Table ""M:\Rasters\10KmonoTif\10k_G.TAB"" Interactive"
objMapInfo.Do "Add Map Layer _10k_G"
objMapInfo.Do "create frame (0.3174,0.2139) (16.2,21.8174) Pen (1,2,0) Brush (2,16777215,16777215) From Window WindowID(4) FillFrame On"
objMapInfo.Do "create frame (12.6708,21.8174) (16.2007,23.2153) Pen (1,2,0) Brush (2,16777215,16777215) Title ""OSCrownCopyrightFull Map"" FillFrame On"
objMapInfo.Do "Create Frame (7.9278,21.8174) (12.6813,23.2153) Pen (1,2,0) Brush (2,16777215,16777215) Title ""Key"" FillFrame Off"
objMapInfo.Do "create Text ""Site " & Site & " By Severity"" (1.8313,21.834) (1.9014,22.134) Font (""Arial"",1,36,0)"
objMapInfo.Do "Create Frame (15.1528,0.2146) (16.2,1.3604) Pen (1,2,0) Brush (2,16777215,16777215) Title ""NORTHN5 Map"" FillFrame On"
objMapInfo.Do "Create Frame (0.3146,21.8174) (1.8194,23.2153) Pen (1,2,0) Brush (2,16777215,16777215) Title ""fulllogoa5_pantone361_corporat Map"" FillFrame On"
objMapInfo.Do " Create Line (1.8194,23.2153) (7.9278,23.2153) Pen (1,2,0)"
objMapInfo.Do "Set Window FrontWindow() Printer Name ""\\ap02-0030\q-0030-plotpool"" Orientation Portrait Copies 1 PaperSize 620"

Hope This Helps?

 

[annabelouise]

Thanks! Solved my problem.