Joining an Access table to a table in a personal geodatabase within ArcGIS software is currently only feasible with ArcMap. However, as discussed in a previous blog, you could import your Access database tables into a format which is acceptable to ArcGIS Pro. In this case creating a join to Access tables in ArcGIS Pro would be possible.

Prior to discussing how to join Access tables in ArcGIS Pro it is relevant to quickly review the history of personal geodatabases and a possible reason for ArcGIS Pro currently not supporting Access tables.

Personal geodatabases were introduced with version 8 of ArcMap. Personal geodatabases use the Microsoft Access .mdb format and are only available on the Windows operating system. A personal geodatabase has on average an effective size of up to 500 mb. Whilst the maximum size of Access tables is theoretically 2GB in reality because of performance issues 500 mb is recommended as the optimum size. Additionally, Esri has also now developed the file geodatabase which gives better scalability, performance as well as cross platform use.  

Since ESRI introduced ArcGIS Pro, Access database connection availability has been limited to ArcMap. Some users may choose to use ArcMap for Access connection and ArcGIS Pro for other work. ArcGIS Pro has many attractive features, which take advantage of the new Windows 10 ribbon interface, which are superior to the menu based system used by ArcMap.

ArcGIS Pro may not support Access database connection because of problems experienced with connecting Access databases to ArcMap. This could be because personal geodatabases and older Access tables share the same .mdb format. As the personal geodatabases use .mdb files they could be accidentally corrupted by users modifying these tables within the Access software.

As mentioned in the previous blog you can easily import Access databases into SQL Server and then access these tables in ArcGIS Pro.

Here is a screen shot of the Observatory database, which has been imported from Access, into SQL Server Management Studio. Here we can see the table which we can use within an ArcGIS project.

If you are using ArcMap and regularly need connection to Access databases you should be aware that if you decide to upgrade to ArcGIS Pro there is currently no Access database connection facility.

There have been some suggestions that since ESRI introduced a ribbon based interface, issues with Windows 10 are the reason for not having an Access connection. However, MapInfo Pro is now also using the same new ribbon based interface with Windows 10 and has no problems with connecting to Access databases.
​
Microsoft Access 2007 introduced the new  .accdb file extensions which replaced the .mdb file extension.  Currently ArcGIS Pro does not connect to the .accdb format.  However,  ArcMap can connect to this newer Access format.

You can work with Access tables of an .mdb format in ArcMap via an OLE DB connection. OLE DB is the standard for sharing data between applications. This enables connection to Access databases in ArcMap. However to ensure data is not corrupted changes should only be made within Access and not via ArcMap.  

For the same reason, modifying a personal geodatabase should only take place within ArcMap. Modifying these files, which are in an Access .mdb format, within Access could lead to the geodatabase file being corrupted.

Access is not recommended for editing personal geodatabases as it was not created for use with the ArcMap geodatabase format. In addition, unlike many other relational databases Access does not support geometry. The ArcMap geodatabase has to keep track of the table changes whenever a user update occurs. For this reason modifying these tables in Access could corrupt the geodatabase.

As there have been problems with Access connections to ArcMap geodatabase files, there is some logicality in ESRI currently not providing an Access database connection in ArcGIS Pro.

This obviously creates a dilemma for current ArcMap users who want to upgrade to ArcGIS Pro but also need to continue to connect to Access databases. One solution would be to use ArcMap for Access connection and use ArcGIS Pro where Access is not required. Running two different versions of gis software could lead to compatibility issues so this is not an ideal solution. The other problem is that ArcMap will not be supported forever and there will come a point where a decision to switch to ArcGIS Pro will become inevitable to avoid working with unsupported software. Currently, there is no information as to whether ESRI will provide Access database connection in ArcGIS Pro.

An interim solution could be to convert Access databases to another database format that ArcGIS Pro does support. Access is a Microsoft product and therefore the obvious route would be to use a Microsoft conversion tool for moving Access tables to another relational database such as SQL Server 17 Express.  

According to Microsoft the maximum size of an Access database is 2GB whilst the max size of SQL Server 17 Express is 10GB. So any current Access database should be well within the limits of the SQL Server 17 Express database.

