nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Creating table inside .mdb database using arcpy.CreateTable_management. I have an access mdb database and I want to use arcpy.CreateTable_management to create a table inside the .mdb. How?

PaulHacker2

I have an access mdb database and I want to use arcpy.CreateTable_management to create a table inside the .mdb.

This works to make the DB using Python:

config_keyword = ""

mdb = ".mdb"

out_folder_path = "C:/output"

out_name = "CountyCode_MapGrid_"

name = "newDB"

arcpy.CreatePersonalGDB_management(out_folder_path, out_name + name + mdb)

And it works fine but to get a table created INSIDE the .mdb using:

arcpy.CreateTable_management(out_folder_path, "Block_77", config_keyword)

arcpy.AddField_management(out_name, "OBJECTID", "AutoNumber")

arcpy.AddField_management(out_name, "SHAPE", "OLE Object")

arcpy.AddField_management(out_name, "id_block", "Number")

arcpy.AddField_management(out_name, "id_mapgrid", "Number")

arcpy.AddField_management(out_name, "map_number", "Short Text")

arcpy.AddField_management(out_name, "Block", "Short Text")

arcpy.AddField_management(out_name, "SHAPE_Length", "Number")

arcpy.AddField_management(out_name, "SHAPE_Area", "Number")

arcpy.AddField_management(out_name, "created_user", "Short Text")

arcpy.AddField_management(out_name, "created_date", "Date/Time")

arcpy.AddField_management(out_name, "last_edited_user", "Short Text")

arcpy.AddField_management(out_name, "last_edited_date", "Date/Time")

Does not work. Any suggestions?

Thanks!

Find more posts tagged with

Accepted answers

All comments

DanPatterson_Retired

Create Table—Help | ArcGIS for Desktop doesn't appear to be an option of the 3 output types given

blake.terhune

You need to use the workspace (with the name of the mdb), not just the folder location like you're doing with out_folder_path. Same goes for adding the fields. You either need to define an arcpy.env.workspace() or use the full path to the geodatabase and table.

But better yet, build your paths better with raw strings and the os.path module. Filenames and file paths in Python

Also, looks like you're trying to add a shape field. Is this supposed to be a feature class? If it's a feature class (a table with with a shape field), you should also be defining a spatial reference.

Finally, is there a particular reason you're using a personal geodatabase instead of a file geodatabase, which more robust and future-proof in the Esri world?

Types of geodatabases—ArcGIS Help | ArcGIS for Desktop

PaulHacker2

Thanks Blake.

I was asked to build personal geodatabase only and that is why I'm doing it.

I changed it to have the DB name fully in the out_folder_path and it worked except on the "SHAPE" field.

Now the original access mdb I used as a template had the 'Field Name' as SHAPE, and the data type says "OLE Object" when I look at it in the access db. I presumed it would be the same when creating using arcpy. I guess I'm wrong cause it stops right there in creating the table inside the DB. It gives the error..

SO I guess "OLE Object" is obviously not one of them!Any idea what a "OLE Object" is? I see it is allowed in Access. The OLE (Object Linking and Embedding)seems to be only in MS Access.

Paul

WesMiller

Are you trying to Create Feature Class—Help | ArcGIS for Desktop you will not need to add the shape or OID fields they will be created for you

PaulHacker2

Thanks. Not sure about the feature class.

Well my code looks like this:

# Import system modules

import os, sys, traceback, math, decimal

import arcpy

from arcpy import env

# Set local variables

out_folder_path = "C:\\output"

out_name = "CountyCode_MapGrid_"

template = "TestDB.accdb"

# database designation

mdb = ".mdb"

# no idea what this will be used for.

config_keyword = ""

# arr is set to just one DB and table to be created inside the DB. But I will later have many DBs that are duplicate except for the DB and table names.

arr = {"big_testdb"}

# Execute CreatePersonalGDB loop.

for name in arr:

# Execute create mdb database

fullname = out_name + name

