A few days ago I had asked for advice on using MapInfo and Microsoft Access together in the same project. I wanted to know if there were any issues in editing records in Access and then viewing them with MapInfo and vice versa. If you read this (long) summary to the end, you'll find a link to a demo of an interesting solution to this problem.
In general, linking Access tables to MapInfo seems to work well. Steve Nabors reports that he has had no trouble making edits in Access and having them reflected immediately in MapInfo. Alan Gunn does just the opposite; he edits in MapInfo and produces reports in Access. However, he says that the Access database has to be saved before changes show on reports, and the reports must be closed and re-opened first. He also adds that the reports must be created in a separate Access database and linked to the tables that MI updates. Steve King uses Access and MapInfo concurrently, adding map records in MapInfo to a mappable Access table and then filling them in further with Access forms. He says that if you use the refresh option in Access, there are no synchronization problems. However, he does warn that if you mix deleting records in Access and MapInfo in a linked table and then pack the table, you'll have problems. Packing the Access data leaves deleted records unchanged in MapInfo, and if you then pack the MapInfo table, the two linked data sets become misaligned. Another problem occurs if you edit the structure of the Access table. If you do that, you'll need to re-open the table in MapInfo from scratch. Robert Crossley suggested using ODBC as a method to avoid table-packing problems. He also suggested that Access data be kept in its own database, separate from a reports database as a way to improve security and easily scale up the operation should the data need to be implemented in a more robust DBMS (like SQL Server or PostgreSQL.) Not a bad idea. He also mentions that there is an issue with editing Access tables that have indices where the tables are stored in one Access database. He says, > "MI crashes when you edit one and then select from another from MapBasic (new versions of MapInfo will rebuilt the indices automatically when this occurs when using MI directly, but it still does it when the events are driven from MapBasic). Basically you have to close all your access-based tables from that database after saving and re-open them." Peter Horsbøll Møller entered the discussion with the philosophical point that using a mappable Access-MapInfo table hybrid is "doomed to failure." This echoes Steve King's observation about records becoming mismatched when either the Access or the MapInfo side of the records is deleted. His solution is to keep attribute data in Access separate from graphic data in MapInfo, linking them with a common key as the need arises. Finally, Rob Davis had a novel approach which addresses many of the previous concerns and is one I found quite interesting. He keeps all the attribute information in Access and the mappable information in MapInfo, using a common unique key attribute and DDE to link them as needed. He can then use Access forms to edit the attribute data from within MapInfo. By simply passing the id(s) of the map objects whose attribute data you want to edit to Access via DDE, you can make Access dialogs pop up over your map just as if they were MapBasic dialogs -- except these forms can sport controls, colors, fonts and images that MapBasic programmers can only dream of! Another benefit is that you don't have issues maintaining MapInfo links into your Access tables (and all those extra *.tab, *.aid, *.ind etc. files.) You can also use DDE to call MapInfo from an Access dialog to bring up the map objects of the records you're editing. This idea was pioneered by Ludovic Gnemmi a few years ago, and he wrote a demo application (in French) which is available for download from Jacques Paris' web site (see Projet Access<>MI at www.paris-pc-gis.com/index.html.) But for those who don't read French, I've built a similar (and somewhat simpler) demo in english, which you can download from my "Free Tools" web page at http://www.gisnet.com/catalog/software/tools/index.php. I think this approach may have a great deal of potential. It keeps the MapInfo and Access tables physically separate while still logically connected. It allows you to use the much better user interface of Access and its SQL language to present data to the user. Many times in the past I've wished for the ability to use a data-bound grid control or to alter the fonts in a dialog, or even include a graphic on the dialog in my MapInfo application, and now it's possible. - Bill Thoen --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 16595