SQL Server 2017 Express is a free edition of SQL Server, ideal for development and production for desktop, web, and small server applications. You can download the Express version here.  ​You should also download SQL Server Management Studio (SSMS) which you can use to query, design, and manage your databases, either on your local computer, or in the cloud.

Most importantly SSMS is also free! You can download it here.

Start up SQL Express and then SSMS. A dialogue box for connecting to SQL Express will appear as shown in the following screen shot.

Accept the default settings and you will be connected to the server as shown in the next screen shot.

The next step is to create a database for your Access tables. Right click in the Object Explorer pane on the Database folder. Select and click on the New Database option. 

Click on the Next button and choose the Access database connection option and browse to your Access database location as shown in the next screen shot.

Select Destination, Server Name and the Observatory database as shown in the next screen shot.

Click on the Next button and accept the default as in the next screen shot.

Next select the table or tables you wish to import as shown in the next screen shot.

Click on Next to show the next dialogue box as shown in the next screen shot.

The final dialogue box confirms the actions requested and imports the data into the SQL Server database as shown on the next screen shot.

Here is a screen shot of the Observatory tables within SQL Server Management Studio.

The Access tables are now available within SQL Server Management Studio. Now we can connect the imported Access tables to ArcGIS Pro.

Create a new project in ArcGIS Pro called Observatory and click on the Add New Item icon in the Favorites section in the Insert tab. Next choose the New Database Connection option as shown in the next screen shot.

If you are familiar with the Atlas feature in QGIS you will be pleased with the new Report feature available in QGIS 3, which is the latest version. With this new Report feature, as well as attribute driven pages, you can also add headers, footers and static pages to your pdf report. In this way your report will become more readable and meaningful to those you send it to.   

For instance, with the Atlas function you can iterate through a series of polygons, lines or points to create a map series based upon an attribute field of a specified layer. However, with the Report function, in addition to headers and footers, you can also have nested map series as well as static pages in your final report. Because you can generate a much more complex report with many sub sections, the benefits of headers and footers and their inclusion within the report, to make it meaningful, becomes more apparent.

When you include a nested map series your report will iterate through the attributes and group them based on a linked attribute in the top group layer. If your nested layer does not have an attribute field that can be linked you can create a link with the “Join by location” algorithm from the Tool Box.  

For example, if you wanted to link a towns table to a regions table you would use the algorithm to group the towns which are within in regional polygon to create a new table which can then be linked to the top group layer.

If you start a new project in QGIS 3 you can create a new report quite easily. Below I have created a new project with three layers. A French town’s point layer, a polygon regions layer and a graduated polygon regions layer.

To create a report click on Project on the top menu and select New Report as in the following graphic.

Next complete the Create report Title dialogue box and click OK.

This brings up the Reports dialogue box.

Here you can create both a header and footer for your report. When both header and footer are enabled you can click on the Edit buttons to create the relevant pages for your report. Below is a sample header page containing two text boxes.   

If you want to have a static layout page click on the green plus button. Choose the Static layout section option as in the following screen shot.

When you click on the Edit button a blank static layout page is created as shown below.

You can include text, images or legends in this section as shown in the following screen shot.

By clicking at the top level on the Report heading and the creating a Field group section we can then edit the Body section. We can create maps related to regional population by including attribute driven text fields together with a map zoomed in to region level and a legend. An example is shown in the next screen shot.

We can now add a nested section by leaving the Group section highlighted rather than going back to the top Report level. This nested section has a header page which is shown below.

Having created a header page related to each set of regional towns we can now create individual maps of each town within a region. You can see from the following screen shot that the map is using an OpenStreet map layer. As this report has both graduated symbol layers and OpenStreet layers ensure that Lock Layers and Lock Styles are enabled within the Items Properties section for each Field group section as shown in the next screen shot.

Also note that within each Field group section the maps are controlled by the report so you need to ensure that this option is also enabled.   

By clicking on the Report option on the top menu you can create a pdf for your report as shown in the next screen shot.

