ArcGIS10.1:Using Access accdb table to do MakeXYEventLayer-Error732. Why?

[ScottChang]

Hi all,

In my Access 2007, I created the following accdb file "APG10pointsMMRP.accdb" in my C:\Temp folder:
file_id      Pt_ID         X                 Y                Z         sample_id        Boring
5             BH1     -76.301725    39.392986        0.1              1              DH-3
6             BH-2   -76.300981    39.392928         0.2              2              DH-6
..             ....         ......             .....              ...               .               ....
14            WELL1  -76.382654   39.395984        1.1              10             DH-59

In the IDLE (Python GUI) of my ArcGIS 10.1, I executed the following script:

# scAPG10pointsMMRPMakeXYlayer.px  [X,Y-coodinates in the Access 2007 accdb for 10 points of boring locations]
# Description: Creates an XY layer and uses CopyFeatures to save it to a layer file (Used ArcGIS 10.1)
# Author: ESRI - modified by Scott Chang per Arkadiusz Matoszka & Wayne Whitley

# ******* This script was created and executed in ArcGIS 10.1 by Scott Chang on 1 Feb 2013 ******

# import system modules 
import arcpy
from arcpy import env
 
try:
    # Set the local variables
    # in_Table = "firestations.csv"
    tb = r"C:\TEMP\APG10pointsMMRP.accdb\Table1"
    xc = "X"
    yc = "Y"
    zc = "Z"

    out_Layer = "Pointlocations_layer"
    saved_Layer = r"c:\TEMP\APG10pointsMMRPaccdb.shp"    # not .layer!!!
 
    # Set the spatial reference
    # spRef = r"Coordinate Systems\Projected Coordinate Systems\Utm\Nad 1983\NAD 1983 UTM Zone 11N.prj"
    spRef = r"Coordinate Systems\Geographic Coordinate System\World\WGS 1984" 
 
    # Make the XY event layer...
    arcpy.MakeXYEventLayer_management(tb, xc, yc, out_Layer, spRef)
 
    # Print the total rows
    print arcpy.GetCount_management(out_Layer)
 
    # Save to a layer file
    # arcpy.SaveToLayerFile_management(out_Layer, saved_Layer)
    arcpy.CopyFeatures_management(out_Layer, saved_Layer)       # New!!!

except:
    # If an error occurred print the message to the screen
    print arcpy.GetMessages()

It did not run and I got Error 732 - see the attached file for details.

I guess the following code statement is wrong:
tb = r"C:\Temp\APG10pointsMMRP.accdb\Table1"

But I don't know how to correct it.  Please help and advise.

Thanks,
Scott Chang

[ScottChang]

Hi Wayne,  Thanks for valuable responses.

I checked my ArcCatalg 10.1 and I saw the followin:
Database Connections
OLE DB Connection
Table1

I studied your code and then I drafted the following Python-ArcPy script:

# scAPG10pointsTable1OLEDB.py  (drafted by Scott Chang on 5 Feb 2013)
# Tips from Wayne Whitley 

tbl = r"Database Connections\Table1"
xc = "X"
yc = "Y"
zc = "Z"

# import system modules
import arcpy
from arcpy import env

out_Layer = "PointLocatiopns_layers"
saved_Layer = r"C:\Temp\APF10pointsTable1accdb.shp"

# Set the spatial reference
spRef = r"Coordinate System\Geographic Coordinate System\World\WGS 1984"

# Make the XY Event Layer...
arcpy.MakeXYEventLayer_management(tbl, xc, yc, out_Layer, spRef)

# Print the total rows
print arcpy.GetCount_management(out_Layer)

# Save to a layer file
arcpy.CopyFeatures_management(out_Layer, saved_Layer)

# If an error occurred, print the messgae to the screen
print arcpy.GetMessages() 

In the IDLE (Python GUI), I executed my draft script and I got: ImportError: No module named arcpy.  Please see the attached file for details.

Please kindly help and tell me what mistakes I made and how to correct the error.

Thanks,
Scott Chang

[ScottChang]

Hi Wayne,

I found that tbl = r"Database Connections\Table1" was wrong and I changed it to tbl = r"Database Connections\OLE DB Connection\Table1".  I executed the revised script and I got a different error message - see the new attached file.

Please help and advise.

Thanks,
Scott Chang

[T__WayneWhitley]

Well I don't know what your table looks like, but the script is choking on line 20, which is your MakeXYEvent...

