Join field in feature class to field MS Access table 2007 format

629
5
08-16-2013 12:49 AM
PriyankaMehta
New Contributor
Hi,
I am trying to join fields from a feature class in geodatabase to a field in MS Access database.


This statement used to work fine for 2003 format of ms access but for 2007 format it gives an error
gp.JoinField_management(gdbfeat1,"gdbField1",accdbtable1,"accField1","accField2")

arcgisscripting.ExecuteError: Failed to execute. Parameters are not valid.
ERROR 000732: Join Table: Dataset tablexxx does not exist or is not supported


although it clearly exists and it is being able to do execute other statements like
cursor.execute("delete * from " + accdbtable1)  which is inside the same loop.  

Can somebody please point how should I join fields from geodatbase table to ms access table.

Thanks in advance,
Priyanka
Tags (2)
0 Kudos
5 Replies
JamesCrandall
MVP Frequent Contributor
Post the complete code.  What is the error(s)?  How are you connecting to the MS Access db?  Perhaps you need to provide fully qualified table/field names for the join?

Jut not enough info to give you a direct answer.
james
0 Kudos
PriyankaMehta
New Contributor
I am using Pyodbc to connect to access database

import pyodbc, arcgisscripting,arcpy
arcpy = arcgisscripting.create(10.1)
arcpy.overwriteoutput = 1
        
conn = pyodbc.connect(("Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
                                     "DBQ=C:\Projects\ProjectDatabases\MIA\PrimaryDatabase.accdb;"))
cursor = conn.cursor()

InputTransport = "C:\ProjectDatabases\GIS\SIWW_Data_Display.gdb"

def TransportationAccess(accdbtable1,gdbfeat1):
        
                cursor.execute("delete * from " + accdbtable1)  #clear table  
                #join Trans_NodeTypeDetails from geodatabase to Acces_Trans_NodeTypeDetails. Join fields NodeType and Stations
                arcpy.joinfield_management((InputTransport+"\\"+gdbfeat1),"NodeType",accdbtable1,"NodeType","Stations")     



TransportationAccess("Access_Trans_NodeTypeDetails", "Nodes")  
 
  

Feature class in geodatabase (gdbfeat1 in code) is "Nodes"
Table in MS Access(accdbtable1) is "Access_Trans_NodeTypeDetails"

I am trying to join these two tables.
and I get this error saying "Access_Trans_NodeTypeDetails" does not exist. It is the same same table that it is clearing in the previous line.

this is the error that I get

arcgisscripting.ExecuteError: Failed to execute. Parameters are not valid.
ERROR 000732: Join Table: Dataset Access_Trans_NodeTypeDetails does not exist or is not supported
Failed to execute (JoinField).


Thanks,
Priyanka
0 Kudos
JamesCrandall
MVP Frequent Contributor
First, where are you setting "accdbtable1" or "Access_Trans_NodeTypeDetails" before invoking the TransportationAccess() method??? 

Second, I am unsure if the Access_Trans_NodeTypeDetails meets the requirement of the join operation.  From the help http://resources.arcgis.com/en/help/main/10.1/index.html#//001700000064000000


[INDENT]The Join Table can be any of the following types of tables: a geodatabase table (ArcSDE, file, or personal), a dBASE file, an INFO table, or an OLE DB table.[/INDENT]


...which I don't think your .accdb table meets any of these.  One thing I would try is apply this to a TableView first, then join that to the FeatureLayer


def TransportationAccess(accdbtable1,gdbfeat1):
         
                tblv = "accdbtab"
                arcpy.MakeTableView_management(accdbtable1, tblv)
                cursor.execute("delete * from " + accdbtable1)  #clear table  
                #join Trans_NodeTypeDetails from geodatabase to Acces_Trans_NodeTypeDetails. Join fields NodeType and Stations
                arcpy.joinfield_management((InputTransport+"\\"+gdbfeat1),"NodeType",tblv,"NodeType","Stations")     



But why exactly are you attempting to "clear" accdbtable1 before the join operation???  That makes no sense to join an empty table.

Also, that is untested above and I am not even sure if this is the problem but maybe worth a try.
0 Kudos
AmyKlug
Occasional Contributor III
you can use this tool  http://resources.arcgis.com/en/help/main/10.1/index.html#//001200000027000000 or convert your .accdb to a .mdb and access through a database connection
0 Kudos
PriyankaMehta
New Contributor
Thanks for the suggestions but nothing worked. accdb format MS Access table could not be brought to the geodatabase or layer view directly.
i dont know if it was the best method but I finally ended up creating a new empty table in geodatabase and inserting the values from access table to geodatabase and using that for joining.
I used the insert cursor procedure and made a replica of my Access table in geodatabase.

Thanks for the suggestions !
Priyanka
0 Kudos