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

[T__WayneWhitley]

You are correct - you have to connect to the file appropriately.

Connecting to a 2007 Microsoft Access database (.accdb) in ArcGIS
Geodata » Data types » Tables » Creating and editing tables
http://resources.arcgis.com/en/help/main/10.1/index.html#//005s00000024000000

[ScottChang]

Hi Wayne, Thanks for your nice response and info for "Connecting to a 2007 Microsoft Access database (.accdb) in ArcGIS 10.1".

I tried to follow the Steps of "Connecting to a 2007 Microsoft Access database (.accdb) in ArcGIS 10.1".  I have difficulties to do Step 2. d. - Drag ADD OLE DB Connection from the Commands list onto a toolbar in ArcCatalog!!??  Where is the "toolbar" in ArcCatalg? Is it on the left pane or the top line? Do I have to type in anything in the "Show commands Containing:" box?  Please help me in doing this Step 2. d.

Thanks again,
Scott Chang

[ScottChang]

Hi Wayne,

I tried myself and I was lucky to overcome the Step 2.d. and dragged "Add OLE DB Connection" to the top line in "toolbar"... I went on to Step 8 for doing "Test Connection" - it failed in "Test Connection" (see the attached file for details).  I don't know why the "Test Coonection" failed!!??  Please help and advise.

Thanks,
Scott Chang

[T__WayneWhitley]

"Drag...onto a toolbar" --- meaning you can choose the toolbar you want to 'dock' this tool onto.  It's sort of like 'drag and drop' behavior, but if you more carefully maneuver (while dragging) over where you'd like to place it, I believe you will see a large 'I' bar, meaning when you 'drop', that's where the tool's button will be inserted.

Don't worry if you don't quite get it where you want on the very 1st try - with the 'customize' window still open you should be able to grab it again and drag it to whatever location on the bar you prefer.  I think if you happen to 'miss' the toolbar nothing 'happens'...so try again.  All this is intended to do is place one of the standard commands that ships with ArcGIS on your user interface that isn't usually there (not there by default).  If you haven't already done so, make sure you open the ArcCatalog app, and follow the procedures.

Wayne

EDIT:
It's a tool for creating a valid connection ---- so let me pose the question to you:
Why would you expect any attempted connection to work if you have not provided any connection parameters?.....skipping from Step 2d to Step 8, what is that??

One more noteworthy thing to make sure you have as stated in Step 1, either have to be doing this on a machine that has Office 2007 or install the necessary connectivity components from Microsoft:
'...Microsoft Office 2007, download AccessDatabaseEngine.exe from 2007 Office System Driver: Data Connectivity Components.'

[ScottChang]