So look at your table in ArcMap or Catalog and what kind of fields for X and Y do you have?  That's where I'd start troubleshooting -- could be too this is not a valid table view as the expected input...as a troubleshooting measure, see if you are reading the table okay with Make Table View.  Or, if you need objectid fields, then grab the table and immediately convert it to a file gdb table (or something native to ArcGIS) as a preprocessing measure.

Of course this is extra processing, but you must do something to trace the source of your error.  At this point, you really know nothing other than MakeXYEventLayer doesn't accept what you have fed it.  Simple thing to check, does your table have fields, 'X' and 'Y', as the variables defined in your code?...and are they in acceptable format?  Your spRef var doesn't look right to me either.

[ScottChang]

Hi Wayne,  Thanks for your response.

What you said in your last response is above my head and I am not able to tackle the problem.

I attach a copy of my latest APG10points_accdb_Table1 I used to do the connecting it to my ArcGIS 10.1 for OLE DB Connection.
Please exam this .accdb Table1 and figure out where the problem starts.

Thanks,
Scott Chang

[T__WayneWhitley]

I was curious and thought this warranted further investigation - so I did this via Python (using IDLE), code below.
I am not very good with Access, but it was very easy to input some XY coords into my table, simply entering some test values, 3 valid XY coords in new fields I defined ('X' and 'Y')...I wondered how the fields would be formatted and read by ArcGIS - they came across neatly as the 'Double' types, as I was hoping in order to plot as decimal degrees.  All good!

One more note - I did not define the spatial reference (spRef) so my feature class has an undefined reference, meaning it isn't going to overlay properly with my other layers in a state plane reference.  I'm not interested in keeping this fc; only interested if it gets converted okay table to points, and it does.  Just to verify for this instance, I changed my data frame spatial reference to WGS84 which then 'forces' my other layers to project on-the-fly to my new output event point layer in DD, and voila it matches just fine.

IDLE 2.6.5      
>>> import arcpy

>>> # my pathname connection via my Catalog defined OLE DB connection to my 2007 Access db:
>>> tbl = r'Database Connections\test OLE DB connect 2007 Access.odc\LWRandMIDDLE'

>>> # the xc and yc variables for X and Y coords:
>>> xc = 'X'
>>> yc = 'Y'

>>> # the lyr output event
>>> out_Layer = r'someTestLayer'

>>> # the gdb copy of the lyr
>>> saved_fc = r'C:\Documents and Settings\whitley-wayne\Desktop\stage.gdb\FcToDeleteLater'
>>> 
>>> # didn't bother this spRef
>>> # ...for now, let's see how input reads
>>> arcpy.MakeXYEventLayer_management(tbl, xc, yc, out_Layer)
<Result 'someTestLayer'>
>>> 
>>> # apparently that produced a result...
>>> # does the layer contain anything?
>>> arcpy.GetCount_management(out_Layer)
<Result '41'>
>>> 
>>> # Let's copy the result...
>>> arcpy.CopyFeatures_management(out_Layer, saved_fc)
<Result 'C:\\Documents and Settings\\whitley-wayne\\Desktop\\stage.gdb\\FcToDeleteLater'>
>>> 
>>> # well, that worked too...

[T__WayneWhitley]

Scott, this doesn't look right:

tbl = r"Database Connections\Table1"


...should be a pathname assembled with generally these components:

shortcut root path to db connections (usually 'Database Connections') + your odc filename + your table name


An easy way to get this (with a valid connection of course) is to open ArcCatalog connect and view your table - when you KNOW you are hitting your table that you want to view, copy the address from the Location text box - if you don't have that box, add it as in adding toolbars -- click on Customize on the main toolbar, go to Toolbars, then tick on Location.  It's an easy way to copy/paste pathnames so you don't make a mistake.

Look at how I set my tbl variable-- broken down into those components I mentioned above:
shortcut root:  Database Connections
odc filename:  test OLE DB connect 2007 Access.odc
table name:  LWRandMIDDLE

Then, in my case (yours will be similar):
tbl = r'Database Connections\test OLE DB connect 2007 Access.odc\LWRandMIDDLE'

Ordinarily, I would most likely rename the odc name, but since it's temporary and I don't want to confuse you further, I did not bother.

Your MS Word screenshot looks great to me, but mystifies me why you simply didn't zip and send the db itself - do this, if still stumped:
If the db is massive, just export the table alone to a new Access db, zip and attach that.

Keep it simple and Enjoy,
Wayne

