1

I am working on a project for the National Park Service. The goal of the project is to connect a Microsoft Access Database to a File Geodatabase, where any information put into the .accdb tables will automatically be put into the FGDB tables. The reason for this is that it will enable other employees in our park without GIS experience to easily put in data from cave surveys into the simplified UI we made for the accdb. We want that data to then be transferred to the FGDB as seamlessly as possible; the end goal is to have something that gets it done with one process and no other hassles involved.

So far I have connected the access database to ArcCatalog via an OLE Database link, which works without issue. However, I am having trouble figuring out how to set up a way in which data from the accdb will be automatically sent to the FGDB, without having duplicates that we would have to manually delete. The only way I could think of was to set up a model like in image I included below; it only includes the model for just one of the table datasets needed. It almost works, but it keeps deleting the wrong set of duplicates. It deletes the new set (the most current data), not the old set. Essentially, I am just trying to have the model overwrite the older cave survey data and replace it with the newer data. If I could find a way to have the model delete the older duplicate set, it would do exactly what I want it to.

Toolbox graphic

Can anyone give me input on my method or recommend another?

I have limited python experience.

2

Well, just as a conceptual framework, what I picture helping you solve this challenge is roughly as follows.

First, in the MS Access Database you would need to use Macros, calculated fields, or some other method to ensure there is an automatically populated "last updated" type field for relevant tables, so any time a new record is inserted or an existing record is updated, it puts the time of that change in that field.

Next, when setting up the schemas for the feature classes / tables in the File Geodatabase, you'd need to make sure you have a field to save the Key field from the associated MS Access tables (a unique identifier field, by default in MS Access this is typically named "ID" and is an auto number field). EX: in other words, in addition to the default ObjectID field created in the FeatureClass, you'd add probably a long integer field maybe called MSAccessID or such, and as records are brought over from Access, you'd want to make sure that ID field gets carried over.

Next, you could either have a *.txt file somewhere, or add another table to the FileGDB to save info to, etc... but you would need to have somewhere to store the "last synchronized time".

Then, you could take and set up a python script (or set it up as a model builder model, which you could always call from a python script if necessary). It would need to:

  • Get and save a the current date/time (script start)
  • Open the table, txt file, ... and get the date/time the access database was last synchronized with the FGDB (last synchronized)
  • Select all records from the access database that have a last updated date/time between last synchronized and script start date/time
  • Select all records from the feature class/FGDB table, that have a MSAccessID (or equivalent) field, with a value matching the values of currently selected Access Records
  • Delete those FGDB records that got selected
  • Append the selected MS Access Records into the feature class
  • Save the script start date/time to the table or text file where you are storing the last synchronized date/time

You could then either run that manually, as needed, or even schedule the script in task manager to automatically synchronize them every so often (if it's a python script or a model being called by a python script).

PS: by saving the "last synchronized" date/time instead of just saying I'm going to schedule this to run every 10 min, so just use features selected in the last 10 min, if the server/computer has to be re-started and misses an execution, if a synchronize fails for some reason, etc... this way it should still run correctly and grab all the updated records.

I don't know if that's perfect for your situation or not, but hopefully it gives you a starting point, and if you need further help or clarification, let me know.

  • Thanks a bunch. Will try to implement this when I get the chance. – Devin Scheid Sep 2 '16 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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