Depending on how many pages are included in your report and whether OpenStreet data is included will determine how long the report takes to be created. Below are some screen shots from the pdf generated by the above report.

Here is the static page screen shot.

Next the report will generate a map for each region of the French map as shown in the next screen shot.

Next the nested header page is printed as in the next screen shot.

Next the report will produce maps of the individual towns within the Alsace region as shown in the next screen shot.  

Note that the nested Prefecture sections show an OpenStreet layer whilst the Regional sections show a graduated symbol layer.  

This final screen shot shows the final page of the report.

As the above example shows the new Report feature has potential to produce both simple and complex reports which can contain a range of map formats. Because of its flexibility it represents a considerable improvement over the previous Atlas program.

​We could enhance this map by adding a mask as in the next example.

Some improvements to the program would include including an ability to recognise when the user closes one set of maps and opens new ones. This would also include closing any elements in memory which could impact adversely on the next set of maps. Also the cartographic element is very rudimentary.

The program currently is hard coded and only works with base maps. If a style override is applied this is not included in the maps generated by the program. The ability to be able to read this kind of data regarding layer information and apply it to the map series would be very useful.

You can obtain the Atlas program and the related MapInfo Pro tab files from the Pitney Bowes community download page with this link
​

​CartoLegend then calls ThematicName.

Then a SQL query is run to select the region to be printed. ​

In this final blog about automatically creating  a series of maps in an atlas type program,  we will look at adding some text options for the user.

The first option, regarding adding text to a series of maps, is to add a text box so the user can put text on to one or more maps.  This could be achieved by adding an additional ribbon tab for adding text to the map.  In previous versions of the Atlas program updating the organisation details was an option in which a dialogue box automatically appeared when a new selection was created.  Whilst this option enabled updates to occur to organisational data it may be overkill if changes rarely occur.  By adding a tab, where this option could be accessed as an when necessary,  would therefore be an acceptable alternative.  This is option is shown in the graphics below.  

​When the Data option is selected the dialogue box appears with an additional option of a text box for inputting additional information in the map.

This text box could be used to input text relevant to all the maps in a series.  However, this option would not be suitable for customised statements which would be relevant to individual maps.  For this option you will need to add MapBasic code into the Atlas program.  This option would be appropriate if you have data in your tables which you would like to display in your map series.  We can achieve this by updating the code in the LayoutPortrait and LayoutLandscape modules.  
​
Here is the result of some standard text included within a section from the map of French departments.  This French departmental map was used in the original version of the program called PrintLayout developed by Egge-Jan Pollé.

​We could also add some text into a map series which contains variable data by creating code with MapBasic.  Here is an example using data from the map's browser.

​Here is a close up of the variable text.

​Here is the browser data from which this information is derived.

So far in previous blogs we have used MapBasic to create an Atlas program which can iterate through the regions of a specified layer in order to create individual customised maps. The Atlas program could be further enhanced if the user could have cartographic and thematic legends automatically created as required. Additionally the thematic layer should also be made available for the user to include in the printouts.

In the following screen shot a workspace has just been loaded into MapInfo Pro. As you can see the Legend Designer window is in focus. (As seen by the blue band at the top of its window).   

The Atlas program requires the map window to be in focus in order to create a cartographic or thematic legend for each selected region. In the next screen shot a Note message informs the user of the need to change the focus if they require a cartographic or thematic legend. If they ignore this message a map or maps can still be created but without a legend being included in the output.
Here is a screen shot of the Note message.

Assuming that the focus is on the map window, output can be obtained like the following screen shots which are based on the Canadian map provided in the MapInfo Professional trial data. Note that in this map the capital and highways data is not particularly informative.  It is recommended that you save the layers with more meaningful names as is shown in further examples.

Here we have saved the capital and highway layers with more meaningful names:-

The cartographic legend can also be incorporated into maps which have a mask element as in the following screen shots:-

Having created cartographic legends automatically from a workspace the next stage is to also be able to automatically create output from thematic maps with an appropriate legend.

Here is another workspace of the Canadian regions which has a thematic theme and legend.

