AnsweredAssumed Answered

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

Question asked by simpson.ben1990 on Dec 4, 2015
Latest reply on Dec 12, 2015 by bixb0012



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,




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)

SQL = """SELECT Include
FROM DeterminandsForSimcat"""