Hi Wayne, Thanks for your response (in Thread #5:Today 06:46 AM)
I am not sure your last response is for my Tread #3 or Tread #4 or both!!
I am very confused regarding where I am in dealing with this connecting to my 2007 Microsoft Access database (.accdb) in my ArcGIS 10.1.  Let me state the following things for letting you know about my "confused" sisuations: (i) my Computer Team install "Windows 7" and "Microsoft Office 2007" (Access 2007 included) for me few weeks ago. I am not sure whether I am the "Admin." for Access 2007 database or not. That is why I skipped to Step 2 directly.   (ii) I think I dragged and dropped the right "Microsoft Office 12.0 Access Database Engine OLE DB Provider" to the "toolbar" on the top line of ArcCatalog.  Did I do the Steps 2, 3,....,8 right or not?  (iii) Do I have to do something in my Access 2007 for connecting it to ArcGIS 10.1/ArcCatalog?  Sorry for my poor understanding of this "Access 2007-ArcGIS 10.1" connection

Please kindly help, advise more, and respond again.

Thanks,
Scott Chang

[T__WayneWhitley]

See the attached pdf.

Hope that works satisfactorily...it isn't a big deal once you are accustomed.


Enjoy,
Wayne

[ScottChang]

Hi Wayne, Thanks for your pdf file.

1. In the Data Source of my existing "Data Link Properis", I typed in:  C:\Temp\APG10pointsMMRP.accdb, and then I clicked on "  Test Connection" button and I got the "Test connection succeeded" box appeared.  But I did not see anything for me to do the "Make XY Event Layer" by using Python-ArcPy programming in ArcGIS 10.1!!??  Did I still miss something in this approach?

2. If I click on the "OLE DB Connection" thing in the toolbar on the top of ArcCatalog, I get the "MS Access Database" in the Database Source box. I click on the "Test Connection" button.  I got no response at all !!??

3. Wayne, are you still using ArcGIS 10.0 or ArcGIS 10.1 to do the Python-ArcPy programming on this "Make XY Event Layer" by using the accdb file?  It seems that your pdf file is for ArcGIS 10.0.  But I am using ArcGIS 10.1 that has no existing connection established between the OLE DB and ArcGIS 10.1 and is quite different from ArcGIS 10.0 that ArcCatalg 10.0 has the connection between ArcGIS and OLE DB alreagy. 

I am still confused about the above-mentioned 1, 2 and 3.   Please  tell me what version of ArcGIS 10 you use and enlighten me as much as you can to help me in getting my Make XY Event Layer of my APG10pointsMMRP.accdb via Python-ArcPy programming.

Thanks,
Scott Chang

[T__WayneWhitley]

Scott, forgive me if I am laughing just a little right now....it's partly because you are successful and are not even aware of it!

That button 'test connection' or whatever it's called was provided as a convenience for you in case you need to troubleshoot your parameters without having to close and reopen that form...

So you achieved the '...succeeded' message.  As troublesome as sometimes these connections can be, I would be jumping for joy!


OK, back on task -- so once you dismiss the msg box, and click OK to dismiss the form, you should have a shortcut connection somewhere.  I'd look in your Database Connections shortcut in ArcCatalog, is it there?....not sure what you named it, but it'll likely have an odc extension on it (your SDE [Spatial Database Engine] connections will have an sde extension; other connection types will have other extensions...).  Maybe you're confused about the purpose of the 'button' you added?  Basically sets up your connection file - it's a shortcut.  Writes a file, like a .sde file, only .odc -- don't know how else to explain it.

Like I said in the little demo pdf, use it in the pathname in your script, just as you would connecting to a file gdb or an sde database.
For example, this refers to a native gdb table:

tbl = r'C:\someDirectory\aFileGDB.gdb\standAloneTable'

This refers to an SDE table:

tbl = r'Database Connections\MCPA.sde\someSDETable'

And this refers to some sample Access 2007 table, just for instance (I did not rename my earlier connection):

tbl = r'Database Connections\test OLE DB connect 2007 Access.odc\anAccessTable'


EDIT:  This is pretty fundamentally important, why I'm sticking with the effort to drive it home --- this is how I am connecting via Python and reading an existing table based on the earlier work (again, this is just a test database -- as I said earlier, I have no recollection what it represents and for testing purposes it is irrelevant):

IDLE 2.6.5      
>>> tbl = r'Database Connections\test OLE DB connect 2007 Access.odc\LWRandMIDDLE'
>>> import arcpy
>>> rows = arcpy.SearchCursor(tbl)
>>> for row in rows:
               print row.Name
           
Phelps/Lipinski
Bryeans, Dale
Rothdeutsch, Thomas
Cadden, James
Heistand, Mark
Hondru, Susan
Wesson, Donald
...
...
etc.
etc.
...
...

>>> del row, rows

[T__WayneWhitley]

This is worth mentioning and may help clear up your confusion, Scott:

There may be a slight omission in the documentation - although I do not think there is anything to change with what I already told you.

But maybe it's worth a follow-up --- you have to test this to be absolutely certain, but see this 10.1 OLE DB connection documentation:

Adding OLE DB connections to databases
Desktop » Mapping » Working with ArcMap » Working with the Catalog window
http://resources.arcgis.com/en/help/main/10.1/index.html#//006600000452000000

Notice if you will the statement (near the top) - and I made the relevant part in bold for you to pay particular attention to:

"You can create and manage OLE DB connections by adding the Add OLE DB connection button to a toolbar in ArcCatalog. Connection files created from this button are listed in the Database Connections folder in the Catalog tree."

The minor omission occurs in the documentation originally given you (listed again below) - I was unable to find such a statement, however it is the same button, and it should be as I have already informed you (look in the Database Connections folder for your new [successfully] created OLE DB connection to your 2007 Access database).

Connecting to a 2007 Microsoft Access database (.accdb) in ArcGIS
Geodata » Data types » Tables » Creating and editing tables
http://resources.arcgis.com/en/help/main/10.1/index.html#//005s00000024000000

Let me know if I am mistaken.  If that's a point of contention for you, a point that needs improvement in the documentation, you should contact ESRI.

Enjoy,
Wayne