The Atlas program has MapBasic code to check whether a thematic layer is present. In that case the program creates maps which reflect the theme and also includes a thematic legend as in the following screen shots.

Here is another example:-

As with the cartographic examples, maps with a thematic element can also can be enhanced with a mask. Here are some examples:-

And here is another example:-

The Atlas program needs several new modules in order to be able to create maps which have thematic layers or cartographic legends.

Here is the project code showing the respective modules.

Project file: Atlas.mbp

In all we are adding four new modules, ShadeInfo1, SearchReplace, ThematicName and CartoLegend.

​Additionally, Atlas.def has also been updated.

''************************************************************************************
'**  Project Atlas
'**  
'**  Definition file
'**  
'**  Author: Joseph Short
'**  Date: 14 September 2017
'**  Version: AppVersion (see below)
'************************************************************************************
Include "MAPBASIC.DEF"
Include "ICONS.DEF"
Include "MENU.DEF"

Declare Sub Main
Declare Sub End_Program
Declare Sub Endhandler
Declare Sub InputAtlasDefaultSettings
Declare Sub MLB
Declare Sub LayoutPortrait
Declare Sub LayoutLandscape
Declare Sub MLBOKButton
Declare Sub SelectAllFromMLB
Declare Sub HighLightSelectedRegions
Declare Sub PopulateRegionsArray
Declare Sub DlgHandler
Declare Sub Atlas_dialog
Declare Sub AddMaps
Declare Sub DialogHandler
Declare Sub AtlasAbout
Declare Sub RibbonMaskSettings
Declare Sub CreateMaskMaps
Declare Sub CreateRibbonMask
Declare Sub ShadeInfo1
Declare Sub ThematicName
Declare Sub LegendInfo
Declare Sub CartoLegend
Declare Sub WinFocusChangedHandler 
Declare Function LongDate(ByVal dDate As Date) As String
Declare Function SearchReplace(ByVal strInput as String, ByVal strReplace as String, ByVal strReplacement as String) as String

'Function to check whether a table is open or not
Declare Function TableIsOpen (ByVal sTabName As String) As Logical

' /////     Class "MBExtensions.MBDateAndTime"     /////
' Allows you to retreive information from a MapBasic Date (i.e. YYYYMMDD) or
' DateTime (i.e. YYYYMMDDHHMMSSFFF) string
 
' Convert the string to it's equivalent long date string representation
' (depends on Control Panel > Regional and Language Options)
Declare Method RegionalLongDate
    Class "MBExtensions.MBDateAndTime"
    Lib "MBExtensions.dll" (ByVal sDateString as string) As String

Define AppName "Ribbon Based Atlas Sample Code"
Define AppVersion "0.70"
Define MLBID 1001
Define SelectAllMLBID 1002 

'Defining Dialog Width and Height
'This allows you to specify with and height clauses in terms of characters (i.e., Width 30dW, Height 10dH).
Define dW *4 'Four dialog units equals one character in width
Define dH *8 'Eight dialog units equals one character in height

Global tTime as Time
Global dDate as Date
Global sTabFile, sTable, sColA, sColB, sRegionsArray(), sAddressArray(5), sPrintDate, sCopyrightText, sFont, sRegion, sRegionTitle, sPrinterName, sDocTitle, sPapersize, sOutputFolder, sThematicLayer, sShadeText, sTotalString,sFirstLine, sFirstPart, sSecondPart,sFirstPartThematic, sSecondPartThematic, s_title, sColumn,sRec1, sLegendText As String 
Global iRegionsSelectedArray(), iPaperChoice, iPapersize, iLogoWindowID, iMapWindowID, iInitialMapWindowID,iLayoutWindowID, iLayoutFontSize1, iLayoutFontSize2, iLayoutFontSize3, iLayoutFontSize4, iOrientation as Integer
Global fZoom, fMp, fPaper1, fPaper2, fPaperWidth, fPaperHeight, fHorizontalDistance, fVerticalDistance, fPos1, fPos2 As Float
Global lRegionObjectsSelected As Logical
Global l_create_mask, l_shadeInfo, is_thematic, is_legend, is_cartographic As Logical 'TRUE means include mask 
Global sMaskTable, sMaskTabFilePath As String
Global sPctTranslucencyArray(5) As String
Global fX1, fY1, fX2, fY2 As Float
Global oRectangle, oCutter, oMask, ObjRegion As Object
Global iPctTranslucency As Integer
Global pPenStyle As Pen
Global alAlias,alTable As Alias