arcpy.CreatePersonalGDB_management(out_folder_path, fullname + mdb)

# works fine. The DB is created. Now on to the tables inside.

env.workspace = "C:\\output\\" + out_name + name + mdb

arcpy.env.workspace = "C:\\output\\" + fullname + mdb

# Execute CreateTable INSIDE the mdb

bigpath = out_folder_path + "\\" + out_name + name + mdb

fieldPrecision = 9

print "got here 2. Create table: " + bigpath

arcpy.CreateTable_management(bigpath, "Block_" + name, config_keyword)

arcpy.AddField_management(fullname, "OBJECTID", "AutoNumber")

arcpy.AddField_management(fullname, "SHAPE", "LONG",fieldPrecision, "", "","","NULLABLE")

arcpy.AddField_management(fullname, "id_block", "DOUBLE")

arcpy.AddField_management(fullname, "id_mapgrid", "Number")

arcpy.AddField_management(fullname, "map_number", "Short Text")

arcpy.AddField_management(fullname, "Block", "Short Text")

arcpy.AddField_management(fullname, "SHAPE_Length", "Number")

arcpy.AddField_management(fullname, "SHAPE_Area", "Number")

arcpy.AddField_management(fullname, "created_user", "Short Text")

arcpy.AddField_management(fullname, "created_date", "Date/Time")

arcpy.AddField_management(fullname, "last_edited_user", "Short Text")

arcpy.AddField_management(fullname, "last_edited_date", "Date/Time")

It only creates the table and the field OBJECTID. Nothing else is created. Errors....

Traceback (most recent call last):

File "C:\Users\phacker\Documents\PROJECTS\Access PDB\PersonalDB.py", line 61, in <module>

arcpy.AddField_management(fullname, "OBJECTID", "AutoNumber")

File "C:\Program Files (x86)\ArcGIS\Desktop10.4\ArcPy\arcpy\management.py", line 3149, in AddField

raise e

ExecuteError: Failed to execute. Parameters are not valid.

ERROR 000732: Input Table: Dataset CountyCode_MapGrid_big_testdb does not exist or is not supported

Failed to execute (AddField).

WesMiller

Comment out the add "OBJECTID" field that one is created for you. I see you are also trying to create a shape field which suggests your trying to create a feature class. You will also need to either use the full path of the input table or set the workspace environment.

import os
arcpy.env.workspace = os.path.join(out_folder_path, out_name, name, mdb)

Here is the help o creating tables

Create Table—Help | ArcGIS for Desktop

Here is the help on adding fields

Add Field—Help | ArcGIS for Desktop

PaulHacker2

Thanks. That is what I've been using but.. the fields for each table are different that what the AddFields allow.

Yet the users want an "OLE Object"! We may have to have a compromise!

And I got the AddFields straitened out! It works but I'll have to see how they want the SHAPE to work (that is the OLE Object) to function.

Thanks!

JoshuaBixby

You seem to be struggling with the fundamental concepts of tables and feature classes in ArcGIS. A feature class is simply a spatially-enabled table (a table with an extra "shape" field). If you want a "shape" field, or one that is a functional shape field, you will need to use the Create Feature Class tool. After you do so, you will notice that the field is of "OLE Object" type.

PaulHacker2

Thanks. I'm trying that now. But is there a way to copy one field from a table (in a DB) to another table in ANOTHER DB? That is we have that OLE object defined in another table already, can it be copied to the table I created using arcpy.CreateTable.management?

Thanks.

JoshuaBixby

If you already have a feature class or shapefile and want to copy it into a different folder or geodatabase, check out the Feature Class to Feature Class tool.

PaulHacker2

Found using ArcMap I can create a field name "SHAPE" with data type "Geometry".

How can I do this with Python? THIS is what I need exactly to get the 'OLE Object' to work.

I used arcpy.AddField_management but no go with:

arcpy.AddField_management(tablename, "SHAPE", "Geometry")

