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 · ‎06-03-2016

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!

DanPatterson_Retired · ‎06-03-2016

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

BlakeTerhune · ‎06-03-2016

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 · ‎06-03-2016

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 · ‎06-03-2016

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 · ‎06-03-2016

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 · ‎06-03-2016

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 · ‎06-03-2016

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 · ‎06-03-2016

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 · ‎06-06-2016

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.