1

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?

5
  • It is not recommend to edit personal geodatabases (.mdb) outside ArcMap. ESRI state this here support.esri.com/en/technical-article/000008858 (backup your data first if you do).
    – Mapperz
    Sep 29, 2022 at 2:09
  • 1
    Push back on your employer, ask them what they're going to do to fix this, it's their mandate so it's their problem to fix! (sorry, I'm just a little jaded about dumping and rewriting ~15 years worth of ArcObjects code when Desktop goes EOL). Excel and CSV are super annoying to work with due to lazy (nonexistent) constraints on data cells so I wouldn't call them an option. Oracle spatial might be a good workaround or Microsoft SQL Server, I'm sure that would export to Microsoft Jet (Access) format. Sep 29, 2022 at 4:27
  • Agree about the pushback. Unless I missed it, you don't provide reasoning why you go to Access for QA. What does Access provide you can't do else where?
    – KHibma
    Sep 29, 2022 at 12:06
  • 1
    We don't edit the .mdb export file in Access, the tables are moved into a separate .accdb database and then the data are inserted into the master tables which have different data structures. This process allows combining datasets collected in Survey123 with older data collected using PDAs. We use Access because there are SQL queries and VBA scripts that have been built over the years that help with QAQC. I'm not sure exactly when the full transition to ArcGIS Pro will happen, but when it does it will be beyond my control (government agency). I've been advised to future proof workflows
    – WindRiver
    Sep 29, 2022 at 15:17
  • I updated the original post with the information in my comment above.
    – WindRiver
    Sep 29, 2022 at 15:26

1 Answer 1

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.

Quick Export tool

This for example will convert my test polyline dataset:

Data in

Into this...

Output data

1
  • Thanks, it looks like this would work. Unfortunately I don't have access to that extension so I can't test it right now, but maybe I can use this as reasoning to get that licensed
    – WindRiver
    Nov 10, 2022 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.