Transfer data from access mdb to personal geodatabase (OLE, Python scripting)

Question

I am very new in Python. And I guess I need to use lots of Python here.

I have MS Access 2003-2007 database (mdb format) and personal geodatabase (mdb). I created a tool in Access that imports data from personal geodatabase to access mdb, checks it and updates changes.

Now I am working on implementing backwards functionality (transfer data from access mdb to personal geodatabase). For now, I can implement that manually using OLE connection.

How I do it:

1.in ArcCatalog create OLE connection,

2.Connect to my access database backend,

3.Choose a Table I want to use from this access database backend,

4.Open ArcMap, add Feature class from personal geodatabase to the map,

5.Perform join of Feature class and Table,

6.Then using Field calculator update all necessary fields.

For now in my vision I can create custom tool for join Feature class and Table.

Do you have any suggestions how I can automate this process, at least half of this process.

Here is a code to join Table to Feature Class

import arcpy
from arcpy import env
env.overwriteOutput = 1
env.workspace = r"D:\Working\Tools\ToolShare\Scripts"

arcpy.MakeFeatureLayer_management("Crossings", "tempLayer")
arcpy.AddJoin_management("tempLayer", "UNIQUE_NAME", "tblCrossings", "UNIQUE_NAM")

When I open the feature class, there are no changes... When I tried to use arcpy.JoinField_management("tempLayer", "UNIQUE_NAME", "tblCrossings", "UNIQUE_NAM")instead of arcpy.AddJoin_managementfields were added to the feature class, but permanently (I don't need that).

After this part I guess I need to use UpdateCursor (Field Calculator point). But I don't know how correctly it can be used here.

Here are my thoughts, please help me to organize them.

Join and calculate field script

import arcpy
import sys
import os
from arcpy import env

env.overwriteOutput = 1
scriptPath = sys.path[0]
arcpy.AddMessage("Script folder: " + scriptPath)
toolSharePath = os.path.dirname(scriptPath)
arcpy.AddMessage("ToolShare folder: " + toolSharePath)

inFeatureClass = arcpy.GetParameterAsText(0) 
inTable = arcpy.GetParameterAsText(1)

arcpy.MakeFeatureLayer_management("Crossings", "tempLayer")

arcpy.AddJoin_management("tempLayer", "UNIQUE_NAME", "tblCrossings", "UNIQUE_NAM")

arcpy.CalculateField_management("tempLayer", "Status_", "!Status!", "PYTHON_9.3", "")

arcpy.RemoveJoin_management("tempLayer", "")

Add feature class script

import arcpy
from arcpy import env

inFeatureClass = arcpy.GetParameterAsText(0)
mxd = arcpy.mapping.MapDocument("CURRENT")
df = arcpy.mapping.ListDataFrames(mxd,"*")[0]
newlayer = arcpy.mapping.Layer(inFeatureClass)
arcpy.mapping.AddLayer(df, newlayer,"BOTTOM") 

Add feature class, join and calculate together

import arcpy
import sys
import os
from arcpy import env

env.overwriteOutput = 1

scriptPath = sys.path[0]
arcpy.AddMessage("Script folder: " + scriptPath)
toolSharePath = os.path.dirname(scriptPath)
arcpy.AddMessage("ToolShare folder: " + toolSharePath)

inFeatureClass = arcpy.GetParameterAsText(0)  # Feature class from personal geodatabase
inTable = arcpy.GetParameterAsText(1)  # Table from access database (mdb)

mxd = arcpy.mapping.MapDocument("CURRENT")
df = arcpy.mapping.ListDataFrames(mxd,"*")[0]
newlayer = arcpy.mapping.Layer(inFeatureClass)
arcpy.mapping.AddLayer(df, newlayer,"BOTTOM")

arcpy.MakeFeatureLayer_management("Crossings", "tempLayer")
arcpy.AddJoin_management("tempLayer", "UNIQUE_NAME", "tblCrossings", "UNIQUE_NAM")
arcpy.CalculateField_management("tempLayer", "Status_", "!Status!", "PYTHON_9.3", "")
arcpy.RemoveJoin_management("tempLayer", "")

Answer 1

You are almost there. You have completed up to step 5, however arcpy.AddJoin_management just joins the two tables in memory (as per your manual step 5) - you'll still need to calculate the fields you want updated.

For step six you'll want to make use of arcpy.CalculateField_management for each field you need to have updated, just as you would if you used field calculator manually in your attribute table.

Something like

arcpy.CalculateField_management("layer", "fieldToUpdate", "!fieldToUpdateFrom!", "PYTHON_9.3", "")

for each field you want to calculate, where fieldToUpdate is your Feature Class field that needs updating, and !fieldToUpdateFrom! is the Table field you're getting the value from.

To be tidy you may want to include a arcpy.RemoveJoin_management("layer", "joinName") to remove your join.