Changing the format of a field in an Access table from a python script

[BenjaminSimpson]

Hi,

In my python script I have some code which, using a SQL statement copies a table from one Access database to another. In the original database there are several fields which are Yes/No fields. The problem I am having is that when I copy over the table to the new database these fields revert to Access's default boolean code (-1/0). I know how to manually change the fields format in Access but I am struggling to work out how to do it from my python script. I need to get these fields back to displaying Yes/No.

I copy the table across using the """SELECT * INTO (table name) IN (new database) FROM (table name) IN (original database)""". I have tried using the FORMAT(table name,'Yes/No') clause in SELECT statements, ALTER TABLE statements and UPDATE statements but with no luck.

At this point I am not too sure what I am suppose to do. It is most likely that I am doing something very obvious wrong but I am clueless as to what it is.

Thanks in advance for any help,

Ben.

This is a snippet of my current script:

import os
import sys
import arcpy
import pyodbc

common_db_new = [arcpy.GetParameterAsText(0)]
direc = os.path.dirname(__file__)
Broom_db = direc + "\\River_Broom_126.mdb"
Broom_common = direc + "\\River_Broom_126_CommonTables2k.mdb"

Broom_conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ='+Broom_common)
Broom_cursor = Broom_conn.cursor()
common_conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ='+common_db_new[0])
common_cursor = common_conn.cursor()

SQL = """SELECT * INTO DeterminandsForSimcat IN '{0}'
FROM DeterminandsForSimcat IN '{1}'""".format(common_db_new[0], Broom_common)
common_cursor.execute(SQL)
common_cursor.commit()

SQL = """SELECT Include
FORMAT(Include,'Yes/No')
FROM DeterminandsForSimcat"""
common_cursor.execute(SQL)
common_cursor.commit()

common_cursor.close()
common_conn.close()
Broom_cursor.close()
Broom_conn.close()

[DanPatterson_Retired]

does it have to be boolean or boolean like ... ie short integer with 0, 1... or string with variants of T, F.  Short integer 0,1 is python friendly since it can be brought into boolean service and visa versa

[BenjaminSimpson]

it needs to be displayed as Yes/No. I am trying to recreate the table the same as in databases that already work. They will be put through a software which may specifically look for Yes/No in these fields. I am not exactly sure what the software looks for so I am having to replicate the tables with the exact same format. I can easily change the format from the default Access boolean (-1/0) to Yes/No but I was hoping that there was a way of coding it so I dont have to do this manual step every time I want to create this database.

[JamesCrandall]

Open "DeterminandsForSimcat" table in the Access database and view the table in Design View.  Is that field (I don't think you mention the field name anywhere) set as Yes/No data type?

You really should be explicitly stating any fields, it's just really bad in any case to just issue sql with * --- don't be lazy, write out the fields.

[DarrenWiens2]

As this question does not relate directly to ArcGIS, although you may get any answer here, you should consider asking questions about general Python on Stack Overflow.

[JamesCrandall]

My guess is that it's just a field formatting issue causing you to see different values in the destination table you are inserting into.  That is, after your process completes go ahead and open the Access database and access the properties of that destination table.  In Design View of the table, look at the properties of the YES/NO field you inserted into.  You should see "General" and "Lookup" tabs at the bottom of the window.

The Lookup tab should reveal a "Display Control" property and if you set this to "Check box" and save the table, when you open it in Datasheet view it will display the Checkbox control as seen in the original table/field you copied from.

[BenjaminSimpson]

The fields in question are definately using the Yes/No datatype. I can easily change the fields to display Yes/No instead of -1/0 or any of the other boolean codes but I am hoping to find a way of coding this manual step into my script. Its just on less manual step for the users to deal with.

[TedKowal]

I have gotten around this issue with other languages and other ODBC api's by:

Yes No field in Access is either -1, 0 no nulls.  I have had success by passing a non null tiny int/small int to an access Yes/No field if it is already pre-exisiting.

For table creation with an access Yes/No field, I pass thru a DDL statement to access to create the table then populate it with non null -1,0 values as neccessary for Yes/No True/False.

The DDL field type to pass is "YESNO"

CREATE TABLE TestAllTypes

   (

      MyText       TEXT(50),

      MyMemo       MEMO,

      MyByte       BYTE,

      MyInteger    INTEGER,

      MyLong       LONG,

      MyAutoNumber COUNTER,

      MySingle     SINGLE,

      MyDouble     DOUBLE,

      MyCurrency   CURRENCY,

      MyReplicaID  GUID,

      MyDateTime   DATETIME,

      MyYesNo      YESNO,

      MyOleObject  LONGBINARY,

      MyBinary     BINARY(50)

    )

I would imagine that a DDL "Alter Table"  would work in a similiar manner.  I believe PyODBC requires you to commit when you pass the DDL SQL...

import pyodbc

con = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=Z:\Data\Instruments\testDB.accdb; Provider=MSDASQL;'cur = con.cursor()
string = "CREATE TABLE TestTable(symbol Text(15), leverage double, shares YESNO, price double)"
cur.execute(string)
con.commit()

[JoshuaBixby]

Are you doing this completely outside of ArcPy?  Have you tried ArcPy Copy tool to see if it handles the YESNO field the way you want?