EDIT:  Now that I know the conversion process is sound, I want to get a handle on the spRef parameter - yours did not look right.  Also, if not mistaken, specifying the spatial reference is a little different at 10.1.  But at 10.0 I can still specify the pathname to the prj file containing the projection parameters.  Another thing to do to get the pathname right is, if you do not have the Coordinate Sytems folder displayed in the Catalog tree, do this:

1- from ArcCatalog, go to Customize on the main menu, click ArcCatalog options...
2- on the General tab, tic on Coordinate Systems

Then from the added Coordinate Sytems folder connection, navigate to the coordinate system for which you want the pathname for, click on it to select it... and copy/paste from the Location address to your code, as in this example:

>>> # the corrected pathname for the WGS84 prj file
>>> spRef = r'Coordinate Systems\Geographic Coordinate Systems\World\WGS 1984.prj'

>>> # deleting the out_Layer, from previous execution (see other code window)
>>> arcpy.Delete_management(out_Layer)
<Result 'true'>

>>> # adding the corrected spRef variable parameter
>>> arcpy.MakeXYEventLayer_management(tbl, xc, yc, out_Layer, spRef)
<Result 'someTestLayer'>

>>> # deleting the output fc copy, pre-existing from prior execution
>>> arcpy.Delete_management(saved_fc)
<Result 'true'>

>>> # creating the new fc output, with the spatial ref written to the fc
>>> arcpy.CopyFeatures_management(out_Layer, saved_fc)
<Result 'C:\\Documents and Settings\\whitley-wayne\\Desktop\\stage.gdb\\FcToDeleteLater'>
>>>
>>> # success, no missing projection info, no data frame manipulation required!

[T__WayneWhitley]

Sorry for your trouble, your confusion.  Hopefully, you got connected okay today and you are successfully running your script.
I think perhaps you were trying too hard to digest too much at once, that's all.  Just start with the elementary, go in order, and you will achieve success.

