jps services
  • Home Page
  • GIS Training Courses
    • QGIS Training Courses
    • ArcGIS Training Courses
    • MapInfo Pro Training Courses >
      • MapBasic Training Course
  • About
  • Blog

Creating joins to Microsoft Access database tables with ArcGIS Pro

24/11/2018

0 Comments

 
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.
Picture

Read More
0 Comments

Connecting Microsoft Access databases to ArcGIS Pro

20/11/2018

0 Comments

 
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.
Picture
Accept the default settings and you will be connected to the server as shown in the next screen shot.
Picture
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. 
Picture
The New Database dialogue box appears as in the next screen shot. Give your database a name and click OK. In this case I have called the database Observatory.
Picture
We now have an empty database called Observatory within SSMS as shown in the next screen shot.
Picture
Having confirmed that you have created a new database the next stage is to import the Access tables to the Observatory database. Next select SQL Server Import and Export from your SQL server 17 software options. The dialogue box for the wizard appears as in the next screen shot.
Picture
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.
Picture
Select Destination, Server Name and the Observatory database as shown in the next screen shot.

Picture
Click on the Next button and accept the default as in the next screen shot.
Picture
Next select the table or tables you wish to import as shown in the next screen shot.
Picture
Click on Next to show the next dialogue box as shown in the next screen shot.
Picture
The final dialogue box confirms the actions requested and imports the data into the SQL Server database as shown on the next screen shot.
Picture
Here is a screen shot of the Observatory tables within SQL Server Management Studio.
Picture
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.

Read More
0 Comments

    Author

    Joe Short BSc has been involved with various mapping solutions for over twenty years.  If you are considering implementing a GIS  or have ArcGIS Pro, MapInfo Pro or QGIS training requirements, jps services would be happy to be of assistance to your organisation. 

    Archives

    April 2020
    March 2020
    October 2019
    September 2019
    August 2019
    July 2019
    March 2019
    November 2018
    October 2018
    August 2018
    July 2018
    November 2017
    October 2017
    September 2017
    July 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    May 2016
    February 2016
    September 2015
    August 2015
    April 2015
    February 2015
    November 2014
    October 2014
    July 2014
    June 2014
    May 2014
    March 2014
    February 2014
    December 2013
    November 2013
    October 2013
    September 2013
    August 2013
    June 2013
    May 2013
    April 2013
    February 2013
    December 2012
    October 2012
    September 2012
    August 2012
    July 2012

    Categories

    All
    Arc Gis
    Arcview
    Autodesk
    Cad
    Gis Training
    Local Government
    Mapbasic
    Mapinfo
    Quantum Gis
    Relational Databases
    Saga Gis

    RSS Feed