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?

6187
24
06-03-2016 08:56 AM
PaulHacker2
New Contributor II

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!

0 Kudos
24 Replies
DanPatterson_Retired
MVP Esteemed Contributor

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

0 Kudos
BlakeTerhune
MVP Regular Contributor

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
New Contributor II

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..

ERROR 000800: The value is not a member of TEXT | FLOAT | DOUBLE | SHORT | LONG | DATE | BLOB | RASTER | GUID.

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

0 Kudos
WesMiller
Regular Contributor III

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
New Contributor II

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

ERROR 000800: The value is not a member of TEXT | FLOAT | DOUBLE | SHORT | LONG | DATE | BLOB | RASTER | GUID.

Failed to execute (AddField).

0 Kudos
WesMiller
Regular Contributor III

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
New Contributor II

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

Apparently they only allow TEXT | FLOAT | DOUBLE | SHORT | LONG | DATE | BLOB | RASTER | GUID.

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!

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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
New Contributor II

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.

0 Kudos