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

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.
Picture
This brings up the Database Connection dialogue box. Choose the Observatory database from the drop down list of the Database section as in the next screen shot.
Picture
Click on the OK button. Next within the Catalog pane right click on the server connection as shown in the next screen shot and add it to the project.   
Picture
In the Catalog pane click on the Project tab and open the tables available in the server connection as shown in the next screen shot.
Picture
Next right click on the Observatory.dbo.Mag_GPS table and add it to the map view. Then open the Geoprocessing pane. Search for and select the XY Table to Point option. Then select the Observatory table and the other default settings as shown in the next screen shot.
Picture
Click on Run to create the new table. This will add a points table to your map as shown in the next screen shot.
Picture
Now with the newly created points table you can modify the map to meet your requirements such as changing point size, colour and labelling.​ Here is a screen shot showing the map with labelling and the attribute table view.
Picture
​You can also import Access tables in order to join them to tables that already contain geometry. You can obviously also move data from your Access tables into tables within your projects where this is appropriate.
​As mentioned earlier the lack of connection to Access tables with ArcGIS Pro is a problem but there are a number of ways to solve this. You can work with Access within ArcMap and keep ArcGIS Pro for other projects. Alternatively you could experiment with transferring your Access tables into a relational database format supported by ArcGIS Pro.
0 Comments

Your comment will be posted after it is approved.


Leave a Reply.

    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