In the last blog we developed an atlas program to create A4 and A3 printable maps from tables and workspaces. We could further enhance the program by adding a mask facility. The code for the mask was originally developed by Egge-Jan Pollé.

Here is a screen shot of the updated ribbon based atlas program including the mask option.

If we now use the Canadian maps provided in the MapInfo Pro trial data folder we can see how the mask works. Here is a screen shot after the Mask Settings OK button is pressed.

The Create Mask Settings dialogue box allows the user to select a Border style and Translucency level if the Create Mask Settings option is enabled. If the OK button is pressed with the default settings a map will be printed with the following border style.

The translucency and border options are shown below:-

Having clicked the OK button these settings will be reflected in the maps selected. If we choose the Atlas Example button a dialogue box appears. Her you can choose a table and a column for your map/s as shown in the next screenshot:-

Clicking OK brings up the Default Settings dialogue box. Here you can make changes as required. Here is a screen shot of the dialogue box.

Clicking on OK will bring up the Ribbon Based Atlas Sample Code dialogue box as shown in this screen shot.

Having chosen one or more regions clicking on the Print button will produce output like the following screen shot.

We could change the translucency level or the borders by clicking on the Mask button. The screen shot shows the proposed changes.

Here is a screen shot of a printed map after making these changes to the border type.

If you require a map without a mask you can do this by clicking on the Mask Settings button and deselecting the mask option. Here is a map created after deselecting the mask option:_

In the previous blog we looked at updating the PrintLayout program to a ribbon based interface in MapInfo Pro. As mentioned in that blog the PrintLayout program is an excellent template for creating an atlas program for MapInfo Pro.

We need to make some changes to the code so that a number of extra features can be made available to potential users. For instance, we need to let the user have some means of updating the default settings for the company data. Also the user should be able to use different tables and workspaces with the atlas program.

Here is a screen shot of the atlas code showing the ribbon interface set up in MapInfo Pro.

The atlas program should be able to ascertain whether there are tables open when the Atlas Example button is pressed. As shown in the next screen shot.

If the user opens one or more tables the program will then open a dialogue box to let the user choose a layer and an associated column as shown in the following screen.

Here three tables have been opened using the USA data from the MapInfo trial data. The user has selected the States table and the corresponding State_Name column data, as shown in the next screen shot.

Once the user clicks OK the Default Settings dialogue box appears. This contains the basic organisation information for input into the completed maps. Here is a screen shot of the dialogue box. Note that each element can be updated for this session.

When the user clicks OK the MLB dialogue box appears which contains all the relevant States in the USA States map. Here the user can choose one or more States to create individual maps of selected States. Clicking on Georgia and clicking on the Print button, as shown in the next screen shot, starts the map creation process.

Clicking on the Print button will create the following map into the Output folder.

When the user needs to exit the program clicking on the Info button produces the following dialogue box.

So by adding some additional modules to the original program we have been able to create an atlas program which can be used with both individual tables and workspaces. Additionally the maps can be printed with an organisations’ details.

As mentioned in previous blogs, in order to create this atlas ribbon based program within MapInfo Pro, we first need to create a definition file, relevant modules and a project file.

Here is the code for the definition file:

The PrintLayout program provided by Egge-Jan Pollé on his website is a code sample which iterates through polygons in a map in order to create individual maps from a selection of departmental polygons. This code has the potential to be updated into an atlas like program for general use within MapInfo Pro.

The first thing to note about this program is that all the information is hard coded. The program currently only works with a specific map of France and related data such as the company information which is already in written into the code. If you want to use a different map and update the company data then the program has to be re-compiled for the updates to take effect. So as mentioned before, this program is an excellent template for creating an atlas program but will need more than a ribbon interface for it to be viable for general use.