topic Re: Join field in feature class to field MS Access table 2007 format in Python Questions

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

PriyankaMehta — Fri, 16 Aug 2013 07:49:44 GMT

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

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

JamesCrandall — Fri, 16 Aug 2013 10:56:26 GMT

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

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

PriyankaMehta — Sat, 11 Dec 2021 23:10:05 GMT

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

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

JamesCrandall — Sat, 11 Dec 2021 23:10:08 GMT

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.

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

AmyKlug — Mon, 19 Aug 2013 20:57:38 GMT

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

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

PriyankaMehta — Tue, 27 Aug 2013 00:54:01 GMT

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