Is there another arcpy function for this?

Thanks.

JoshuaBixby

How exactly are you adding this "SHAPE" field in ArcMap? I suspect my reply will still be Create Feature Class tool.

DanPatterson_Retired

Is there another arcpy function for this? No which is why you use copyfeatures or featureclasstofeatureclass or strip the attributes out of a copy of an existing file. This is to slow down people would want to produce geometry outside of what esri intends, much like you can do now with arcpy.da 's numpyarraytofeatureclass. There you can create your own geometry objects but you would have to be working with arrays to begin with.

PaulHacker2

Ok, the users use ArcMap to create a mdb with a table and inside that table they create field name SHAPE with Geometry for the datatype. They already have this one database and table. They want ME to (for many many new databases each with several tables) take the SHAPE field name (with Geometry for datatype) and copy it's attributes to the other databases and tables so they all have a SHAPE with Geometry.

I have a python script that will take the list of names of the databases they want, it's array, and go through each one creating databases with the same names (using arcpy.CreatePersonalGDB_management), then creating tables with the names they specified to be inside each database (arcpy.CreateTable_management), and inside each of the tables use arcpy.AddField_management to make each field... with the exception of the SHAPE field I need to be as Geometry.

And that is where I'm at. I can get each of the fields created, tables created, and PersonalGDB to execute, but adding that one field, for a Geometry datatype, is the problem.

Thanks, Paul

PaulHacker2

New information. The users use ArcGis 10.1 and they create their mdb using the toolboxs. They create a personal Geodatabase. It seems it somehow creates a SHAPE field with Geography for the field type.

Looks like this:

Field Name DataType Description(optional)

-----------------------------------------------------------------------------------------------------------

OBJECTID AutoNumber

SHAPE OLE Object <-- the geography

id_block Number

id_mapgrid Number

etc..

Now such as id_block they input but the SHAPE and OBJECTID is automatically created.

How was this created? What Python tool was used?

I already use arrcpy.CreatePersonalGDB_management - to make the new mdb

arcpy.CreateTable_management - to create the table

and

arcpy.AddField_management - to create the fields

But nowhere does the SHAPE field show up. Now I see a CreateFeatureclass_management but how does that make a field in just one table of the mdb?

Thanks,

Paul

JoshuaBixby

I don't know how many more ways people can say it, you don't create a spatially-enabled table in either a personal (*.MDB) or file (*.GDB) geodatabase by using Create Tool and Add Field. As you have already found, the proper method to create a spatially-enabled table (one with a SHAPE field) is to use the Create Feature Class tool. The tool does multiple things for you, one it creates a table, and two it adds the spatial field. The result is a new table with a SHAPE field already defined for you.

In terms of how, specifically, Create Feature Class makes the SHAPE field, the answer is buried in the code of the tool. I think for your purposes, all you need to know is that the tool works and you should be working with it.

If your goal is to create a SHAPE field in a Access table not using Esri tools, the short answer is don't.

RebeccaStrauch__GISP

Can you take a step back and explain what your actually goal is? I think you are getting confused about just a table vs a featureclass. A table can be just that, a list of fields with data in them. Having a field called shape does not make it into a featureclass in and of itself. Read thru the links that Joshua and Dan sent. If you create a featureclass, ArcGIS (whether thru a toolbox or arcpy) will add the Shape field and will contain the geometry. What geometry is included in the Shape is determined by you input.

if you have a table that contains XY coordinates, you can generate a point featureclass, but you can also import from many other formats (Shape files, coverages, autocad, raster formats, etc) to create a featureclass.

