Moving database from ArcGIS Online Feature Layer to Microsoft Access

Question

I have a project that involves collecting data in Survey123 (hosted as a ArcGIS Online Feature Layer) and conducting QA/QC in Microsoft Access. We're trying to come up with the best process for moving the data between these programs.

Some constraints that are specific to our database:

1. There are multiple fields with entries greater than 254 characters
2. Field names are mostly more than 10 characters
3. The spatial attributes are not saved in the AGOL attribute table
4. There are many tables and many fields per table, necessitating an automated approach

Our current method involves moving the data from AGOL to ArcMap via FGDB, then using an ArcMap model to do the following:

1. Create new fields with text copies of globalid field to preserve original values (the actual globalid fields are overwritten anytime data is reprojected or moved between tables).
2. Re-projects the tables sequentially in UTM zone 10, UTM zone 11, and WGS84 (geographic), adding the coordinates to the attribute table at each step.
3. Chooses the final coordinates in UTM based on the boundary between UTM zones in WGS84 DD (-120).
4. Exports all tables to a personal geodatabase (.mdb) file so they can be opened in Access. In Access they are moved to a .accdb file and then inserted via query into a different "master" table structure that holds many years worth of data.

This workflow is fine for right now, but in the not-too-distant future my employer is going to require ArcGIS Pro only (no ArcMap) and Pro does not have the ability to write to .mdb files.

Exporting as a .csv or Excel file is also an option, but importing to Access requires setting every field type and length manually, which is time consuming and error-prone. The field name lengths and long text fields don't work with .dbf exports.

Any suggestions on how to automate this in a way that is compatible with ArcGIS Pro?

Answer 1

Well its not entirely true that ArcGIS Pro won't work with MS Access databases. It won't natively read/write to them which is a big head ache and I do hope someone from Esri resolves it. It seems that's a dream that won't come true any time soon. MS Access is a great piece of software and very useful for quick data analysis.

If you have the Interoperability Extension installed you can export from a file geodatabase directly into a new\existing accdb database directly cutting out any intermediate conversion steps.

You use the quick export tool and set the format to be Microsoft Access (JDBC) as shown below.

This for example will convert my test polyline dataset:

Into this...