This thread is getting rather long, so I sent you a private message about previewing your table in ArcCatalog - on a fundamental level, if you can preview data, whether feature data or tables, in ArcCatalog (ArcMap as well), then you are connected and can proceed to perform geoprocessing, etc., whatever you need (within reason).  (If you cannot preview, then you need to troubleshoot your connection.  You said earlier you 'connected successfully' and that you were able to make your earlier script run on Excel, so it isn't likely to be an MS driver or system-related problem.)

Hope you have a better experience.  Like I said before, it shouldn't be that difficult - once up and running, you won't look back.  All good.

Enjoy,
Wayne

[ScottChang]

Hi Wayne,  Thanks for your nice response.

(1) This morning, I asked my co-worker who has ArcGIS 10.0 to do a "Connection Test" on my APG10pointsMMRP.accdb file on his machine. He got the .odc filename in his ArcCatalog 10.0!!!
(2) If I used the icon of "Add OLE DB Connection" toolbar (created yesterday [based on the "Connecting to a 2007 Microsoft database (.accdb) in ArcGIS" Steps and I saved it  on the top of my Catalg 10.1) to test my C:\Temp\APG10pointsMMRP.accdb file - Connection Test was successful. But there was no ".odc" filename appeared on my Catalog 10.1!!!????
If I used/clicked the "Add Database Connection" in the left pane of my Catalog 10.1, the "Database Connection" box showed up and Database Platform has no "OLE DB Connection" (just SQL Server, DB2, etc.) to pick!!!??? So I am completely lost in this attemp to find the odc filename for my APG10pointsMMRP.accdb!!!
(3) In this "Python" forum, I just saw the post 'Bug with "Database Connection" in Python using 10.1' by Gregory Dillon (http://forums.arcgis.com/threads/76725-Bug-.....).  It looks pretty complicated discusions in that post and I am not able to know whether my ArcGIS 10.1 with 64-bit can get the right OLE DB Connection (i. e. odc filename, file path, etc.) for me!!!???

Wayne and ArcGIS 10.1 Experts, please kindly help and advise me what I can do to get the right "tbl" for getting the XY-coordinates read and used in the "MakeXYEventLayer" Python code statement.

Thanks,
Scott Chang

[T__WayneWhitley]

Ah, then if that is your problem, see Chris Fox's last statement:

So your options are to reference the full path to the connection file or always ensure you are running the script against the 32 bit or Destkop install of python.


Do you know what the full path reference means?  That means essentially do not use the Database Connections shortcut connection as you would see it in ArcCatalog.

I think you should test this:

1- Get your coworker to save the odc to a shared network location.
2- Use the full pathname to reference in a script to test whether you can connect adequately from your machine.
3- If not able to connect, then I suspect your machine configuration must have changed since you were successfully running scripts last, and you may be having trouble with 64-bit background processing.

Test 1st, then repost.  Typically for admin tasks on the Server, I would copy my admin db connection files to a convenient place accessible to the Server.  So in that case, the pathname may look something like this fictional one to the connection file itself:

\\someOmnipotentServer\SharedLocation\PlaceIMayPlaceConnectionFiles\AnSDEconnectionFile.sde

...and, of course, to reference an existing table via that connection, that may look like the following:

\\someOmnipotentServer\SharedLocation\PlaceIMayPlaceConnectionFiles\AnSDEconnectionFile.sde\dbName.owner.MyTable


EDIT:  For an Access 2007 db, test this---
1- If you can, 1st copy the Access 2007 database to a shared network location...
2- Make an OLE DB connection to the db copy (result of step 1) using the full pathname or the recognized share path (get your coworker to do this for now).
3- Copy the odc to the shared network path...
4- Use the connection file in a script, see the fictional example below:

tbl = r'\\someOmnipotentServer\SharedLocation\test OLE DB connect 2007 Access.odc\LWRandMIDDLE'

[T__WayneWhitley]

There is something I forgot about - I don't think OLE DB connections are supported with 64-bit processing.

There's a very good thread here:

10.1 sp 1 and 32/64 bit Python versions?
http://forums.arcgis.com/threads/70241

Heed the comments there by V Stuart Foote and Kevin Hibma.  What I stated earlier about using the full pathname to your connection file (rather than the Database Connections shortcut) is still good practice -- but with 2 versions of Python installed (an additional 64-bit installation for enhanced background processing), generally the latest version executes with any scripts launched - typically the 64-bit one.  On the install the 32-bit version has been left in place.  Hopefully your team did not uninstall the old version.  For anything that cannot interact with the 64-bit environment (such as the 32-bit 2007 Access app with the OLE DB data access drivers), you must force your script to run with the 32-bit version.

With the 2 Python exe versions (32- and 64-bit), hard-coding the database connection pathnames referenced in a script overcomes the inability of 64 bit to 'find' the shortcut reference known as 'Database Connections' (since it is a different install location), but will likely still attempt running in the 64-bit environment.  Ordinarily that would be fine except you need support for OLE DB.

Then, you have a couple of different options - but before going off half-cocked, let us 1st investigate if indeed there are the 2 versions of the Python exe on your machine.  Search the Python install directory, usually here:

C:\Python27

If you have the dual installation, according to V Stuart Foote and Kevin Hibma, you should see both folders:

ArcGIS10.1
ArcGISx6410.1

And to 'force' your script to reference the 32-bit version (from the 1st folder listed above), you may explicitly use the 32-bit version by using the path in the call to launch your script:

C:\Python27\ArcGIS10.1\python.exe


To clarify further, consider the command line shown execution in the blog post by Kevin Hibma here:

Python scripting with 64-bit processing
by Kevin Hibma on November 12, 2012
http://blogs.esri.com/esri/arcgis/2012/11/12/python-scripting-with-64-bit-processing/

You will see a statement just above a picture of the Windows Command Prompt:
The following script, �??Intersect_Dissolve.py�?� is run in the 64-bit processing space by qualifying which Python installation to execute against at command line (c:\Python27\ArcGISx6410.1\python.exe).

The command line consists of 2 parts underlined in red that is entered by you, the user (if the script itself requires no further parameters):
1- The pathname to the correct Python executable.
2- The script to launch.

For the command line as shown in the blog, the 64-bit processing is used:

> c:\Python27\ArcGISx6410.1\python.exe Intersect_Dissolve.py


In order to launch the script using the 32-bit Python, 'qualify' the version with this command, which will run the same Intersect_Dissolve.py script (but in the 'older' 32-bit environment):

> c:\Python27\ArcGIS10.1\python.exe Intersect_Dissolve.py


Note:  Optionally, you may use file associations to run all your scripts in the foreground (which should use 32-bit), qualifying to explicitly run in 64-bit when needed.

I know that may be a lot to digest, but in short, for the explicit task of processing via an OLE DB connection to a 2007 Access database, you will need to make sure you are running the 32 bit version of Python.  You have a few different ways to configure how you conduct this, controlling your system configuration - but at this point it is more important to identify whether you have the 32-bit/64-bit dual environment and if so, understand how to handle it in some way.  You can make further adjustments later as your understanding increases.

Hope that's clear.  Also, note that in one of the more recent posts (I forgot which) a KB (knowledge base) article is in the works.

-Wayne