if you have a featureclass with geometry and have another table with attributes that you want tohave attached to the features with a common field, use JoinTable_managerment (sorry can't add the direct link...will come back and add).

in anycase, unless there is a business requirement for you having to stay in and mdb personal database...I highly recomend you change to using the file geodatabase instead. Much more reliable and made for ArcGIS instead of relying on Access and it's limits (and version changes).

So what is you ultimate goal and what are your resources? are they just tables with columns and data or is there some sort of coordinate information or geometry in the tables already. Again, you don't want to add a Shape field and try to create a featureclass that way. what format was it in originally that has the geometry in an "OLE" field? maybe I'm missing something?

edit link to join table management for 10.1 ArcGIS Help 10.1

PaulHacker2

Sorry gang. Here is the story.

The users use ArcGis to make this to begin with. It ends up with a .mdb database. It already has in the mdb the tables GDB_ColumnInfo, GDB_GeomColumns, GDB_ItemRelationships, GDB_ItemRelationshipTypes, GDB_Items, GDB_Items_Shape_Index, GDB_ItemTypes, GDB_ReplicaLog, GDB_SpatialRefs, as well as the tables they created using Arcgis.

In their tables, many of these tables, each with a name as well as a number, and that number corresponds to land tracts, have inside them field names such as OBJECTID (AutoNumber), and SHAPE (OLE Object.. and I know it is supposed to be a polygon), and other fields they have defined with Data Types Number, Short Text, Date/Time..

Now while they have done ONE such MDB, they want me to duplicate this maybe 500 times for many other tracts.

Yes many databases.

I am using Python and CreateFeatureclass_management

CreatePersonalGDB_management/CreateTable_management/AddField_management (which works for everything but the SHAPE field... can't get a POLYGON in it using AddField.)

Each of the databases will have unique names, and most of the tables will have unique names, and most of the fields will have unique names depending on the tract number!

I have a txt file with all the tract numbers and I read it in via Python open and 'for line in f.readlines' and an array to hold them.

So I can do all the renaming easily, it's the SHAPE field that drives me nuts!

Oh, and I am a MVS/CICS/COBOL Mainframer turned Visual Studio/C#/SQL programmer & ArcGIS/Python programmer. And this is my first Python program! Everything works except that SHAPE field...

Now if I can just find some way to use the same CreatePersonalGDB_management/CreateTable_management/AddField_management to make the database and then in the tables slip in a SHAPE that is a polygon, that would do what I need to be done.

Thanks!

WesMiller

Have a look at Geodatabase XML—Help | ArcGIS for Desktop

KoryKramer_0-1626301819147.png

DirkVandervoort_0-1626302016627.jpeg

Eng_A_SH_0-1626306660762.png

blake.terhune

Paul Hacker says:

Now if I can just find some way to use the same CreatePersonalGDB_management/CreateTable_management/AddField_management to make the database and then in the tables slip in a SHAPE that is a polygon, that would do what I need to be done.

Joshua Bixby replied:

...the proper method to create a spatially-enabled table (one with a SHAPE field) is to use the Create Feature Class tool. The tool does multiple things for you, one it creates a table, and two it adds the spatial field. The result is a new table with a SHAPE field already defined for you.
If your goal is to create a SHAPE field in a Access table not using Esri tools, the short answer is don't.

There's really nothing more to be said. Not sure why you're so stuck on trying to reinvent the wheel when it comes to making a feature class.

Eng_A_SH_1-1626306877981.png

PaulHacker2

So are there any Python script to create Geodatabase from XML Workspace documents?

direction.png

DongheeKim_0-1626307862328.png

PaulHacker2

Ok, I used arcpy.CreateFileGDB_management(out_folder_path,out_name) to create the geodatabase

and

arcpy.ImportXMLWorkspaceDocument_management(target_gdb, in_file, import_type) to use the XML to load it with just

the SCHEMA_ONLY.

But how to rename all the tables and fields to match the land tracks for each DB required?

JoshuaBixby

Alter Field tool or you could edit the XML file before using it to create the new feature classes.

DongheeKim_1-1626307990376.png

PaulHacker2

Yep. Using Python to get the list of tracts, then edit a template XML document and create all the XML documents for all the tracts, and then making the new feature classes for each of the tracts. Now it's 'merely a matter of programming!"

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.